This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 31st, when you will be able to use this site as normal.

    Forum Stats

  • 3,890,899 Users
  • 2,269,649 Discussions


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

Rainer Stenzel
Rainer Stenzel Member Posts: 64 Bronze Badge
edited Nov 15, 2016 5:07AM in Database Ideas - Ideas

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 ?

2 votes

Active · Last Updated