RE: strategies for optimizing 'like' operations
From: Oxnard Montalvo <oxnard_at_carolina.rr.com>
Date: Sun, 9 Mar 2008 10:52:43 -0400
Message-ID: <16929988.629171205074363429.JavaMail.root@cdptpa-web14-z02>
Date: Sun, 9 Mar 2008 10:52:43 -0400
Message-ID: <16929988.629171205074363429.JavaMail.root@cdptpa-web14-z02>
This idea depends on your data. In my situation it worked due to the layout of
data and knowning the questions which would be asked. So it may not work for
you.
In my case most of the data has some number of leading zeros and is always 16 bytes long. so I pre-figured the cardinality for the cases and placed it into the query using the cardinality hint. The predicates the users would leave off the leading zeros. This worked well for us.
A much more generic solution is to use Oracle Text, but of course that's not much good in an online system.
Ox
-- http://www.freelists.org/webpage/oracle-lReceived on Sun Mar 09 2008 - 09:52:43 CDT