This query is driving me nuts ;-).
I finally managed to get an inner join working containg two XMLTABLE expressions with corresponding indices. @Paulzip pointed me to the fact that the cbo doesn't like mixing SQL types. Using only SQL 89 syntax worked for me whereas ANSI and appy syntax didn't work.
My working query is
SELECT
x1.*, x2.*, p.entity_id, po.entity_id
from person po, patient p
, XMLTABLE(xmlnamespaces(default 'http://www.dudu.com/model'), '/Person' PASSING po.ENTITY COLUMNS
"Vorname" VARCHAR2(100) PATH 'Vorname'
, "Nachname" VARCHAR2(100) PATH 'Nachname'
) x1
,XMLTABLE(xmlnamespaces(default 'http://www.dudu.com/model'), '/*' PASSING p.ENTITY COLUMNS
"Personalnummer" VARCHAR2(8) PATH 'Personalnummer'
, "Versicherungsverhaeltnis" VARCHAR2(100) PATH 'Versicherungsverhaeltnis'
) x2
where po.entity_id = p.r_person
and x1."Vorname" = 'Freida'
order by x1."Nachname", x2."Personalnummer" ASC;
This query is very fast, explain plan shows it using the indices:
Plan hash value: 3925823853
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 95 | 11970 | 2268 (1)| 00:00:01 |
| 1 | SORT ORDER BY | | 95 | 11970 | 2268 (1)| 00:00:01 |
| 2 | NESTED LOOPS | | 95 | 11970 | 2267 (1)| 00:00:01 |
| 3 | NESTED LOOPS | | 95 | 11970 | 2267 (1)| 00:00:01 |
| 4 | NESTED LOOPS | | 95 | 9500 | 1982 (1)| 00:00:01 |
| 5 | NESTED LOOPS | | 95 | 5130 | 1697 (1)| 00:00:01 |
|* 6 | TABLE ACCESS FULL | XIDX_PERSON_XT | 95 | 2375 | 1602 (1)| 00:00:01 |
| 7 | TABLE ACCESS BY USER ROWID | PERSON | 1 | 29 | 1 (0)| 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID BATCHED| PATIENT | 1 | 46 | 3 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | IDX_PATIENT_R_PERSON | 1 | | 2 (0)| 00:00:01 |
|* 10 | INDEX RANGE SCAN | SYS2447413_2447414_RID_IDX | 1 | | 2 (0)| 00:00:01 |
| 11 | TABLE ACCESS BY INDEX ROWID | XIDX_PATIENT_XT | 1 | 26 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - filter("SYS_SXI_2"."VORNAME"='Freida')
9 - access("PO"."ENTITY_ID"="P"."R_PERSON")
10 - access("P".ROWID="SYS_SXI_3"."RID")
When I change the query to include a left outer join between the tables
SELECT
x1.*, x2.*, p.entity_id, po.entity_id
from person po, patient p
, XMLTABLE(xmlnamespaces(default 'http://www.dudu.com/model'), '/Person' PASSING po.ENTITY COLUMNS
"Vorname" VARCHAR2(100) PATH 'Vorname'
, "Nachname" VARCHAR2(100) PATH 'Nachname'
) x1
,XMLTABLE(xmlnamespaces(default 'http://www.dudu.com/model'), '/*' PASSING p.ENTITY COLUMNS
"Personalnummer" VARCHAR2(8) PATH 'Personalnummer'
, "Versicherungsverhaeltnis" VARCHAR2(100) PATH 'Versicherungsverhaeltnis'
) x2
where po.entity_id = p.r_person(+)
and x1."Vorname" = 'Freida'
order by x1."Nachname", x2."Personalnummer" ASC;
, it reverts to Full Table Scans, not even using the XMLIndex Indices for extraction.
Plan hash value: 3208873812
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2825K| 4920M| | 20M (3)| 00:13:18 |
| 1 | SORT ORDER BY | | 2825K| 4920M| 5518M| 20M (3)| 00:13:18 |
| 2 | NESTED LOOPS | | 2825K| 4920M| | 19M (3)| 00:12:36 |
|* 3 | FILTER | | | | | | |
|* 4 | HASH JOIN OUTER | | 691K| 1203M| 457M| 138K (1)| 00:00:06 |
| 5 | TABLE ACCESS FULL| PERSON | 691K| 449M| | 18777 (1)| 00:00:01 |
| 6 | TABLE ACCESS FULL| PATIENT | 691K| 753M| | 59763 (1)| 00:00:03 |
|* 7 | XPATH EVALUATION | | | | | | |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(EXISTSNODE(SYS_MAKEXML(0,"P"."SYS_NC00008$"),'/*','
xmlns:oraxq_defpfx="http://www.dudu.com/model"')=1)
4 - access("PO"."ENTITY_ID"="P"."R_PERSON"(+))
7 - filter("P"."C_01$" IS NOT NULL AND
CAST(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL(SYS_XQEXTRACT("P"."C_01$",'/Person/Vorname','x
mlns="http://www.dudu.com/model" '),0,0,21004288,0),50,1,2) AS
VARCHAR2(100) )='Freida')
I'll try to give a simplified version of my setup:
CREATE TABLE "PERF_TEST_SYS"."PERSON"
("ENTITY_ID" RAW(16),
"ENTITY" "SYS"."XMLTYPE"
CONSTRAINT "PK_PERSON" PRIMARY KEY ("ENTITY_ID"))
CREATE TABLE "BEIREFA_PERF_TEST_SYS"."PATIENT"
("ENTITY_ID" RAW(16),
"ENTITY" "SYS"."XMLTYPE" ,
"R_PERSON" RAW(16),
CONSTRAINT "PK_PATIENT" PRIMARY KEY ("ENTITY_ID"))
Both tables have about 600k entries. These are the XML indices I created:
CREATE INDEX "XIDX_PERSON" ON "PERSON"("ENTITY") INDEXTYPE IS XDB.XMLINDEX
PARAMETERS('
XMLTable XIDX_PERSON_XT
XMLNAMESPACES (DEFAULT ''http://www.dudu.com/model''), ''/Person'' COLUMNS
Vorname VARCHAR2(100) PATH ''Vorname'',
Nachname VARCHAR2(100) PATH ''Nachname''
');
CREATE INDEX "XIDX_PATIENT" ON "PATIENT"("ENTITY") INDEXTYPE IS XDB.XMLINDEX
PARAMETERS ('
XMLTable XIDX_PATIENT_XT
XMLNAMESPACES (DEFAULT ''http://www.dudu.com/model''), ''/*''
COLUMNS
Personalnummer VARCHAR2(8) PATH ''Personalnummer'',
Versicherungsverhaeltnis VARCHAR2(100) PATH ''Versicherungsverhaeltnis''
');
Thanks for any help.