Skip to Main Content
  • Questions

  • RBO-Outer joined table as Driving table.

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Rahul.

Asked: July 02, 2001 - 10:08 pm UTC

Last updated: July 28, 2004 - 1:17 pm UTC

Version: 8.1.7

Viewed 1000+ times

You Asked

Hello Tom,

1)I am under the impression that for Cost based optimizer, a table participating in an Outer join(+) will not be used as the driving table. Is the same applicable for RBO. If yes, then what if i accidentally have a table used in outer join as the last table in the from clause(dr.table for RBO). Is the next from right, used as the driving table.

2)What will happen if i delete the statistics for entire schema, but still use hints for some queries(ORDERED).Will there be any difference in the performance while the statistics are deleted but hints are still used.Will hints enable Oracle use the CBO while Optimizer mode is set to CHOOSE.

3)Could i strictly ensure that Oracle uses CBO for some queries and RBO for some??.

Thanks,
Rahul.




and Tom said...

1) a table that is outer joined to, eg:

select *
from dept, emp
where emp.deptno (+) = dept.deptno;

(here emp is being outer joined to) Cannot be used as a driving table under either optimizer. The RBO uses the table order only when it can, else it uses rules to reorder the tables in the from clause. Here, DEPT will be the driving table regardless of optimizer goal.


2) You will be using the CBO for all queries with hints (unless the hint is RULE of course). The optimizer will make its decisions based on little to no data. How it will work depends on your data and your queries. No statements of fact can be made without knowing that information.

3) Set the optimizer goal to RULE in the init.ora. Then add an ALL_ROWS or FIRST_ROWS hint to queries you want to use the CBO with.

Or, analyze your tables and use the RULE hint when you wanted to use the RBO.

However, I would suggest you twiddle with the optimizer_index_cost_adj init.ora parameter and use the CBO exclusively.

You might want to pick up Jonathan Lewis's book "Practical Oracle8i" -- he has a good discussion on this.

Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

RBO and driving table

Rahul Pathri, July 10, 2001 - 10:11 pm UTC

The information provided clarified a lot and paved way for new experiences.

If structured properly is RBO still a good optimizer to choose

Lakshmi Narasimhan R, December 29, 2001 - 8:02 am UTC

Hi Tom

in my firm, they have developed their big product based on RBO fully. Since already been couple of versions running on diff clients they are not willing to change this and also its working without much problem.

Is there any problem now changing it into CBO and analysing all the tables. Since all select has been made for RBO will it create problem ? or Oracle engine is capable of handling automatically.

I am arguing to change to CBO since couldnt use most of the new features like partition, function based index etc.

You have any suggestion.

thanks in advance

Tom Kyte
December 29, 2001 - 11:42 am UTC

You will have to test -- it is something I would consider a major change. Some queries will run better, some will run unchanged -- undoubtably there will be some that do not perform as well and may need some help via init.ora parameters or reworking.

Kulguru, December 29, 2001 - 1:45 pm UTC

"The RBO uses the table order only when it can, else it uses rules to reorder the tables in the from clause"

Tom, this is for the first time that you are stating this fact on your web site.

From your earlier discussions, I was under the impression that when we use RBO, the order of the table is used for sure, but when we use CBO, then the order of tables doesnt matter unless and untill we use the /* + ordered */ hint.

It is coming really as a surprise that for example

when we use a

query like

select * from emp, dept
where emp.deptno=dept.deptno



you are saying that using RBO , there is a possibility that emp might be used as a driving table and not dept.

Can you substantiate this claim.

Thank you

Tom Kyte
December 29, 2001 - 2:36 pm UTC 

Well, lets be clear here.  Its always ONLY been true that the order of the tables in the from clause MAY affect the query plan in the RBO.  Stress the keywords ONLY and MAY.  It is true that in RBO, the order of tables in the from clause can be important.  It is true that in CBO, it is not so.  It is true that in RBO, it will use RULES to process the query and these rules govern how the tables will be accessed. 

Here is the simplest example that shows the same query using different driving tables under RBO.  I just play games with the indexes -- the RBO uses its rules to find the driving table using the indexes, not the order of the tables in the from clause. 

ops$tkyte@ORA817DEV.US.ORACLE.COM> create table emp as select * from scott.emp;
Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> create table dept as select * from scott.dept;
Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> set autotrace traceonly explain
ops$tkyte@ORA817DEV.US.ORACLE.COM> create index dept_idx on dept(deptno);
Index created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from emp, dept where emp.deptno = dept.deptno;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   NESTED LOOPS
   2    1     TABLE ACCESS (FULL) OF 'EMP'
   3    1     TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'
   4    3       INDEX (RANGE SCAN) OF 'DEPT_IDX' (NON-UNIQUE)


<b>so here, EMP is the "driving" table, we full scan it and do an index access into DEPT</b>

ops$tkyte@ORA817DEV.US.ORACLE.COM> drop index dept_idx;
Index dropped.

ops$tkyte@ORA817DEV.US.ORACLE.COM> create index emp_idx on emp(deptno);
Index created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from emp, dept where emp.deptno = dept.deptno;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   NESTED LOOPS
   2    1     TABLE ACCESS (FULL) OF 'DEPT'
   3    1     TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
   4    3       INDEX (RANGE SCAN) OF 'EMP_IDX' (NON-UNIQUE)

<b>same query, different indexes -- the roles are reversed.  DEPT drives, EMP is driven into</b>

ops$tkyte@ORA817DEV.US.ORACLE.COM> create index dept_idx on dept(deptno);
Index created.


<b>Now they both have equally "appealing" indexes -- the order of the tables in the from clause is used to break the tie</b>


ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from emp, dept where emp.deptno = dept.deptno;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   NESTED LOOPS
   2    1     TABLE ACCESS (FULL) OF 'DEPT'
   3    1     TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
   4    3       INDEX (RANGE SCAN) OF 'EMP_IDX' (NON-UNIQUE)



ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from dept, emp where emp.deptno = dept.deptno;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   NESTED LOOPS
   2    1     TABLE ACCESS (FULL) OF 'EMP'
   3    1     TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'
   4    3       INDEX (RANGE SCAN) OF 'DEPT_IDX' (NON-UNIQUE)



ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> set autotrace off

A reader, July 28, 2004 - 1:17 pm UTC


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.