Re: SQL Tuning Question

Hi,
I am not sure, if this helps:

Try using the 'Exists' clause in the where clause instead of using the '=' .

Venkat Atluri.
In article <391EF2AA.E5300E7B_at_0800-einwahl.de>, Martin Haltmayer <Martin.Haltmayer_at_0800-einwahl.de> wrote:
>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.
>
>
>
>

• Sent from RemarQ http://www.remarq.com The Internet's Discussion Network * The fastest and easiest way to search and participate in Usenet - Free!