Re: optimizer won't pick bitmap index

Hi All,

Indeed the hint is invalid (must use table alias if specified).

Note once fixed and the hint is used, the CBO will kick in automatically. Hints automatically use the CBO (with the exception of the RULE hint).

Cheers

Richard
"Vladimir M. Zakharychev" <bob_at_dpsp-yes.com> wrote in message news:ai57pa$n9t$1_at_babylon.agtel.net...
> Your hint is invalid. It should read
> /*+ INDEX(a adebsu_dup_x3) */
> but still it will only work if CBO is in use. Plans explicitly show that
> RBO is used in both cases. Try
> alter session set optimizer_mode=CHOOSE
> or just use /*+ FIRST_ROWS */ and see if it improves the situation.
> And as a matter of fact, HOW did you analyze data structures (which
> command you used)?
>
> --
> Vladimir Zakharychev (bob_at_dpsp-yes.com)
http://www.dpsp-yes.com
> Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet
applications.
> All opinions are mine and do not necessarily go in line with those of my
employer.
>
>
> "Mikael Kruse" <mikael.kruse_at_ehs.ericsson.se> wrote in message
> news:ai3pll$qv6$1_at_newstoo.ericsson.se...
> > 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 Tue Jul 30 2002 - 00:34:11 CDT