Skip to Main Content
  • Questions

  • LINGUISTIC and BINARY_CI performance problem

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Aleksej.

Asked: November 13, 2024 - 10:43 am UTC

Last updated: December 11, 2024 - 7:49 am UTC

Version: 19

Viewed 1000+ times

You Asked

Hello,
We use Oracle 19c with c# ADO and EntityFramework.
We have performance issue when use ALTER SESSION SET NLS_COMP = LINGUISTIC NLS_SORT = BINARY_CI;
Our system use this for all queries.
I use this simple table for test:

CREATE TABLE app."Customer" (
  "Id" NUMBER(19) GENERATED BY DEFAULT ON NULL AS IDENTITY,
  "Name" NVARCHAR2(254) NOT NULL,
  PRIMARY KEY ("Id")
)

BEGIN
    FOR i IN 1..2000000 LOOP
        INSERT INTO app."Customer" ("Name")
        VALUES (
            DBMS_RANDOM.STRING('U', 10)
        );
        IF MOD(i, 10000) = 0 THEN
            COMMIT;
        END IF;
    END LOOP;

    -- Final commit for any remaining rows
    COMMIT;
END;

CREATE INDEX app."IX_Customer_Name_NLS" ON app."Customer" (NLSSORT("Name", 'NLS_SORT=BINARY_CI'))

CREATE INDEX app."IX_Customer_Name" ON app."Customer" ("Name")



Now if I select:
0.02ms
SELECT * FROM app."Customer" WHERE "Name" LIKE N'test' || '%'

0.02ms
SELECT * FROM app."Customer" WHERE "Name" LIKE &name || '%'


0.02ms
    BEGIN
        FOR rec IN (SELECT "Name" 
                    FROM app."Customer" 
                    WHERE "Name" LIKE 'test%'
                    OFFSET 0 ROWS FETCH NEXT 100 ROWS ONLY) 
        LOOP
            DBMS_OUTPUT.PUT_LINE(rec."Name");
        END LOOP;
    END;


0.700ms
    DECLARE
        v_name NVARCHAR2(254) := N'test';
    BEGIN
        FOR rec IN (
            SELECT "Name" 
            FROM app."Customer" 
            WHERE "Name" like v_name || '%'
            OFFSET 0 ROWS FETCH NEXT 100 ROWS ONLY
        ) 
        LOOP
            DBMS_OUTPUT.PUT_LINE(rec."Name");
        END LOOP;
    END;



ADO+EF uses the last option when sending a request, so all system works slowly.

I try make 4 different query for last query with parameter:
BINARY+BINARY
LINGUISTIC +BINARY
BINARY+BINARY_CI
LINGUISTIC +BINARY_CI
Results:
Query 1 duration: 14 ms
Query 2 duration: 11 ms
Query 3 duration: 10 ms
Query 4 duration: 557 ms <----LINGUISTIC +BINARY_CI



If take only 1-5 rows - the result will be fast, if it found it. If not, it will be slow - possible it do a full scan.


When I crate another DB with max_string_size=EXTENDED
Create same table with "Name" NVARCHAR2(254) COLLATE BINARY_CI NOT NULL
I got this on all queres

Results:
Query 1 duration: 173 ms
Query 2 duration: 173 ms
Query 3 duration: 171 ms
Query 4 duration: 180 ms


Look like it that faster when use session, but not fast as if make plain parameterless sql request.
I want to achieve the speed of a plain sql request, if it is possible.

Thank you.

and Connor said...

We'll need some more details - I can't reproduce your findings on my machine - I'm getting index usage as expected


SQL>
SQL> CREATE TABLE cust (
  2    Id NUMBER(19) GENERATED BY DEFAULT ON NULL AS IDENTITY,
  3    Name NVARCHAR2(254) NOT NULL,
  4    PRIMARY KEY (Id)
  5  );

Table created.

SQL>
SQL> insert into cust (name)
  2  select DBMS_RANDOM.STRING('U', 10)
  3  from dual
  4  connect by level <= 2000000;

