Re: SQL Tuning Question

if changing the SQL is an option, and it is important to support NULLs in the NOTE
column, and adding an indicator column is not workable... then...

maybe you could consider building your own "pseudo index" table, (let's call it tableAx) that contains only the primary key column(s) of tableA, but only contains a row for those rows in tableA that meet the limiting criteria (in your case, rows from tableA that have a NULL NOTE column).

for an initial load of tableAx, you can't avoid a full tablescan:

insert into tableAx ( <pk> )
select <pk> from tableA
where tableA.NOTE IS NULL;

then you can join the two tables in the query, like this:

select tableA.*, tableA.rowid
  from tableA, tableAx
 where tableA.<pk> = tableAx.<pk>

if you're using the cost based optimizer, be sure to analyze the tables and compute statistics, and then test the query to see if it performs any better. you may need to add a hint, i'm thinking that you'd be looking for a plan that does a full table scan on tableAx, and then an index lookup to tableA using the primary key index.

To keep tableAx "in sync" with tableA, you could define trigger(s) on tableA for insert, update, and delete. you could also try defining tableAx as an index-organized table (IOT) in oracle 8 or 8i.

HTH <rspeaker_at_my-deja.com> wrote in message
news:8feu19$iu0$1_at_nnrp1.deja.com...
> 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 Fri May 12 2000 - 00:00:00 CDT