Re: Outer Join reads all rows from underlying tables

Notes in-line:

Daniel Morgan <damorgan_at_x.washington.edu> wrote in message news:<1084174025.929140_at_yasure>...

> What version?

8.1.7.0.0

> 8i means a lot of things ... have you patched to 8.1.7.4?

No patches (see above)

> What is the init setting for optimizer_index_cost_adj?

100 (default)

> What is the init setting for optimizer_index_caching?

0 (default)

My understanings is that these index related init.ora parameters will affect Oracle's decision whether to use and index over a FTS which is not my problem... The plan itself does not concern me too much. I am concerned that the WHERE clause is not being passed through to the view in the SELECT statement. I don't see how upping the weight Oracle places on index usage will affect this (since it is already using the most suitable index).

> What happens when the same statement is run a second and third time?

No difference. The parse time is not the overhead (I can see this from the tkprof output).

> How are you gathering the statistics? Does this include index stats too?

dbms_utilty with compute...

> How are you determining the statistics are there?

Checking last_analyzed from user_tables (and the fact that the CBO is being used since HASH join is used throughout the plan).

> What is the optimizer mode?

CHOOSE. I have tried FIRST_ROWS, and RULE and also tried every different join order combination... With an /*+ ORDERED */ hint.

> Show us the SQL statement, the relevant indexes, and the explain plan

I have given you the SQL statement and plan with the row counts which show that the optimizer is not restricting the rows returned from the view "PS_TM_V_CCEH".

> Have you run a stats pack?

Statspack is not relevant in this case... I am not concerned with the overall system picture. This plan (and row counts) is repeatable and consistent whenever I run this statement.

> Do you have an RDA?

What OS specific stuff will affect the optimizers decisions..? I dont think RDA is relevant.

Incidentally the equivalent inner join restricts the rows returned from the VIEW... I read on Metalink a quote from Jonathan Lewis that outer-joins can have problems when used in VIEWs but I haven't been able to back this up with any evidence/bug reports.

Matt Received on Mon May 10 2004 - 08:47:13 CDT