Re: METALINK and OWS

If you have time, a BIG if that is, set up different queries, passing hints to make sure differen execution plan will be picked up (and verified by EXPLAIN PLAN and/or trace/tkprof), and time the performance using DBMS_UTILITY.GET_TIME. What Rachel said, and in fact, recommended by Oracle is absolutely true. However, the number Rachel refered to, as low as 3-5% or as high as 20%, is for a 'typical' set up. It all boils down to a 'simple' choice for the optimizer to make : is it more efficient to do a full table scan or index (unique, range) scan to find and return the rows you ask for?

And that choice depends just 'a few' factors:

1. How many rows the table has?
2. What is the avg_row_len(gth)?
3. What is your block_size?
4. What is your db_file_multi_block_read_count setting?
5. Do you have updated and accurate statistics on your table?
6. Do you have updated and accurate statistics on your index?
7. How uniformly is your indexed column data distributed? If not, do you have histograms?
8. Where are you data and index tablespace datafiles physically located? Will it cause excessive I/O contention when both are accessed at the same time?

I am sure there are a few factors here that I missed. But the message is the same, do your own homework. There is just some piece of information nobody knows better than you do. What do you think, Rachel?

Michael

• Original Message ----- From: "Rachel Carmichael" <carmichr_at_hotmail.com> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Sunday, June 11, 2000 8:14 PM Subject: Re: METALINK and OWS

> WHOA! Slow down before you drop the indexes. Indexes are not totally
> useless! It very much depends on the queries you run against your
database.
> If you are doing a mixture of queries, where some return single rows or a
> very small subset of data, and some return a large number of rows, you
still
> should keep the indexes for the first type of query.
>
> Rachel
>
>
> >From: Nicholas Tufar <ntufar_at_chemist.gen.tr>
> >Reply-To: ORACLE-L_at_fatcity.com
> >To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> >Subject: Re: METALINK and OWS
> >Date: Sun, 11 Jun 2000 14:17:51 -0800
> >
> > >
> > > Nicholas,
> > >
> > > Sorry... I thought most people knew this. Oracle says that if a query
> >will
> > > return more than 20% of the rows of a table, it will use a full table
> >scan.
> > > So you DON'T tune it to use an index. In fact, as of 8i, I believe
> >Oracle
> > > now recommends that if a query returns as little as 3-5% of a table, a
> >full
> > > table scan is the way to go.
> >
> >A very valuable piece of information. How did I miss it!
> >Now I know what I am going to do on Monday. I had a feeling that
> >all those indeces are useless, now I know why they are.
> >Thank you very much.
> >
> >Nicholas
> >--
> >Author: Nicholas Tufar
> > INET: ntufar_at_chemist.gen.tr
> >
> >Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> >San Diego, California -- Public Internet access / Mailing Lists
> >--------------------------------------------------------------------
> >To REMOVE yourself from this mailing list, send an E-Mail message
> >to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> >the message BODY, include a line containing: UNSUB ORACLE-L
> >(or the name of mailing list you want to be removed from). You may
> >also send the HELP command for other information (like subscribing).
>
> ________________________________________________________________________
> Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com
>
> --
> Author: Rachel Carmichael
> INET: carmichr_at_hotmail.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
Received on Sun Jun 11 2000 - 22:10:08 CDT