Discussions
Categories
- 17.9K All Categories
- 3.4K Industry Applications
- 3.4K Intelligent Advisor
- 75 Insurance
- 537.7K On-Premises Infrastructure
- 138.7K Analytics Software
- 38.6K Application Development Software
- 6.1K Cloud Platform
- 109.6K Database Software
- 17.6K Enterprise Manager
- 8.8K Hardware
- 71.3K Infrastructure Software
- 105.4K Integration
- 41.6K Security Software
join elimination on outer join with distinct subquery (instead of unique index)

Rainer Stenzel
Member Posts: 64 Bronze Badge
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 ?