Use the /*+ NO_XML_QUERY_REWRITE */
hint. My environment for the below is 11.2.0.3.0
on Linux_x86-64
platform, Enterprise Edition
.
create table xml_table (c1 xmltype) pctfree 99;
(PCTFREE 99
, just a simple trick to spread the table over many blocks, thus making full table scan even more costly and inefficient. 1 row/block in this case.)
insert into xml_table select
xmltype('
<stuff>
<something>
<x1>' || rownum || '</x1>
<x2>' || rownum || '</x2>
</something>
</stuff>
')
from dual connect by level <= 10000;
commit;
So I have a table with 10000 rows, with XMLType values like:
<stuff>
<something>
<x1>1</x1>
<x2>1</x2>
</something>
</stuff>
<stuff>
<something>
<x1>2</x1>
<x2>2</x2>
</something>
</stuff>
...
And I want to run queries like, let's say:
select * from xml_table
where extractvalue(c1, '/stuff/something[x1=5]/x2/text()') = '5';
Create the function-based index and gather statistics:
create index xml_table_fbi1 on
xml_table(extractvalue(c1, '/stuff/something[x1=5]/x2/text()'));
exec dbms_stats.gather_table_stats(user, 'XML_TABLE', no_invalidate=>false);
Run the query and see what happened:
alter session set statistics_level=all;
select * from xml_table where extractvalue(c1, '/stuff/something[x1=5]/x2/text()') = '5';
C1
--------------------
<stuff>
<something>
<x1>5</x1>
<x2>5</x2>
</something>
</stuff>
Check the plan and runtime statistics:
select * from table(dbms_xplan.display_cursor(format=>'allstats last cost'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
SQL_ID a78z6npnp94va, child number 0
-------------------------------------
select * from xml_table where extractvalue(c1,
'/stuff/something[x1=5]/x2/text()') = '5'
Plan hash value: 3307077916
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 226K(100)| 1 |00:00:00.43 | 10239 |
|* 1 | FILTER | | 1 | | | 1 |00:00:00.43 | 10239 |
| 2 | TABLE ACCESS FULL | XML_TABLE | 1 | 10000 | 2764 (2)| 10000 |00:00:00.04 | 10239 |
| 3 | SORT AGGREGATE | | 10000 | 1 | | 10000 |00:00:00.37 | 0 |
| 4 | NESTED LOOPS SEMI | | 10000 | 667K| 223K (1)| 1 |00:00:00.28 | 0 |
| 5 | XPATH EVALUATION | | 10000 | | | 10000 |00:00:00.12 | 0 |
| 6 | XPATH EVALUATION | | 10000 | | | 1 |00:00:00.14 | 0 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(SYS_XMLTYPE2SQL(SYS_XQSEQ2CON(SYS_XQEXTRACT(,'/something/x2/text()')))
='5')
The database transformed the XPath expression, so the function-based index is not a valid candidate anymore, so it is a full table scan with over 10000 buffer gets.
While running the same with the above hint:
select /*+ NO_XML_QUERY_REWRITE */ * from xml_table where
extractvalue(c1, '/stuff/something[x1=5]/x2/text()') = '5';
C1
--------------------
<stuff>
<something>
<x1>5</x1>
<x2>5</x2>
</something>
</stuff>
Plan and runtime statistics:
select * from table(dbms_xplan.display_cursor(format=>'allstats last cost'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------
SQL_ID gby3tfs2z7mqm, child number 0
-------------------------------------
select /*+ NO_XML_QUERY_REWRITE */ * from xml_table where
extractvalue(c1, '/stuff/something[x1=5]/x2/text()') = '5'
Plan hash value: 2259369741
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2 (100)| 1 |00:00:00.01 | 2 |
| 1 | TABLE ACCESS BY INDEX ROWID| XML_TABLE | 1 | 1 | 2 (0)| 1 |00:00:00.01 | 2 |
|* 2 | INDEX RANGE SCAN | XML_TABLE_FBI1 | 1 | 1 | 1 (0)| 1 |00:00:00.01 | 1 |
---------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("XML_TABLE"."SYS_NC00003$"='5')
Much better.
Also notice the Cost column in the plans. The cost of the first plan was 226K, but with disabling XML rewrite, the cost of the second plan was 2. I didn't investigate this topic deeper, but it seems this is another kind of query transformation, that is not based on the cost.
xmls=
in your first index, butxmlns=
in your query, but that may be just a typo. 2. Just because you have an index, the optimizer will not use it automatically in every possible case. Try forcing the usage of the index with an INDEX hint. If that works, then the optimizer earlier simply decided not to use it, because of inaccurate statistics, or the distribution of your data.