If that content is statics, create a column that called TRACKINGEVENTID
stores 'TrackingEventId=nnnnnnnn'. Is EVENTID the same as TRACKINGEVENTID?
Partition it by QUEUETYPE so that it only goes after one patition instead
the entire table if partition does not affect other SQLs.
Bing Wong
-----Original Message-----
Sent: Tuesday, October 15, 2002 2:10 PM
To: Multiple recipients of list ORACLE-L
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]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Carle, William T (Bill), ALCAS
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Wong, Bing
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).
