Skip to Main Content

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

join elimination on outer join with distinct subquery (instead of unique index)

Rainer StenzelNov 15 2016

Encountering queries structered as outlined below I was wondering whether

join elimination could already (11.2) take place under specific preconditions

or should be proposed as implementation idea.

Shouldn't join eliminations be achievable on queries over a view as

create or replace view vusers as

with ot42 as (select distinct owner# from sys.obj$ where type#=42),

     ot57 as (select distinct owner# from sys.obj$ where type#=57)

select user#,ot42.owner# ot42user#,ot57.owner# ot57user#

from sys.user$

left outer join ot42 on ot42.owner#=user#

left outer join ot57 on ot57.owner#=user#

when the affected columns ot42user# and/or ot57user# are not used ?

Comments

Post Details

Added on Nov 15 2016
0 comments
380 views