No question at this time
DBA Top 10
1 A. Kavsek 8600
2 M. Cadot 7000
3 B. Vroman 6100
4 T. Boles 5150
5 J. Schnackenberg 4100
6 P. Wisse 3000
7 T. P 1200
8 G. Lambregts 1100
8 . Lauri 1100
10 R. Pattyn 800
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48371
Total active users1539
Act. users last 24h2
Act. users last hour0
Registered user hits last week106
Registered user hits last month948
Go up

CTX indexes
Next thread: impdp
Prev thread: ora-16665 timeout waiting for the result from a database

Message Score Author Date
Hi We are have performances issues when using c...... James Alcroft Apr 25, 2019, 11:16

Follow up by mail Click here


Subject: CTX indexes
Author: James Alcroft, United Kingdom
Date: Apr 25, 2019, 11:16, 24 days ago
Os info: Windows 2012
Oracle info: 12.2
Message: Hi

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:
<Contact>
<Person>
<Initials/>
<Name>Dr Dave Davison</Name>
<Firstname>Dave</Firstname>
<Surname>Davison</Surname>
<Email>dave@email.com</Email>
</Person>
<Address>
<Institute>Excellent Company</Institute>
<Department/>
<Address1>1 School Lane</Address1>
<Address2>Eaton Socon</Address2>
<Zip>PE19 8HR</Zip>
<City>St Neots</City>
<State/>
<StateCode/>
<Country>USA</Country>
<CountryCode>US</CountryCode>
</Address>
<Phone>01223456789</Phone>
<AccountNumber/>
<LastUsedDate>22-AUG-13</LastUsedDate>
</Contact>

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:
select *
from PVE_CONT_REP
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.

James
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here