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;
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;
Check the huge performance gain: 03:24.42 vs. 00:00:00.40
** Reference:
Thank you.
Regards
Stefan