Re: Using an index.

will you investigate the relationship with - optimizer_index_caching and
- optimizer_index_cost_adj

it seems to me that in OLTP, switching from RBO to CBO will make 80% of my queries go faster but... 20% will go slower Adjusting the parameters above mostly does the trick for me so I don't have to add
hints all over the place

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:b8bpc7$b4q$1$830fa17d_at_news.demon.co.uk...
>
> I came across that old fairy-tale about
> Oracle and indexes a few days ago -
> the one that goes:
> Oracle will use an index if the query
> is going to access less than X%
> of the data.
>
> Where X is usually given a value somewhere
> between 2 and 10.
>
> Well, just as an idle way to pass the time,
> I decided to construct a data set and 'tune'
> an instance to see how low I could make X
> and still get a tablescan instead of fetching
> a single row through a primary key index.
>
> 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.
>
> Otherwise, I'll write it up some time in the
> next few weeks.
>
>
> --
> Regards
>
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
>
> The educated person is not the person
> who can answer the questions, but the
> person who can question the answers -- T. Schick Jr
>
>
> One-day tutorials:
> http://www.jlcomp.demon.co.uk/tutorial.html
>
> ____UK_______April 22nd
> ____USA_(FL)_May 2nd
> ____Denmark__May 21-23rd
> ____Sweden___June
> ____Finland__September
> ____Norway___September
>
> Three-day seminar:
> see http://www.jlcomp.demon.co.uk/seminar.html
> ____UK_(Manchester)_May x 2
> ____Estonia___June (provisional)
> ____Australia_June (provisional)
> ____USA_(CA, TX)_August
>
> The Co-operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
>
>
>
Received on Fri Apr 25 2003 - 13:46:15 CDT