Oracle-L: RE: Can I index this query?

From: Wong, Bing <bing.wong_at_IngramMicro.com>
Date: Tue, 15 Oct 2002 15:13:42 -0800
Message-ID: <F001.004E9E4F.20021015151342@fatcity.com>

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
wcarle_at_att.com

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Carle, William T (Bill), ALCAS
INET: wcarle_at_att.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: Wong, Bing
INET: bing.wong_at_IngramMicro.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------