Forum Stats

  • 3,874,324 Users
  • 2,266,718 Discussions
  • 7,911,812 Comments

Discussions

Implement "index backbone join" transformation/rewrite in CBO code

Stefan Koehler
Stefan Koehler Member Posts: 283 Silver Badge
edited Jan 11, 2016 6:17PM in Database Ideas - Ideas

Hi guys,

i think it would be worthwhile (especially from a performance perspective) to make the CBO "index backbone join" aware. This kind of transformation can be a huge performance gain and does not necessarily need a manual re-write of the query in my opinion. As far as i understood the corresponding patent (http://www.google.com/patents/US20110119249) it could be costed and Oracle already got the patent as well.

One of the inventors recently published an example in a German IT magazine. I just quote the corresponding query (re-write) and the DBMS_XPLAN output from it here. For the full artice please check the reference.

Original query

SQL> select * from (

select a.city_name, p.last_name, substr(p.data,1,1) p_data, substr(a.data,1,1) a_data

  from ibj.address a,

       ibj.person p

where p.person_id = a.person_id

  and a.city_name = 'Bern'

  and p.last_name = 'Müller')

where rownum < 11;

csm_tuning-abb7-flatz_98361555d6.png

Manual rewrite (currently necessary because of lack of CBO implementation)

SQL> select * from (

select a.city_name, p.last_name, substr(p.data,1,1) p_data,

       substr(a.data,1,1) a_data

  from ibj.person p,

       ibj.address a,

       (select /*+ NO_MERGE  */ p.rowid p_rowid, – phase 1

               a.rowid a_rowid

           from ibj.address a,

                ibj.person p

           where p.person_id = a.person_id

            and a.city_name = 'Bern'

            and p.last_name = 'Müller'

         ) i

where p_rowid = p.rowid

  and a_rowid = a.rowid)

where rownum < 11;

csm_tuning-abb8-flatz_596a66363d.png

Check the huge performance gain: 03:24.42 vs. 00:00:00.40

** Reference:

Thank you.

Regards

Stefan

Stefan KoehlerMartin PreissberxMahmoudHaSayan MalakshinovLothar FlatzFranck PachotRandolf GeistPravin TakpirectriebJagadekaraabhinivesh.jaintop.gunGeert GruwezBrian BakulaKayKvinaykumar2Jagjeet SinghManish ChaturvediJonathan LewisApexBineulohmann5c6e4cec-787c-4a56-a5ea-4a1afce715d1sensoftSandeep Kumar skmdaskalovRobert Marz3397048Timur Akhmadeev
30 votes

Active · Last Updated

Comments