SQL Tuning Question

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

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu May 11 2000 - 00:00:00 CDT