Re: optimizer won't pick bitmap index

Hi,
I don't think I'm using any aliases and I analyzed the concerned tables to get the statistics complete, so here it is:

SQL after:
SELECT /*+ INDEX(q_biauto_deb_susp_dup adebsu_dup_x3) */

distinct a.bi_file_id FROM Q_BIAUTO_DEB_SUSP_DUP a

WHERE a.bi_errcode = 2

and exists

(SELECT 1 FROM Q_BIAUTO_DEB_HIST_HEAD b

WHERE a.BI_FILE_ID = b.bi_file_id

and b.bi_auto_debcode = 'NROAM'

AND b.file_layout_type = 'E'

AND b.bi_auto_status = 70)

ORDER BY a.bi_file_id;

The SQL before was exactly the same apart from that the hint comment was missing.

Explain plan before:

SELECT STATEMENT Hint=RULE

SORT UNIQUE NESTED LOOPS TABLE ACCESS FULL Q_BIAUTO_DEB_SUSP_DUP TABLE ACCESS BY INDEX ROWID Q_BIAUTO_DEB_HIST_HEAD INDEX UNIQUE SCAN ADEBHH_PK Explain plan after:

SELECT STATEMENT Hint=RULE

SORT UNIQUE HASH JOIN TABLE ACCESS FULL Q_BIAUTO_DEB_HIST_HEAD TABLE ACCESS FULL Q_BIAUTO_DEB_SUSP_DUP /Mikael

"Andy" <andy.spaven_at_eps-hq.co.uk> wrote in message news:M7d19.203$9R.1006472_at_newsr2.u-net.net...
> Mikael
>
> Can you provide more information such as the exact SQL and the explain
plans
> before and after creating index and adding hint ?
>
> To provide some pointers to be going on with: -
>
> 1) Be careful to use the alias as the table_name in the hint if you have
> aliased a table
> 2) Make sure the names are correct as Oracle will ignore invalid hints
> without raising an error
> 3) Make sure you don't have incomplete statistics on the table - either
have
> none and fully hint the SQL or have complete stats as this often
complicates
> matters.
>
> Andy
>
> "Mikael Kruse" <mikael.kruse_at_ehs.ericsson.se> wrote in message
> news:ai3l3k$gva$1_at_newstoo.ericsson.se...
> > Hi,
> > I have a query taking some time and thought I'd test if it goes quicker
> > using a bitmap index. So I created the bitmap index and now I'm trying
to
> > give hints to the optimizer to make it use the index, but I do not
> > succeed...
> > Running Oracle 8.0.6 on HP-UX, optimizer mode=RULE and changing to
> > cost-based on system or session level is not an option. My method has
been
> > to simply give a hint:
> > select /*+ index(table_name index_name) */ column1, column2....
> > but as mentioned it doesn't work. I've also tried giving two hints:
> > select /*+ all_rows index(table_name index_name) */ column1, column2....
> > to make the optimizer switch to cost-based for this query and then use
> > bitmap index but it was no good.
> > If anyone can help me I would be really grateful...
> >
> > /Mikael
> >
> >
>
>
Received on Mon Jul 29 2002 - 11:19:37 CDT