AW: Outer join -> Union

From: Petr Novak <>
Date: Fri, 5 Nov 2010 11:07:56 +0000
Message-ID: <>

Hallo Gints,

good point, thanks. In the first tests yesterday I also tried indexes with constants - for example (1,id) and not null columns , but forgot to do it also on 11.2 and 9.2. I repeated the tests with not null columns and there was no improvement.  

Best Regards,

Von: Gints Plivna [] Gesendet: Freitag, 5. November 2010 10:52 Bis: Petr Novak
Cc: Dion Cho; Ric Van Dyke; Betreff: Re: Outer join -> Union

BTW I hope that in your real test case you have defined columns as NOT NULL. That can make difference sometimes in index (not) usage :)

I also tried your examples with columns as NOT NULLS, but the best I could achieve was INDEX FULL SCAN or FAST FULL INDEX SCAN, and these wasn't better (in terms of logical reads) than 2 full scans and hash join.

Gints Plivna

2010/11/5 Petr Novak <>:
> Hallo Dion,
> I have found two Notes in Metalink:
> Bug 7345484 - Left outer join with filters are slow [ID 7345484.8]
> CBO Chooses Poor Plan In When Outer Joining [ID 727604.1]

Received on Fri Nov 05 2010 - 06:07:56 CDT

Original text of this message