Note in-line
--
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
The educated person is not the person
who can answer the questions, but the
person who can question the answers -- T. Schick Jr
One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html
____Finland__September 22nd - 24th
____Norway___September 25th - 26th
____UK_______December (UKOUG conference)
Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____USA__October
____UK___November
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
"Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message
news:1064537943.68764_at_yasure...
> Jim Reesman wrote:
>
> >In the following listing, the first query has 2 literal values in
the
> >where clause. It results in a star transformation being chosen and
the
> >plan and response time are good. The second query has simply had
the 2
> >literals replaced with bind variables. Despite including the
> >STAR_TRANSFORMATION hint, the CBO chooses the full scan and index
> >range scans (it does so seemingly without regard to _any_ hints -
eg.
> >FACT () ).
> >
> >How can I get the second query to use the first execution plan? I'm
> >using 9.2.
>
> I can't wait to see the answer because from my experience hints are
> often ignored.
> And yet others have repeated posted that they are not. This looks
like a
> good test case.
>
9.2 Data warehousing guide, p.17-12
Star transformation is not supported for ...
queries that contain bind variables.
Hints are only obeyed if they are legal, correct,
and used in the correct context.
The major pain in the backside with Oracle is
finding the bit in the documentation where you
get told what is legal - I checked the SQL Reference
and the Performance Guide (where it mentioned that
you can't use star_transformation with cursor_sharing)
before I got to the Datawarehouse Guide. It's always
a good source of clues for optimizer issues.
> --
> Daniel Morgan
> http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
> http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
> damorgan_at_x.washington.edu
> (replace 'x' with a 'u' to reply)
>
Received on Sun Sep 28 2003 - 06:40:14 CDT
