Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: why would Oracle require hint to use bitmap index? does not pick up unless hinted

Re: why would Oracle require hint to use bitmap index? does not pick up unless hinted

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 8 Dec 2000 20:56:59 -0000
Message-ID: <976308888.1074.0.nnrp-09.9e984b29@news.demon.co.uk>

You have an extreme data skew.
Look at 'analyze .... for indexed columns'; to generate a distribution histogram on the indexed column. Then (with literals in the 'where' clause) Oracle will use the index without hinting for the small data set and a tablescan for the large data set.

--
Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Practical Oracle 8i:  Building Efficient Databases

Publishers:  Addison-Wesley
Book bound date: 8th Dec 2000
See a first review at:
http://www.ixora.com.au/resources/index.htm#practical_8i
More reviews at: http://www.jlcomp.demon.co.uk/book_rev.html



Haild wrote in message <3a3141f9$0$99579$45beb828_at_newscene.com>...

>we have oracle 8i enterprise version, we created bitmap index on field.
>cardinality is 10 disitnct values over 90, 000,000 rows. we anlyzed both
>table and index. we run qurey that looks for value that exists in only
1000
>of the 90 million rows, the explain plan is full table scan (bad). we pick
>value that exists in 70 million of rows an dit also does full talbe
scan(ok).
>
>we throw index combine hint and it picks it up. both not without hint.
Isn't
>oracl esupposed to pick it up automaitcally based on optimizer rules?
>
>here is index def:
>
>CREATE BITMAP INDEX X_BT_SALES
> ON TF_SALES(SALES_DEPT_TARIFF)
>LOGGING TABLESPACE DATA01 PARALLEL 3
>
>
>status is valid
>
>
>select * from TF_SALES ss where ss.SALES_DEPT_TARIFF = 'MULTI TIME OF DAY'
>
>
>
Received on Fri Dec 08 2000 - 14:56:59 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US