RE: Optimizer and ANSI Joins

The squiggles tell the story in regards to expand sql text feature

From: Sayan Malakshinov <xt.and.r_at_gmail.com> Sent: Tuesday, January 29, 2019 12:50 PM To: Jared Still <jkstill_at_gmail.com>
Cc: ORACLE-L <oracle-l_at_freelists.org> Subject: Re: Optimizer and ANSI Joins

Hi Jared,

Of course, cbo understand ansi joins, because it transforms them into own syntax :) 

(with some nuances like full outer join(it has now native full outer join, but not in syntax yet) or outer join to more than one table before 12c)

You can check 10053 trace or dbms_sql2.expand_sql_text (DBMS_UTILITY.expand_sql_text since 12c)

вт, 29 янв. 2019 г., 20:10 Jared Still HYPERLINK "mailto:jkstill_at_gmail.com"jkstill_at_gmail.com:

Hello All:

Something I have heard a few times is that the Oracle Optimizer does not understand ANSI joins, and that ANSI joins are first converted to some legacy format before the optimizer processes them.

Does anyone here know if there is any truth in this?

Some things that come to mind:

• perhaps it was true at one time (9i)
• only partially true
• a blatant lie started by a SQL Server Fan.

OK, that last one was uncalled for, but a little humor is necessary sometimes :)

I have googled around for this a bit, but find nothing that really discusses the topic.

Thanks,

Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist

Principal Consultant at Pythian

Pythian Blog HYPERLINK "https://urldefense.proofpoint.com/v2/url?u=http-3A__www.pythian.com_blog_author_still_&d=DwMFaQ&c=RoP1YumCXCgaWHvlZYR8PZh8Bv7qIrMUB65eapI_JnE&r=N2hWu5HFsaIjmMkjQbnlokJ7uinNZMgPVk8rqPT9esM&m=BnHevQoyrA2OSMOnUqsjTrBNX217LfDWgLZOM3x2NrI&s=LTP_RfFZiTu1CsDu5v5ir3Yqf4D_u92ZhLGrInf0Oys&e="http://www.pythian.com/blog/author/still/

Github: HYPERLINK "https://urldefense.proofpoint.com/v2/url?u=https-3A__github.com_jkstill&d=DwMFaQ&c=RoP1YumCXCgaWHvlZYR8PZh8Bv7qIrMUB65eapI_JnE&r=N2hWu5HFsaIjmMkjQbnlokJ7uinNZMgPVk8rqPT9esM&m=BnHevQoyrA2OSMOnUqsjTrBNX217LfDWgLZOM3x2NrI&s=hLvVCnB9O2YJiMc8lCYXpKc-UURqh6p1lVI-zK1Edio&e="https://github.com/jkstill

--
http://www.freelists.org/webpage/oracle-l