Re: SQL Tuning Question

spencer, thanks for the suggestions. Rewriting the code is most definitely an option, but one I was trying to avoid, at least for now. The developer are hard at work on other things and can't stop to rewrite one piece of code, put it thru testing and CM, etc at the present time. I was hoping to find something else I could do from the DBA standpoint until they can rework the SQL.

Thanks,
Roy

In article <lMMS4.546$uz3.4861_at_news.swbell.net>,   "spencer" <spencerp_at_swbell.net> wrote:
> 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.
> > >
> >
> >
>
>

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