2000000 rows created.

SQL> commit;

Commit complete.

SQL>
SQL> CREATE INDEX cust_ix1 ON cust (NLSSORT(Name, 'NLS_SORT=BINARY_CI'));

Index created.

SQL> CREATE INDEX cust_ixs ON cust (Name);

Index created.

SQL>
SQL> set timing on
SQL> SELECT * FROM cust WHERE Name LIKE N'test' || '%';

no rows selected

Elapsed: 00:00:00.01
SQL> select * from dbms_xplan.display_cursor();

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  36mqs493hj9dz, child number 0
-------------------------------------
SELECT * FROM cust WHERE Name LIKE N'test' || '%'

Plan hash value: 1121143573

------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |          |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| CUST     |     1 |   269 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | CUST_IXS |     1 |       |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("NAME" LIKE U'test%')
       filter("NAME" LIKE U'test%')

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


24 rows selected.

Elapsed: 00:00:00.02
SQL> SELECT * FROM cust WHERE Name LIKE N'test' || '%';

no rows selected

Elapsed: 00:00:00.00
SQL> select * from dbms_xplan.display_cursor();

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  36mqs493hj9dz, child number 0
-------------------------------------
SELECT * FROM cust WHERE Name LIKE N'test' || '%'

Plan hash value: 1121143573

------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |          |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| CUST     |     1 |   269 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | CUST_IXS |     1 |       |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("NAME" LIKE U'test%')
       filter("NAME" LIKE U'test%')

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


24 rows selected.

Elapsed: 00:00:00.01
SQL> SELECT * FROM cust WHERE Name LIKE 'test%' || '%' OFFSET 0 ROWS FETCH NEXT 100 ROWS ONLY;

no rows selected

Elapsed: 00:00:00.00
SQL> select * from dbms_xplan.display_cursor();

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  70xn5hw0nqyvm, child number 0
-------------------------------------
SELECT * FROM cust WHERE Name LIKE 'test%' || '%' OFFSET 0 ROWS FETCH
NEXT 100 ROWS ONLY

Plan hash value: 3682864013

------------------------------------------------------------------------------------------
| Id  | Operation                     | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |          |       |       |     2 (100)|          |
|*  1 |  VIEW                         |          |   100 | 28200 |     2   (0)| 00:00:01 |
|*  2 |   WINDOW NOSORT STOPKEY       |          |     1 |   269 |     2   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| CUST     |     1 |   269 |     2   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | CUST_IXS |     1 |       |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(("from$_subquery$_002"."rowlimit_$$_rownumber"<=100 AND
              "from$_subquery$_002"."rowlimit_$$_rownumber">0))
   2 - filter(ROW_NUMBER() OVER ( ORDER BY  NULL )<=100)
   4 - access("NAME" LIKE U'test%%')
       filter("NAME" LIKE U'test%%')

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


30 rows selected.

Elapsed: 00:00:00.02
SQL>
SQL> variable v varchar2(10)
SQL> exec :v := N'test'

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SQL> SELECT * FROM cust WHERE Name LIKE :v || '%' OFFSET 0 ROWS FETCH NEXT 100 ROWS ONLY;

no rows selected

Elapsed: 00:00:00.01
SQL> select * from dbms_xplan.display_cursor();

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  91hqzb87f84ud, child number 0
-------------------------------------
SELECT * FROM cust WHERE Name LIKE :v || '%' OFFSET 0 ROWS FETCH NEXT
100 ROWS ONLY

Plan hash value: 3682864013

------------------------------------------------------------------------------------------
| Id  | Operation                     | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |          |       |       |     2 (100)|          |
|*  1 |  VIEW                         |          |   100 | 28200 |     2   (0)| 00:00:01 |
|*  2 |   WINDOW NOSORT STOPKEY       |          |     1 |   269 |     2   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| CUST     |     1 |   269 |     2   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | CUST_IXS |     1 |       |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(("from$_subquery$_002"."rowlimit_$$_rownumber"<=100 AND
              "from$_subquery$_002"."rowlimit_$$_rownumber">0))
   2 - filter(ROW_NUMBER() OVER ( ORDER BY  NULL )<=100)
   4 - access("NAME" LIKE SYS_OP_C2C(:V||'%'))
       filter("NAME" LIKE SYS_OP_C2C(:V||'%'))

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


