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 184.108.40.206. 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.