Re: SQL Tuning Question

please note that the technique i outlined in my previous post may not improve the overall performance of your query... but this technique has worked for me before for very large tables in a data warehouse type application on another database platform (teradata) where we needed to quickly retrieve an extremely small subset of rows (relative to all of the rows) based on a very specific set of criteria.

another idea i have might possibly work on 8i, but not on 8.0. I'm not familiar with the new Oracle 8i feature i've heard referred
to as a "function based index", but it would be way too cool if you could define an index on a function like this:

decode(tableA.NOTE,NULL,'1',NULL)

and then rewrite the query to take advantage of the index:

select tableA.*, tableA.rowid

   from tableA
   where decode(tableA.NOTE,NULL,'1',NULL) = '1'

"spencer" <spencerp_at_swbell.net> wrote in message news:9nMS4.169$za3.77401_at_nnrp3.sbc.net...
> 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