Re: CBO picks wrong plan after analyze. FIRST_ROWS hint is workaround. ALL_ROWS causes wrong plan

oracle10_at_gmail.com schreef:
>
> Good Plan:
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=170900 Car
> d=42699 Bytes=1067475)

[snip]
> Bad Plan:
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=HINT: ALL_ROWS (Cost=19388 Card=4
> 2699 Bytes=1067475)

CBO does *not* agree: your "good" plan has a cost of 170900, whereas your "bad" plan has a cold of about 1/10th of it: 19388.

If I could have the same car for 11% of the price, I'd go for it!

What are the execution times? What values for the optimizer_cost* parameters? multiblock_read_count?

-- 
Regards,
Frank van Bortel

Top-posting is one way to shut me up...