Skip to Main Content

DevOps, CI/CD and Automation

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!

Slow query

454466Feb 11 2009 — edited Feb 12 2009
The 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?
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Mar 12 2009
Added on Feb 11 2009
1 comment
3,545 views