Re: separate data/inidex

Daniel Morgan wrote:
>
> I agree. I thought he was saying that most of the time we do full table scans,
> etc. And given that many of my application tables have been in the
> multi-gigabyte range that would be a complete non-starter.
>
> When one is looking for a single part of a Boeing 747 ... one does not want to
> scan every part for every plane ever made.
>
> Daniel Morgan
>
> Jonathan Lewis wrote:
>
> > Comments in-line
> >
> > --
> > Jonathan Lewis
> > http://www.jlcomp.demon.co.uk
> >
> > Author of:
> > Practical Oracle 8i: Building Efficient Databases
> >
> > Next Seminar - Australia - July/August
> > http://www.jlcomp.demon.co.uk/seminar.html
> >
> > Host to The Co-Operative Oracle Users' FAQ
> > http://www.jlcomp.demon.co.uk/faq/ind_faq.html
> >
> > Daniel A. Morgan wrote in message <3CC2CB08.64C747AF_at_exesolutions.com>...
> > >Please help me here with two things.
> > >
> > >1. "Nowadays to satisfy queries, you're full scanning tables, full scanning
> > indexes,
> > >not using indexes at all,...."
> > >
> >
> > It is very easy to misunderstand the intent of a comment
> > on the newsgroup, but I think if you re-read Connor's post
> > carefully you will appreciate that he is not saying "you must
> > full scan tables"; he is simply listing a number of the mechanism
> > that are commonly (and necessarily) activated in a modern system.
> >
> > >Since when? I work very hard to not do these things.
> > >
> >
> > Don't work too hard at it. Sometimes the biggest
> > performance problems are introduced by trying
> > to push Oracle too hard into paths which use
> > index range scans and index unique scans to
> > access tables by rowid; resulting in excessive
> > CPU usage and CBC latch contention.

I'll give an example of what I was trying to convey in terms of "full scans good sometimes, full scans bad sometimes" using an example somewhat poached from JL's excellent seminar

create table BOEING1 ( x number, y char(100)); create table BOEING2 ( x number, y char(100)); create index BOEING1 on BOEING1 ( x);
create index BOEING2IX on BOEING2IX ( x);

10000 rows inserted into each table, 200 rows for each value of x from 0 to 49. So after running an analyze...

analyze table BOEING1 compute statistics; analyze table BOEING2 compute statistics;

we can see that they are virtually identical..

select table_name, num_rows, blocks, empty_blocks from user_tables;

TABLE_NAME                       NUM_ROWS     BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ---------- ------------
BOEING1                              9999        153            6
BOEING2                              9999        153            6

select table_name, column_name, NUM_DISTINCT,DENSITY from user_tab_columns
where TABLE_NAME like 'TE%';

TABLE_NAME                     COLUMN_NAME                   

------------------------------ ------------------------------
------------ ----------
BOEING1                        X                                       
50        .02
BOEING2                        X                                       
50        .02

select max(x), min(x), avg(x) from BOEING1;

    MAX(X) MIN(X) AVG(X)
---------- ---------- ----------

        49 0 24.5024502

select max(x), min(x), avg(x) from BOEING2;

    MAX(X) MIN(X) AVG(X)
---------- ---------- ----------

        49 0 24.5024502

But they exhibit spectacularly different performance characteristics...

select /*+ FULL(t) */ count(y) from BOEING2 t where x < 20;

took 156 consistent gets

select /*+ INDEX(t) */ count(y) from BOEING2 t where x < 20;

took 70 consistent gets,

thus you get the "conventional" result of "we should be using an index" when examing this particular BOEING. But the same queries against BOEING1 give

select /*+ FULL(t) */ count(y) from BOEING1 t where x < 20;

took 156 consistent gets

select /*+ INDEX(t) */ count(y) from BOEING1 t where x < 20;

took 3120 consistent gets (20 times worse!)

So - sometimes checking every Boeing ever made is the best option :-)

Cheers
Connor

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."