Re: SQL Tuning Question

Why do you want to avoid a full table scan?

If performance is not the issue, what then?

Martin

rspeaker_at_my-deja.com wrote:
>
> 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 Sun May 14 2000 - 00:00:00 CDT