No question at this time
The DBA-Village forum
|Ever registered users||48392|
|Total active users||1527|
|Act. users last 24h||5|
|Act. users last hour||3|
|Registered user hits last week||277|
|Registered user hits last month||713||
If you think this item violates copyrights, please click here
||James Alcroft, United Kingdom
||Apr 25, 2019, 11:16, 82 days ago
We are have performances issues when using ctxsys indexes.
We have table, pve_cont_rep, contains a number of columns including filter_data_xml being a XMLTYPE size 2000.
Index is defined as:
CREATE INDEX IDX_PVE_CONT_REP_FDX ON PVE_CONT_REP (FILTER_DATA_XML)
INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS (
'TRANSACTIONAL FILTER CTXSYS.NULL_FILTER SECTION GROUP FILTER_DATA_XML DATASTORE CTXSYS.DEFAULT_DATASTORE'
The filter_data_xml is populated from a trigger which data looking like:
<Name>Dr Dave Davison</Name>
<Address1>1 School Lane</Address1>
We have a view, live_pve_cont_rep. It is more complex, but same results if this view is defined as select * from pvw_cont_rep. Performance issue is not the view.
As schema owner, running the following query returns data in about 1, maybe 2 seconds:
WHERE CONTAINS(FILTER_DATA_XML, ASSURED_ORACLE_TEXT_PARSER.simpleSearch('+CountryCode:(+US) +Person:(+Dr +Dave)')) > 0;
Explain plan is using the index, as expected, and has CARDINALITY of 6.
Exactly the same if selecting from the live_pve_cont_rep view.
My problem is that if i run the same query as a user, which has synonyms pointing to the exact same table and view, the query takes 25+ seconds.
Explain plan is NOT using the index, as expected, and has CARDINALITY of 11528.
Explains why it is slow, but don't understand why this is happening.
Are we creating the ctxsys index incorrectly - have tried a number of different options.
Any suggestions gratefully received.
||Reply - Top of page