Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: ordered_hint

Re: ordered_hint

From: Chuck <>
Date: Fri, 23 Sep 2005 08:48:38 -0400
Message-ID: <1127475504.bc7634ced8f6b3878040d1e186587302@bubbanews> wrote:
> You will need to spike the view with hints so it's not behaving
> unpretictable. tom will tell not to use hints and let Orcales CBO sort
> it out. You are looking for a hash join but then with ORDERED is
> exaclty what to do when not to use them.
> You can try to put the view as full sql statement as inline view and
> spike it with hints or not and see what is comming out.
> The query is begging for a full table scan on t1 and maybe on some
> others in the view as result. Once CBO ist starting with it will do all
> sorts of stuff and forget about it straight away.
> For what this excersise is about by the way?

It's for a custom HR benefits query on a Peoplesoft database.

What I really want is not table scan and hash join, but index scan and nested loops. CBO is picking a Cartesian join even though there's no missing joins. I tried FIRST_ROWS first but still got the Cartesian. When I tried ORDERED I got a very good execution plan for this query. It went from 6 seconds to 50ms, and from 350k consistent gets to just over 100.

Before I go asking the developers to change the query, or create a stored outline I want to try increasing optimizer_max_permutations. It's at the default. I've already tweaked all the other optimizer_* parameters to obtain good performance overall. This one query is really throwing the CBO for a loop though. And it's really not that complex. It's one table and one simple view that joins 4 other tables. Why the CBO is choosing a Cartesian join is unexplainable.

To reply by email remove "_nospam"
Received on Fri Sep 23 2005 - 07:48:38 CDT

Original text of this message