Return-Path: Received: from ensim.rackshack.net (root@localhost) by orafaq.net (8.11.6/8.11.6) with ESMTP id g9GFlrH08073 for ; Wed, 16 Oct 2002 10:47:53 -0500 X-ClientAddr: 209.68.248.164 Received: from newsfeed.cts.com (newsfeed.cts.com [209.68.248.164]) by ensim.rackshack.net (8.11.6/8.11.6) with ESMTP id g9GFloG08061 for ; Wed, 16 Oct 2002 10:47:50 -0500 Received: from fatcity.UUCP (uucp@localhost) by newsfeed.cts.com (8.9.3/8.9.3) with UUCP id GAA83123; Wed, 16 Oct 2002 06:29:27 -0700 (PDT) Received: by fatcity.com (26-Feb-2001/v1.0g-b72/bab) via UUCP id 004EA57A; Wed, 16 Oct 2002 05:48:42 -0800 Message-ID: Date: Wed, 16 Oct 2002 05:48:42 -0800 To: Multiple recipients of list ORACLE-L X-Comment: Oracle RDBMS Community Forum X-Sender: "Jamadagni, Rajendra" Sender: root@fatcity.com Reply-To: ORACLE-L@fatcity.com Errors-To: ML-ERRORS@fatcity.com From: "Jamadagni, Rajendra" Subject: RE: Speeding up LIKE '%something%' Organization: Fat City Network Services, San Diego, California X-ListServer: v1.0g, build 72; ListGuru (c) 1996-2001 Bruce A. Bergman Precedence: bulk Mime-Version: 1.0 Content-Type: multipart/mixed; boundary="----=_NextPartTM-000-7deb1883-6323-4398-816e-ce621cd7c0cd" ------=_NextPartTM-000-7deb1883-6323-4398-816e-ce621cd7c0cd Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C27512.23718AE0" ------_=_NextPart_001_01C27512.23718AE0 Content-Type: text/plain; charset="iso-8859-1" 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@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@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@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). ------_=_NextPart_001_01C27512.23718AE0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable RE: Speeding up LIKE '%something%'

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-----
From: Stephane Faroult [mailto:sfaroult@oriolecorp.com]
Sent: Wednesday, October 16, 2002 4:29 AM
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           &n= bsp;           &n= bsp;           &n= bsp;  Null?    Type
>  ----------------------------------------- = -------- ----------------------------
>  = EVENTID           = ;            = ;            NOT = NULL NUMBER(10)
>  = VER           &nb= sp;           &nb= sp;           &nb= sp;   NOT NULL NUMBER(10)
>  = QUEUETYPE          &nb= sp;           &nb= sp;          NOT NULL = CHAR(16)
>  = PUBLISHER          &nb= sp;           &nb= sp;          NOT NULL = CHAR(16)
>  = CREATETIME          &n= bsp;           &n= bsp;         NOT NULL = DATE
>  = LASTREADTIME          =             =             =      DATE
>  = REMOVETIME          &n= bsp;           &n= bsp;           &n= bsp;      DATE
>  = CONTENTS          &nbs= p;           &nbs= p;           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 =3D = 'CodeUpdate' AND Contents LIKE
>   '%TrackingEventId=3D27668677%' = 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@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,
          &nb= sp;           &nb= sp;    decode(instr(CONTENTS, = 'TrackingEventId=3D'),
0, length(CONTENTS),
          &nb= sp;           &nb= sp;           &nb= sp;    instr(CONTENTS,
'TrackingEventId=3D')+16), 8))
          &nb= sp;           &nb= sp;           &nb= sp;       =3D 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 =3D 'CodeUpdate'
AND TrackingEventId=3D27668677
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@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@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).

------_=_NextPart_001_01C27512.23718AE0-- ------=_NextPartTM-000-7deb1883-6323-4398-816e-ce621cd7c0cd Content-Type: text/plain; name="ESPN_Disclaimer.txt" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="ESPN_Disclaimer.txt" *********************************************************************This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*********************************************************************1 ------=_NextPartTM-000-7deb1883-6323-4398-816e-ce621cd7c0cd-- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jamadagni, Rajendra INET: Rajendra.Jamadagni@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@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).