Slow query
454466Feb 11 2009 — edited Feb 12 2009The following queries run slow and performance seems to degrade linearly with table size:
SELECT XMLQuery('for $v in /Quote
where fn:contains($v/euid,"euid1")
return <Row>{$v}</Row>'
PASSING OBJECT_VALUE
RETURNING CONTENT)
FROM test_table1;
select X.*
from Test_Table1,
xmltable(
'for $v in /Quote
where fn:contains($v/euid,"teuid253")
return <Row>{$v}</Row>'
PASSING OBJECT_VALUE
) X
;
This query is slow and seems to offer linear performance:
Total Recs Query Time
----------- -------------
15k 1.2 sec
25k 2.0 sec
35k 2.8 sec
45k 3.6 sec
55k 4.3 sec
Each record is 317 bytes.
The table is Binary XML:
CREATE TABLE test_table1 of XMLType XMLType Store as SECUREFILE Binary XML;
Creates an index:
Create index test_table1_euid_idx1 on test_table1
(extractValue(object_value, '/Quote/euid'));
The explain plan:
Select Statement
Filter
Nested Loops
Table access full test_table1
xpath evaluation
xpath evaluation
Does "Table access full test_table1" mean it is doing a linear scan against all rows and programmatically evaluating the xpath expression? The linear performance suggests this.
Here is sample insert:
-- Remove first line: xml declaration
INSERT INTO test_table1 VALUES (
XMLType('
euid1
2008-08-15T18:59:59-05:00
2.22
ONE
None
true
' ));
Is the index created right? Why doesn't the query hit the index?
Using XMLQuery returned nulls for all unmatched rows in the table. Using XMLTABLE creates a view. Is this necessary? Is there a more straight forward way to query with XQuery?