Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: why would Oracle require hint to use bitmap index? does not pick up unless hinted
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>...Received on Fri Dec 08 2000 - 14:56:59 CST
>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'
>
>
>