Oracle
使用 XPATH extractValue 的基於函式的索引
// Oracle Version 11.2.0.4 Standard Edition // OS RedHat我正在嘗試基於使用 XML 對像在大表上創建函式索引。
所以我做了一些事情，比如：
CREATE INDEX test_function_itemgroup_id ON test_function ( extractValue ( RECORD, '/Product/PublishingDetail/ItemGroup/ItemGroupId', 'xmlns="http://www.editeur.org/onix/3.0/reference"' ) )因此，當我有如下查詢時：
select PRODUCT_ID from TEST_FUNCTION pr where extractValue ( RECORD, '/Product/PublishingDetail/ItemGroup/ItemGroupId', 'xmlns="http://www.editeur.org/onix/3.0/reference"' ) = 'NAME-CLASS'使用了函式索引，我知道我有正確的 xml 命名空間。
當我做以下事情時：
CREATE INDEX test_function_nbd ON test_function ( extractValue ( RECORD, '/Product/Extras[ExtrasIDType="RECORDCREATEDBY"]/IDValue', 'xmlns="http://www.editeur.org/onix/3.0/reference"' ) ) select PRODUCT_ID from TEST_FUNCTION pr where extractValue ( RECORD, '/Product/Extras[ExtrasIDType="RECORDCREATEDBY"]/IDValue', 'xmlns="http://www.editeur.org/onix/3.0/reference"' ) = 'Other'系統執行全表掃描，而不是使用函式索引。
我使用了一個 XMLIndex，但是它的性能太慢了，並且似乎不允許使用基於函式的索引。
有沒有人對如何進行此操作有任何建議？
更新：
添加了完整的 xmlns，因為現在有足夠的聲譽
表上沒有其他索引。
看起來優化器似乎正在將 XPATH 轉換為兩個不同的查詢。
/Product/Extras/ExtrasIDType 和 /Product/Extras/IDValue
沒有被函式索引。
在我的特殊情況下，我不能單獨索引這些，因為它們可以有多個值，並且基於函式的索引不能使用多個值。
此外，如果我必須使用兩個基於函式的索引，系統將進行全表掃描。（根據 oracle 11 文件）
使用
/*+ NO_XML_QUERY_REWRITE */提示。我的以下環境11.2.0.3.0在Linux_x86-64平台上，Enterprise Edition.create table xml_table (c1 xmltype) pctfree 99;(
PCTFREE 99，只是將表分散到許多塊上的一個簡單技巧，從而使全表掃描更加昂貴和低效。在這種情況下為 1 行/塊。)insert into xml_table select xmltype(' <stuff> <something> <x1>' || rownum || '</x1> <x2>' || rownum || '</x2> </something> </stuff> ') from dual connect by level <= 10000; commit;所以我有一個包含 10000 行的表，其 XMLType 值如下：
<stuff> <something> <x1>1</x1> <x2>1</x2> </something> </stuff> <stuff> <something> <x1>2</x1> <x2>2</x2> </something> </stuff> ...我想執行這樣的查詢，比如說：
select * from xml_table where extractvalue(c1, '/stuff/something[x1=5]/x2/text()') = '5';創建基於函式的索引並收集統計資訊：
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);執行查詢，看看發生了什麼：
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>檢查計劃和執行時統計資訊：
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')數據庫轉換了 XPath 表達式，因此基於函式的索引不再是一個有效的候選對象，因此它是一個具有超過 10000 次緩衝區獲取的全表掃描。
在使用上述提示執行時：
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>計劃和執行時統計：
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')好多了。
還要注意計劃中的成本列。第一個計劃的成本是 226K，但是禁用 XML 重寫後，第二個計劃的成本是 2。我沒有深入研究這個主題，但似乎這是另一種查詢轉換，不是基於成本。