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