Forum Stats

  • 3,825,283 Users
  • 2,260,497 Discussions
  • 7,896,478 Comments

Discussions

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

640834
640834 Member Posts: 25
edited Dec 17, 2008 7:52AM in General Database Discussions
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
Tagged:

Best Answer

  • Richard Foote
    Richard Foote Member Posts: 483
    edited Dec 17, 2008 7:46AM Answer ✓
    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/

Answers

  • Richard Foote
    Richard Foote Member Posts: 483
    edited Dec 17, 2008 7:46AM Answer ✓
    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
    640834 Member Posts: 25
    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.
This discussion has been closed.