RE: Speeding up LIKE '%something%'

From: Robson, Peter <pgro_at_bgs.ac.uk>
Date: Wed, 16 Oct 2002 02:43:28 -0800
Message-ID: <F001.004EA2B3.20021016024328@fatcity.com>

Seems to me there may be a case for a little normalisation here - do I understand the field 'contents' to include aggregates of various data attributes? Oh dear...

Although a functional index would probably help, it is simply masking a major design flaw. Get that corrected, and the basic integrity of the data improves straight away.

peter
edinburgh

> -----Original Message-----
> From: Stephane Faroult [mailto:sfaroult_at_oriolecorp.com]
> Sent: 16 October 2002 09:29
> To: Multiple recipients of list ORACLE-L
> Subject: Speeding up LIKE '%something%'
>
>
> "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).
>

---

This e-mail message, and any files transmitted with it, are confidential and intended solely for the use of the addressee. If this message was not addressed to you, you have received it in error and any copying, distribution or other use of any part of it is strictly prohibited. Any views or opinions presented are solely those of the sender and do not necessarily represent those of the British Geological Survey. The security of e-mail communication cannot be guaranteed and the BGS accepts no liability for claims arising as a result of the use of this medium to transmit messages from or to the BGS. The BGS cannot accept any responsibility for viruses, so please

scan all attachments.                            http://www.bgs.ac.uk
*********************************************************************

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Robson, Peter
  INET: pgro_at_bgs.ac.uk

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).

Received on Wed Oct 16 2002 - 05:43:28 CDT