30 rows selected.

Elapsed: 00:00:00.01
SQL>
SQL> SELECT name FROM cust WHERE Name LIKE :v || '%' OFFSET 0 ROWS FETCH NEXT 100 ROWS ONLY;

no rows selected

Elapsed: 00:00:00.00
SQL> select * from dbms_xplan.display_cursor();

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  apqf842t13zm3, child number 0
-------------------------------------
SELECT name FROM cust WHERE Name LIKE :v || '%' OFFSET 0 ROWS FETCH
NEXT 100 ROWS ONLY

Plan hash value: 1427556000

-----------------------------------------------------------------------------------
| Id  | Operation              | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          |       |       |     2 (100)|          |
|*  1 |  VIEW                  |          |   100 | 26900 |     2   (0)| 00:00:01 |
|*  2 |   WINDOW NOSORT STOPKEY|          |     1 |   256 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN    | CUST_IXS |     1 |   256 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(("from$_subquery$_002"."rowlimit_$$_rownumber"<=100 AND
              "from$_subquery$_002"."rowlimit_$$_rownumber">0))
   2 - filter(ROW_NUMBER() OVER ( ORDER BY  NULL )<=100)
   3 - access("NAME" LIKE SYS_OP_C2C(:V||'%'))
       filter("NAME" LIKE SYS_OP_C2C(:V||'%'))

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


29 rows selected.

Elapsed: 00:00:00.01
SQL>
SQL> DECLARE
  2          v_name NVARCHAR2(254) := N'test';
  3      BEGIN
  4          FOR rec IN (
  5              SELECT name FROM cust WHERE Name LIKE v_name || '%' OFFSET 0 ROWS FETCH NEXT 100 ROWS ONLY
  6          )
  7          LOOP
  8              DBMS_OUTPUT.PUT_LINE(rec.Name);
  9          END LOOP;
 10      END;
 11  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SQL>
SQL>


Rating

  (3 ratings)

Comments

Alex, December 04, 2024 - 7:45 am UTC

You need set session:
 ALTER SESSION SET NLS_COMP = LINGUISTIC NLS_SORT = BINARY_CI;


with defalut work quick:
 ALTER SESSION SET NLS_COMP = BINARY NLS_SORT = BINARY;

Connor McDonald
December 11, 2024 - 7:49 am UTC

see my final followup

Alex, December 04, 2024 - 10:08 am UTC 

SQL> ALTER SESSION SET NLS_COMP = LINGUISTIC NLS_SORT = BINARY_CI;

Session altered.


SQL> DECLARE v_name NVARCHAR2(254) := N'TEST%';    BEGIN FOR rec IN (SELECT "Name" FROM app."Customer" WHERE "Name" LIKE v_name) LOOP DBMS_OUTPUT.PUT_LINE(rec."Name"); END LOOP;    END;
  2  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.75


SQL> select * from AIS."Customer" where "Name" like N'TEST' || '%';
6 rows selected.

Elapsed: 00:00:00.01

Aleksej, December 10, 2024 - 7:48 am UTC

The main problem that .NET Entity Framework use command type query with parameters. So we got all this performance problem here.
There are only one option is to create additional tables with normalized uppercase files and search by it.
It's sad that such a problem exists in Oracle.
Maybe in the command should somehow delcared that field is BINARY_CI ? But EF don't do it by default.

Connor McDonald
December 11, 2024 - 7:49 am UTC

Looks like you have hit this:

Bug 34764262 - LINGUISTIC INDEXES NOT USED FOR LIKE :BIND PREDICATES

Fixed in version 23, but you could raise an SR and ask for a backport to 19c
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.