Re: Help with sql query speed. Explain plan included

On Mar 19, 5:49 pm, DA Morgan <damor..._at_psoug.org> wrote:
> Charles Hooper wrote:
> > I just completed some rather extensive testing of this problem on
> > Oracle 10.2.0.3 running on Windows Vista. The results were not quite
> > was I expected. 10.2.0.3, at least on this platform, seems to ignore
> > hints.
>
> Speaking of hints you just reminded me that in the current issue of
> Oracle Scene, the UKOUG's magazine, Issue 29, page 30, there is an
> article by Bob Mycroft on using Plan Stability to do what Advanced
> Rewrite doesn't ... place hints into code when you can not get to the
> source.
> --
> Daniel A. Morgan
> University of Washington
> damor..._at_x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org

I think that I need to correct my statement regarding Oracle 10.2.0.3, Windows Vista, and hints. With the three indexes in place (T1_INDEX1, T1_INDEX2, T1_INDEX3), I _can_ force different access plans with hints. Using the OP's SQL statement, adding the hint: "USE_HASH(TX T0) INDEX(TX) INDEX(T0)" forces an index full scan on the T1_INDEX3 and a hash join between the 75 rows from T1 and T1_INDEX3 (3+ minutes total). Adding the hint "NO_USE_NL(TX T1_INDEX3) INDEX(TX) INDEX(T0)" causes a sort-merge join between the 75 rows from T1 and an index full scan of T1_INDEX3 (3+ minutes). Adding the hint "NO_USE_NL(TX T1_INDEX3) INDEX(TX T1_INDEX1) causes a range scan of the T1_INDEX1 to retrieve the 75 rows and an index full scan of T1_INDEX1 - this query would have run for quite a while.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Tue Mar 20 2007 - 09:15:46 CDT