Re: Outer Join reads all rows from underlying tables

Matt wrote:

> No, I am the only person with any Oracle knowledge on site.
>
> I gather stats on the tables nightly so the optimizer should have
> up-to-date info to use as part of the parse phase.
>
> Which init.ora parameters would cause this behavior..? The plan
> itself is a sensible approach (considering the expected row counts)
> but it seems that the WHERE clause is not being applied to the view
> definition.
>
> Matt

What version?
8i means a lot of things ... have you patched to 8.1.7.4? What is the init setting for optimizer_index_cost_adj? What is the init setting for optimizer_index_caching? What happens when the same statement is run a second and third time? How are you gathering the statistics? Does this include index stats too? How are you determining the statistics are there? What is the optimizer mode?
Show us the SQL statement, the relevant indexes, and the explain plan Have you run a stats pack?
Do you have an RDA?

-- 
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)