Saturday, October 28, 2006

If you have application running on Oracle 8.1.7.4 and you are massively using CONTEXT indexes, you better analyse the queries where context indexes are involved, before you decide to migrate to 10.2.0.x.This is because execution paths might be way different in 10.2.0.x that may lead to performance degradation.Here is one example where you should consider query modification in order to avoid bad performances.Let assume that you have a query that has OR-condition where OR-predicates use CONTEXT operators.

I have opened a SR with Oracle Support on this one, and the answers that I got were very interesting and make sense.The key reason for such a behaviuor is how Oracle optimizer handles OR-expansion condition where OR-predicates use CONTEXT operators.Oracle optimizer usually probes OR-expansion to see if a cheaper index based plan can be produced. But, Or-expansion is not legitimate if OR-predicate has a CONTEXT operator, because some of the new "concatenated" query blocks will loose context of this operator.So, generally, the plan generated in 8.1.7.4 is not legitimate, even though it is very good one, performance wise.Anyway, if you want to use plan similar to the one you used to have, then you better transform your queries.I modified the query shown above, to this one:

end the response time was much better compared to both previous plans.

Now, the questions are:How this can be implemented in the application?How many queries similar to this one are being used in the application?Is it worth the effort?Can your application live with the execution paths based on full table scan?

You better answer to these questions before you decide what your next step would be.

Friday, October 27, 2006

It’s been a long time since my last post on this blog.Ok, one of the reasons for this “absenteeism” was my new job. Honestly, I didn’t have too much time to write anything except reports, research papers, proof of concepts, etc.Although I commute more than I used to, I don’t mind since I use every chance to read something interesting while riding on CTA trains.In three months, only in train cars I’ve read three books, two for the first time and one for the second time (just because it is perfect).