Re: ordered_hint

cybotto_at_yahoo.com 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"