Re: Can I index this query?

Hi,
Atleast you can do one thing if you are having Oracle Pratitioning then just partition it on the QueueType field provided you are having almost same number of 3 different values. Place all partition on differnet harddisks and if possible put them on different controllers. In that case your query response time will reduce to 1/3.

Or If you are having Bitmap index option and not much insertion or deletion going on in this table then u can create a bitmap index on QueueType Field. Bitmap index are used for low cardinality columns only.

I hope this may help you.

Thanks & Regards
Darshan Singh

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Wednesday, October 16, 2002 11:23 AM

> You could always consider something like OracleText to
> assist with these kind of searches, but thats a big
> step..
>
> hth
> connor
>
> --- "Carle, William T (Bill), ALCAS" <wcarle_at_att.com>
> 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
> >
> > --
> > 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).
>
> =====
> Connor McDonald
> http://www.oracledba.co.uk
> http://www.oaktable.net
>
> "Remember amateurs built the ark - Professionals built the Titanic"
>
> __________________________________________________
> Do You Yahoo!?
> Everything you'll ever need on one web page
> from News and Sport to Email and Music Charts
> http://uk.my.yahoo.com
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: =?iso-8859-1?q?Connor=20McDonald?=
> INET: hamcdc_at_yahoo.co.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).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Darshan Singh
  INET: darshan.singh_at_skandiabank.ch

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