hi,
i have a database storing XML documents.
The main table contains the nodes of the document, the other tables contain data for each node (depending on the node's type : ELE, Text, PI, ...)
My test document has 115000 nodes.
the export of the document(extracting all informations from database and writing XML file on disk) takes 30s with Oracle and 5mn with Postgresql.
The tests have been made on different systems
- Sun systems :
- solaris8 : 16 cpu and 64Gb RAM
- solaris8 : 2cpu and 8Gb RAM
- Windows Systems :
- WinNT : 1 cpu(PIV) and 1Gb RAM
- WinXP : 1 cpu(centrino) and 512 RAM
the times are always the same, except with the centrino for which it takes 1 min.
So i don't understand such differences.
here is my main query (on the main table for getting the children of a node) and the execution plan for PostgreSQL and Oracle :
-Query :
SELECT *
FROM xdb_child c1
WHERE c1.doc_id = 100
AND c1.ele_id = 2589
AND c1.isremoved = 0
AND c1.evolution =
(SELECT
MAX (evolution)
FROM xdb_child c2
WHERE c2.doc_id = c1.doc_id
AND c2.ele_id = c1.ele_id
AND c2.evolution <= 0
AND c2.child_id = c1.child_id
AND c2.child_class = c1.child_class) ORDER BY c1.evolution, c1.indx
-Postgresql plan :
"Sort (cost=33.19..33.20 rows=1 width=28)"
" Sort Key: evolution, indx"
" -> Index Scan using index_xdb_child_1 on xdb_child c1 (cost=0.00..33.18 rows=1 width=28)"
" Index Cond: ((ele_id = 2589) AND (doc_id = 100) AND (isremoved = 0))"
" Filter: (evolution = (subplan))"
" SubPlan"
" -> Result (cost=4.34..4.35 rows=1 width=0)"
" InitPlan"
" -> Limit (cost=0.00..4.34 rows=1 width=4)"
" -> Index Scan Backward using index_xdb_child_2 on xdb_child c2 (cost=0.00..4.34 rows=1 width=4)"
" Index Cond: ((ele_id = $1) AND (doc_id = $0) AND (child_id = $2) AND (child_class = $3) AND (evolution <= 0))"
" Filter: (evolution IS NOT NULL)"
-Oracle plan (cost 14):
Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
SELECT STATEMENT Optimizer Mode=CHOOSE 1 14
SORT ORDER BY 1 4 K 14
TABLE ACCESS BY INDEX ROWID XDB_CHILD 1 4 K 4
INDEX RANGE SCAN INDEX_XDB_CHILD_1 1 3
SORT AGGREGATE 1 65
FIRST ROW 1 65 3
INDEX RANGE SCAN (MIN/MAX) INDEX_XDB_CHILD_2 8 M 3
Regards
William
This e-mail is intended only for the above addressee. It may contain
privileged information. If you are not the addressee you must not copy,
distribute, disclose or use any of the information in it. If you have
received it in error please delete it and immediately notify the sender.
Security Notice: all e-mail, sent to or from this address, may be
accessed by someone other than the recipient, for system management and
security reasons. This access is controlled under Regulation of
Investigatory Powers Act 2000, Lawful Business Practises.
