#11017: Oracle LIKEC query doesn't use index
---------------------------------------------------+------------------------
Reporter: jtiai | Owner: nobody
Status: new | Milestone: 1.2
Component: Database layer (models, ORM) | Version: SVN
Resolution: | Keywords: oracle
Stage: Accepted | Has_patch: 0
Needs_docs: 0 | Needs_tests: 0
Needs_better_patch: 0 |
---------------------------------------------------+------------------------
Comment (by jtiai):
Replying to [comment:3 ikelly]:
> Scratch that, I confirmed it myself. I can't get Oracle 10g XE to
formulate a plan using an index for LIKEC queries at all.
>
> However, I still need to see some evidence that changing it back to LIKE
(and finding another fix for #5985) would be an improvement. The three
types of lookups that use LIKEC are {{{contains}}}, {{{startswith}}}, and
{{{endswith}}}. {{{contains}}} and {{{endswith}}} lookups always start
with {{{'%'}}}, so they will never be indexed according to the Oracle
docs. {{{startswith}}} lookups take the form {{{'prefix%'}}} and might
theoretically use the index, but in my testing I was unable to find an
actual example of that form that did.
Both lookups {{{(i)startswith}}} and {{{(i)endswith}}} takes an advantage
of index. In case of {{{endswith}}} index must be created with keyword
"REVERSE". {{{contains}}} lookups can be only indexed with Oracle Text.
And it doesn't matter is is VARCHAR2 or NVARCHAR2 - no effect.
Small sample how table + indices should be created:
{{{
CREATE TABLE TEST_DATA(
ID NUMBER PRIMARY KEY,
TXT VARCHAR(200)
);
# Forward index (startswith)
CREATE INDEX TEST_DATA_TXT TEST_DATA(TXT);
# Reverse index (endswith)
CREATE INDEX TEST_DATA_TXT TEST_DATA(TXT) REVERSE;
# Function based uppercase forward index
# (if istartswith uses UPPER otherwise change to lower accordingly)
CREATE INDEX TEST_DATA_TXT TEST_DATA(UPPER(TXT));
# Function based uppercase reverse index. See previous
CREATE INDEX TEST_DATA_TXT TEST_DATA(UPPER(TXT)) REVERSE;
}}}
Now running different queries (except {{{contains}}} should hit indices).
Few things that might affect it:
* Number of rows is too low.
* Tables are not analyzed.
* Too large columns (2000 chars/bytes I think) doesn't get indexed.
If this doesn't get working, I could rise TAR to Oracle Metalink to see is
there some issues regading usage of LIKEC (it doesn't seem to be much used
anywhere nor documented very well - there might be reason for that)
--
Ticket URL: <http://code.djangoproject.com/ticket/11017#comment:5>
Django <http://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To post to this group, send email to django-updates@googlegroups.com
To unsubscribe from this group, send email to
django-updates+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/django-updates?hl=en
-~----------~----~----~----~------~----~------~--~---
