Discussions
Categories
- 196.8K All Categories
- 2.2K Data
- 238 Big Data Appliance
- 1.9K Data Science
- 450.3K Databases
- 221.7K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 550 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 544 SQLcl
- 4K SQL Developer Data Modeler
- 187K SQL & PL/SQL
- 21.3K SQL Developer
- 295.8K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.5K Development Tools
- 107 DevOps
- 3.1K QA/Testing
- 646K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 155 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 18 Java Essentials
- 160 Java 8 Questions
- 86K Java Programming
- 80 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 437 LiveLabs
- 38 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 171 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 232 Portuguese
Linguistic Index can't do UNIQUE/RANGE SCAN in 10.2, any workaround?

640834
Member Posts: 25
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
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
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
-
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
-
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,
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.