Linguistic Index can't do UNIQUE/RANGE SCAN in 10.2, any workaround?

640834

Hi,

Christian Antognini, in his book Troubleshooting Oracle Performance, when talking about linguistic indexes says this:

"Up to Oracle Database 10g Release 2, another limitation is that in order to apply a LIKE
operator, the database engine is not able to take advantage of linguistic indexes. In other words, a
full index scan or full table scan cannot be avoided. This limitation is no longer available as of
Oracle Database 11g."

But It seems it can't use unique index scan too. This is my test case:

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> create table test as select to_char(rownum) x,to_char(mod(rownum,1000)) y, cast(' ' as char(100)) z from dual connect by level <= 100000;

Tabla creada.

SQL> exec dbms_stats.gather_table_stats(ownname=>user, tabname=>'TEST', method_opt=>'for all columns size 1', cascade=>true);

Procedimiento PL/SQL terminado correctamente.

SQL> CREATE unique INDEX test_idx ON test(NLSSORT(x,'nls_sort=spanish'));

Índice creado.

SQL> CREATE INDEX test_idx2 ON test(NLSSORT(y,'nls_sort=spanish'));

Índice creado.

SQL> SELECT x FROM test WHERE x = '123';
X
----------------------------------------
123

SQL> @plan
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  5fbncq099nf9g, child number 0
-------------------------------------
SELECT x FROM test WHERE x = '123'

Plan hash value: 217508114

-------------------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------
|*  1 |  TABLE ACCESS FULL| TEST |      1 |      1 |   374   (4)|      1 |00:00:00.04 |    1619 |
-------------------------------------------------------------------------------------------------

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

   1 - filter("X"='123')


17 filas seleccionadas.

SQL> SELECT y FROM test WHERE y = '123' order by y;
Y
----------------------------------------
123
.......
.......
123

100 filas seleccionadas.

SQL> @plan
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  85mu6hvnrvd49, child number 0
-------------------------------------
SELECT y FROM test WHERE y = '123' order by y

Plan hash value: 217508114

-------------------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------
|*  1 |  TABLE ACCESS FULL| TEST |      1 |    100 |   375   (4)|    100 |00:00:00.04 |    1625 |
-------------------------------------------------------------------------------------------------

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

   1 - filter("Y"='123')


17 filas seleccionadas.

SQL> SELECT /*+index(test TEST_IDX) */ x FROM test WHERE x = '123';
X
----------------------------------------
123

SQL> @plan
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------
SQL_ID  1w53svu82whqn, child number 0
-------------------------------------
SELECT /*+index(test TEST_IDX) */ x FROM test WHERE x = '123'

Plan hash value: 4153930100

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------
|*  1 |  TABLE ACCESS BY INDEX ROWID| TEST     |      1 |      1 | 20755   (1)|      1 |00:00:00.30 |   20683 |
|   2 |   INDEX FULL SCAN           | TEST_IDX |      1 |    100K|   328   (3)|    100K|00:00:00.01 |     320 |
---------------------------------------------------------------------------------------------------------------

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

   1 - filter("X"='123')


18 filas seleccionadas.

SQL> SELECT /*+index(test TEST_IDX2) */ y FROM test WHERE y = '123';
Y
----------------------------------------
123
.......
.......
123

100 filas seleccionadas.

SQL> @plan
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------
SQL_ID  37yz5ufq7a3b4, child number 0
-------------------------------------
SELECT /*+index(test TEST_IDX2) */ y FROM test WHERE y = '123'

Plan hash value: 34309412

----------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------
|*  1 |  TABLE ACCESS BY INDEX ROWID| TEST      |      1 |    100 |   100K  (1)|    100 |00:00:00.71 |     100K|
|   2 |   INDEX FULL SCAN           | TEST_IDX2 |      1 |    100K|   286   (4)|    100K|00:00:00.10 |     284 |
----------------------------------------------------------------------------------------------------------------

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

   1 - filter("Y"='123')


18 filas seleccionadas.

In the test you can see that oracle don't use the indexes without hint. The reason is the cost in the plan with INDEX FULL SCAN is higher.

As Christian said it doesn't use RANGE SCAN, but it doesn't use INDEX UNIQUE SCAN also. Have anyone know the bug/note in Metalink about this issue? I can't find it. Any workaround?

Many Thanks,

Joaquin Gonzalez

Richard Foote

Hi Joaquin

No, the reason is because you haven't set the necessary nls parameters to perform a linguistic operation rather than the default binary search operation.

Run:

alter session set nls_comp=linguistic;

alter session set nls_sort=spanish;

and try again ...

Cheers

Richard Foote
http://richardfoote.wordpress.com/

Richard Foote

Hi Joaquin

No, the reason is because you haven't set the necessary nls parameters to perform a linguistic operation rather than the default binary search operation.

Run:

alter session set nls_comp=linguistic;

alter session set nls_sort=spanish;

and try again ...

Cheers

Richard Foote
http://richardfoote.wordpress.com/

640834

Richard,

I've done it. It works OK with equal operator. It works as Christian says: only LIKE operator makes a INDEX FULL SCAN:

SQL> SELECT /*+index(test TEST_IDX)*/ x FROM test WHERE x like  '12324243%';

ninguna fila seleccionada

SQL> @plan
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
SQL_ID  0tbfb527jnyp4, child number 0
-------------------------------------
SELECT /*+index(test TEST_IDX)*/ x FROM test WHERE x like  '12324243%'

Plan hash value: 4153930100

----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-T
----------------------------------------------------------------------------------------------
|*  1 |  TABLE ACCESS BY INDEX ROWID| TEST     |      1 |      1 | 20757   (1)|      0 |00:00:
|   2 |   INDEX FULL SCAN           | TEST_IDX |      1 |    100K|   328   (3)|    100K|00:00:
----------------------------------------------------------------------------------------------

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

   1 - filter("X" LIKE '12324243%')


18 filas seleccionadas.

Many Thanks Richard. I've learned a lot from your blog!

Joaquin Gonzalez.