SELECT on database VIEW wider than needed vs JOIN

[adam_krawczyk1]

Hello experts,

Could you please help me to clarify one issue - how database optimizer process SELECT statement on VIEW when only some and not all tables are used.

Example:

VIAUFK_AFVC - this view contains 7 related tables with JOIN conditions:

But there is a query that uses only fields from one table AFVV~ARBEI and AFVV~AUFPL:

SELECT ARBEI FROM VIAUFK_AFVC WHERE AUFPL = '1'

1. First question is - will there be full JOIN performed on all 7 tables in database or only selection from AFVV will be actually executed?

I would say that optimizer should be smart enough to discover that only one table is needed. However execution plan in ST05 with Oracle database shows that all tables are joined for result in execution plan:

2. Does it finally mean that direct select on AFVV will be executed much faster (if it was repeated thousands times)?

I am in doubt if I can say that we should always use JOIN on tables and not the view if the view contains more tables that we actually need for query (for simplification let's ignore buffering).

SELECT ARBEI FROM AFVV WHERE AUFPL = '1'

Estimated costs is now 2 so it looks better than previously 3.

3. In addition estimated rows count is higher now, (3 not 1) does it mean that more rows can be returned from direct SELECT than from view?

I could understand if different tables were involved in select statement, but if it is only one table AFVV then result must be the same and it is just optimizer wrong prediction. I am right?

Kind regards

Adam Krawczyk

[Former Member · Accepted solution]

Answers on your 1 and 3 questions.

The select results from table and view are not necessarily the same. Simple explanation. If one table contains only 1 row and second contains 7 rows, the select from first table returns 1 row, but select from join returns:

1) 7 rows if values from join columns exists in both tables

2) 0 rows if values from join columns in first table doesn't exists in second table.

[adam_krawczyk1 · Accepted solution]

Hi Roman,

Thank you for your answer. Actually this is natural, your hint helped me to realize the solution. This is how I understand it now:

1. Database must perform JOIN even if SELECT on view uses columns from single table AFVV.

Example:

Two tables: PERSON, CAR and view PERSON_CAR_VIEW joined on PERSON.CAR_ID and CAR.ID

Table PERSON contains only rows with null value for CAR_ID (no person with car).

Then:

SELECT NAME FROM PERSON WHERE AGE > 18

may find some rows from PERSON table but:

SELECT NAME FROM PERSON_CAR_VIEW WHERE AGE > 18

will find nothing due to no relation and empty CAR_ID in PERSON table.

2. Due to point 1 it is obvious that such SELECT on view will take more time than SELECT on single table (JOIN cannot be ignored).

If we are sure that we can ignore relations between tables, it is better to select directly from table and not redundant view.

Coming back to example from point 1 - the question is - should we search for adult persons at all, or only adult person who has a car (has filled CAR_ID relation). There is functional difference.

3. You hit the point with answer - it is possible that SELECT on single table may return more rows than SELECT on VIEW - as shown in example with PERSON and CAR.

Regards,

Adam