Re: SQL Tuning Question

I can do that, but the query itself takes less than 10 seconds to run. Updating the table with a parallel degree of 4 reduced it to about 3 seconds, but speed of query was never my concern. I'm trying to find a way, without rewriting the code, to avoid the full table scan.

Thanks,
Roy

In article <8ffbo6$2s7$1_at_nnrp1.deja.com>,   kal121_at_my-deja.com wrote:
> Since you're doing a full table scan and have multiple processors, why
> not run the query in parallel?
>
> In article <8feu19$iu0$1_at_nnrp1.deja.com>,
> 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 via Deja.com http://www.deja.com/
> Before you buy.
>

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