Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

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

640834Dec 17 2008 — edited Dec 17 2008
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
This post has been answered by Richard Foote on Dec 17 2008
Jump to Answer

Comments

Processing
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jan 14 2009
Added on Dec 17 2008
2 comments
152 views