#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 ikelly):
Replying to [comment:5 jtiai]:
> In case of {{{endswith}}} index must be created with keyword "REVERSE".
From what I understand (I'm at home right now and don't have an Oracle
database handy to actually test on), this doesn't work. Reverse-key
indexes are used to provide an optimization for monotonically increasing
columns with lots of concurrent inserts, at the cost of not being able to
use the index for range scans -- not to allow reverse LIKE queries. See
http://richardfoote.wordpress.com/2008/01/16/introduction-to-reverse-key-
indexes-part-ii-another-myth-bites-the-dust/
An actual solution (as mentioned in the same article) is to create an
index of the {{{REVERSE}}} '''function'''. This would require a
refactoring of the way the lookups are implemented in Django backends,
which is something that should be done anyway.
The suggestion of creating an uppercase index in addition to the regular
index is also a useful one, for a separate ticket.
Back on the {{{LIKEC}}} topic, there is no question that a query of the
form {{{WHERE TXT LIKE 'foo%'}}} will take advantage of an index under the
right circumstances. The main obstacle as I see it is #5985. We weren't
able to reproduce that bug ourselves, and so it would be challenging to
confirm the effectiveness of an alternative fix. That's why I would like
to see an example confirming that {{{LIKE}}} will use an index, but
{{{LIKEC}}} does not, before considering this change.
--
Ticket URL: <http://code.djangoproject.com/ticket/11017#comment:6>
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
-~----------~----~----~----~------~----~------~--~---
