No question at this time
DBA Top 10
1 A. Kavsek 8700
2 B. Vroman 5900
3 M. Cadot 5400
4 P. Wisse 4800
5 T. Boles 3700
6 J. Schnackenberg 2100
7 . Lauri 1500
8 R. Pattyn 700
9 T. P 600
9 J. PĂ©ran 600
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48409
Total active users1518
Act. users last 24h5
Act. users last hour0
Registered user hits last week284
Registered user hits last month997
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
If you believe this index would help in performanc...... Lauri Jul 18, 2019, 13:57

Follow up by mail Click here


Subject: CTX indexes
Author: James Alcroft, United Kingdom
Date: Apr 25, 2019, 11:16, 119 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

Subject: Re-CTX indexes
Author: Lauri, Netherlands
Date: Jul 18, 2019, 13:57, 35 days ago
Message: If you believe this index would help in performance, use a hint to confirm it.
You probably have to gather statistics for this index first.
Then, run your query again. Check if it uses the index or not, and compare with the execution using a hint.
Can you post your results?
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here