"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
> [EMAIL PROTECTED]
>
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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
