Solved

Slow search on Indexed XMLTYPE Column

Posted on 2004-08-21
6
1,564 Views
Last Modified: 2008-02-01
HI friends,

I have one table with one column defined as XMLType and 4 other column as number types.

field1 number(1),field2 number(1),field3 number(1),field4 number(1),field5 xmltype

The table is partitioned by range on field1 column. (10 partitions ranging 0-9)
There is primary key on field2 column. A unique index is created on (field1,field3) columns
XMLType column ie field5 is indexed using Oracle Text Manager CONTEXT with PATH_SECTION_GROUP as an additional preference. The DEFAULT_MEMORY_SIZE Changed to 49 MB using Ctx_Adm.SetParameter.

For 100 to 1000 records, its very fast for queries using the contains operator. ie say about .05 seconds or near to that. But after loading about 100,000 records, then its damn slow, say about 30-45 seconds.

I am using the query
SELECT PTX.FIELD5.extract('/TITLE/text()').getStringVal() FROM TABXML  PARTITION (TABXMLPART1) PTX WHERE CONTAINS(PTX.FIELD5,'KUWAIT INPATH(//ADDRESS)',0) > 0

Even after executing the Optimize Fast function also does not makes any performance gain. My Oracle version is 9.0.1.3. The search engine is developed on Java.

Please guide me to how to build performance in this. I am ready to start the process from the scratch.

Thanks
Jim
0
Comment
Question by:jimaricat072100
  • 3
  • 2
6 Comments
 
LVL 6

Expert Comment

by:morphman
ID: 11858768
You need a functional index on the xmltype column.

Here is an example I found.

create index IPURCHASEORDERUSER on PURCHASEORDER PO (
substr(sys.xmlType.getStringVal(sys.xmlType.extract
(PO.PODOCUMENT,'/PurchaseOrder/User/text()')
),1,24)

);
0
 
LVL 1

Author Comment

by:jimaricat072100
ID: 11862470
thanks morphman for the response,

so you mean to drop all other indexes and put only functional index will improve the performance. Or should i add this functional index to the current setup.
And one more doubt. Since my xml documents having diferrent root element, how can your indexing strategy can be modified. Should i put similar functional indexes on each of the elements.

0
 
LVL 6

Accepted Solution

by:
morphman earned 250 total points
ID: 11863431
You will only need to drop other indexes containing the xmltype column. (if that is all indexes, so be it).

I am not sure whether you can create multiple functional indexes on the same column. If you use several elements, I would certainly try to index each element you wish to query with a separate functional index, but im not sure Oracle will alow it.

You would have to try it.

If you really need more performance out of querying your XML documents, it may be better to store them in a different manner than using XMLType objects.

eg. You could parse all of your xml documents when loading them into a database, and place the elements you wish to query into a separate lookup table which will point directly to the XML document. These standard text columns will then get indexed normally and give the performance you require.

Take a look at

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:18017595372392

Which discusses the benefits of each type of XML storage.
0
 
LVL 1

Author Comment

by:jimaricat072100
ID: 11945293
Sorry for the delay.

I did as morphan said, but the performance was same and moreover the maintenance of each functional indexes is bit complicated.

Thankyou for the alternate solution provided. I am trying to store the XMLDocuments using File Store. It seems the performance is much better with less records. But not much as i expected. Anyhow i am trying my level best. From the asktom site, i found out another issue. If the xmldocuments are not stored against a defined schema, then the search will not use the context domain index. I have approached metalink and submitted a TAR. I am waiting for the response.

Anyway morphan i'll surely give points, but i appreciate if this discussion can go further in deeper.
0
 
LVL 1

Author Comment

by:jimaricat072100
ID: 12145180
Actually i was waiting for few more tips regarding this.

Anyhow my points are there to morphan, and thankyou for the support.

The poblem which i raised in this is closely matches with a Oracle Bug, and since there is no patch version for the version 9.0.1.3, i have to go for 9.2 version.

Thankyou
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Oracle RAC 12c 8 87
PL/SQL More than one element associated with another element 7 45
Require data to appear on a single line 2 69
grant user/role question 11 32
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question