AW: Outer join -> Union
Date: Fri, 5 Nov 2010 11:07:56 +0000
Message-ID: <09011014EB621E4CBC2536B62A1B64079B52_at_SMXC001.trivadis.com>
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,
Petr
Von: Gints Plivna [gints.plivna_at_gmail.com] Gesendet: Freitag, 5. November 2010 10:52 Bis: Petr Novak
Cc: Dion Cho; Ric Van Dyke; oracle-l_at_freelists.org 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
http://www.gplivna.eu
2010/11/5 Petr Novak <Petr.Novak_at_trivadis.com>:
> 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 10.2.0.4/11.1.0.6 When Outer Joining [ID 727604.1]
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Nov 05 2010 - 06:07:56 CDT