Re: SQL Tuning Question

In article <MPG.1384d9ecc7be8a3b989680_at_news.flashcom.net>,   jason_at_server.com (Jason) wrote:
> Try to use hint:
>
> select /*+ INDEX(tableA) */
> tableA.*, tableA.rowid
> from tableA
> where note IS NULL;
>
> This is assumed that you have a proper index.
>
> In article <8feu19$iu0$1_at_nnrp1.deja.com>, rspeaker_at_my-deja.com says...
> > Hello all,
> >
> > Consider the following table description & SQL statement:
> >
> >
> > Name Null? Type
> > ------------------------------- -------- ----
> > INCIDENT_NUMBER VARCHAR2(8)
> > USERID VARCHAR2(8)
> > DATE_STAMP DATE
> > NOTE VARCHAR2(2000)
> >
> >
> > select tableA.*, tableA.rowid
> > from tableA
> > where note IS NULL;
> >
> >
> > First, please don't ask why the query is written this way or what
 it is
> > doing -- it was written by a developer who is no longer here and the
> > ones that are can't answer.
> >
> > Obviously, this query is generating a full table scan due to the IS
 NULL
> > condition. According to my monitoring tools, both Quest and Oracle,
> > this query has been executed about 250 times and generated 30,000
 disk
> > reads. The statement is showing a hit ratio of about 0.50%. The
 table
> > has over 300,000 records in it, of which only about 425 match this
> > criteria.
> >
> > My block size is 4k, so I cannot index the note field, and even if I
> > could the IS NULL would ignore it. I have made some recommendations
> > back to the developers on how to fix it (set the null values to some
> > dummy string and query using = 'dummy string'), but as yet they
 haven't
> > gotten around to it. Aside from that, is there anything else I can
 do
> > from the DBA standpoint to improve it's performance?
> >
> > I am running Oracle 8.0.5.1 on AIX 4.2.1. I have 4x166 CPU and 640
 MB
> > of memory. My DB_BLOCK_BUFFERS is currently set to 15,000 giving me
> > roughly 60 MB of DB_BUFFERS. My hit ratio is consistent at about
> > 45%-50% but I can't tell how much of that is being caused by this
 query.
> > Would moving this table into the BUFFER_POOL_KEEP help?
> >
> > Thanks,
> > Roy
> >

Jason, to use an index the indexed columns must be referenced in the where clause and on a single column index null values are not indexed, (i.e., stored in the index.)

rspeaker, you did not say how long the database was running. Two hundred firty executions and 30,000 I/O are nothing if the database has been running for 5 days but might be important if it was running for only a couple of hours.

More importantly how important is this query? How long does it take to run? If it is important and needs to complete more quickly here is an idea. Add a single byte indicator column to the table and place insert and update triggers on the table that test the note column for being null. When it is null the indicator column is set to a not null value then a single column index on this column will have only the approx. 450 entries in it and will provide very fast access.

--
Mark D. Powell  -- The only advice that counts is the advice that
 you follow so follow your own advice --


Sent via Deja.com http://www.deja.com/
Before you buy.