Re: Using an index.

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:b8bpc7$b4q$1$830fa17d_at_news.demon.co.uk...
> Statistics generated by
> analyze table t1 compute statistics
>
> The query:
> select v1 from t1 where n1 = 999;
>
> The index generated by
> alter table t1 add constraint t1_pk
> primary key (n1);
>
> The result:
>
> I configured the system so that Oracle
> CHOSE (i.e. without hints) to do a tablescan
> of 1,500,000 rows instead of using the
> primary key index.
>
> Given enough space, I KNOW that I could
> push with up to tablescan for 1 row in 300M,
> and I think I could manage 1 row in 740M.
>
>
> If you're going to IOUG-A I'll try to find time
> to explain what I did in my presentation on
> CBO on Monday afternoon.

That's entirely depends what you meant by "configuring the system". I assume it's not something like creating stored outline, or having stale or manually "adjusted" statistics telling that there are 0 rows in the table rendering the matter to trivial.

For unique key (START-KEY=STOP-KEY)
cost = BLEVEL+1
For table FULL-SCAN:
cost ~ BLOCKS/DB_FILE_MULTIBLOCK_READ_COUNT

It's interesting to see how could you achieve your goal other than setting ridiculous values for the parameters involved. Received on Fri Apr 25 2003 - 12:18:07 CDT