Oracle-L: RE: Speeding up LIKE '%something%'

From: Jamadagni, Rajendra <Rajendra.Jamadagni_at_espn.com>
Date: Wed, 16 Oct 2002 05:48:42 -0800
Message-ID: <F001.004EA57A.20021016054842@fatcity.com>

Another solution is Intermedia

Raj

Rajendra Jamadagni MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.

QOTD: Any clod can have facts, but having an opinion is an art!

-----Original Message-----
Sent: Wednesday, October 16, 2002 4:29 AM To: Multiple recipients of list ORACLE-L

"Carle, William T (Bill), ALCAS" wrote:
>
> Howdy,
>
> I have a table that has almost 2 million rows called eventqueueentry.
The layout looks like this:
>
> Name Null? Type
> ----------------------------------------- --------

> EVENTID NOT NULL NUMBER(10)
> VER NOT NULL NUMBER(10)
> QUEUETYPE NOT NULL CHAR(16)
> PUBLISHER NOT NULL CHAR(16)
> CREATETIME NOT NULL DATE
> LASTREADTIME DATE
> REMOVETIME DATE
> CONTENTS NOT NULL VARCHAR2(4000)
>
> The users do a query that looks like this:
>
> SELECT EventId, QueueType, Publisher, CreateTime, LastReadTime,
RemoveTime,
> Contents, Ver
> from
> EventQueueEntry where QueueType = 'CodeUpdate' AND Contents LIKE
> '%TrackingEventId=27668677%' ORDER BY EventId
>
> The queuetype field has only 3 different values. The value in the contents
field is close to being unique (high cardinality) but, as you can see, they are picking off a value somewhere in the middle of a varchar2(4000) field. Understandably, their query is slow. Is there anything I can do with an index to speed this up?
>
> Bill Carle
> AT&T
> Database Administrator
> 816-995-3922
> wcarle_at_att.com
>

I think that if it's 'TrackingEventId' which REALLY interests your users (as opposed to any random string of characters within CONTENTS) you should train your users into expressing their query as something similar to

          to_number(substr(CONTENTS,
                           decode(instr(CONTENTS, 'TrackingEventId='),
0, length(CONTENTS),
                                       instr(CONTENTS,
'TrackingEventId=')+16), 8))
                                          = 27668677

(I do *not* guarantee the number of parentheses :) and I assume that the number is always 8-digit long - adapt).
This nice expression would allow you to create a function based index. In fact, you could then create a view above the table which would directly include a TRACKINGEVENTID column defined as above, and, assuming the FBI, your users could query :

SELECT EventId, QueueType, Publisher, CreateTime, LastReadTime, RemoveTime,
Contents, Ver
from
V_EventQueueEntry
where QueueType = 'CodeUpdate'
AND TrackingEventId=27668677
ORDER BY EventId

Another solution would be to add a column, and extract the information as it is inserted (trigger) to fill the column - which you would of course index.

If many different chunks of CONTENTS can be queried, I would consider Intermedia.

-- Regards, Stephane Faroult Oriole Software ----- End Forwarded Message ----- Regards, Stephane Faroult Oriole -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroul INET: sfaroult_at_oriolecorp.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).

-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jamadagni, Rajendra INET: Rajendra.Jamadagni_at_espn.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).

text/plain attachment: ESPN_Disclaimer.txt

Received on Wed Oct 16 2002 - 08:48:42 CDT