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: optimizer won't pick bitmap index

Re: optimizer won't pick bitmap index

From: Vladimir M. Zakharychev <bob_at_dpsp-yes.com>
Date: Tue, 30 Jul 2002 09:21:45 +0400
Message-ID: <ai57pa$n9t$1@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@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:21:45 CDT

Original text of this message

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