Home » RDBMS Server » Performance Tuning » Optimizer
Optimizer [message #65030] Thu, 01 April 2004 02:26 Go to next message
Ajendra
Messages: 165
Registered: February 2004
Senior Member
Hai

Whn I was going through tuning concepts. i got the followoing info

3.1 Rule Based Optimizer (RBO)
RBO follows simple ranking methodology. 15 ranking points are designed in this optimizer. When a query is received, the optimizer evaluates the number of points that are satisfied. The execution path with the best rank (lowest number) is then chosen for executing the query. The 15 point ranking is mentioned below.

1. Single row by ROWID
2. Single row by cluster join
3. Single row by hash cluster with unique or primary key
4. Single row by unique or primary key
5. Cluster join
6. Hash cluster key
7. Indexed cluster key
8. Composite key
9. Single column indexes
10. Bounded range on index columns
11. Unbounded range on indexed columns
12. Sort merge join
13. MAX or MIN on indexed column
14. ORDER BY on indexed columns
15. Full table scan.

Could anyone please explain me what is cluster join, Hash cluster, sort merge join.

Does we have any control over it. How do we write a qry so that it will use
the spcific type of join.

Can we give hint to use a particular join. Then we shd always use cluster join as it does have better performance and then on which circumstance shd we use Sort Merge join.

Thanks
Ajendra
Re: Optimizer [message #65041 is a reply to message #65030] Mon, 05 April 2004 11:15 Go to previous message
Thiru
Messages: 1089
Registered: May 2002
Senior Member
Cluster join:
-------------
"A cluster join is nothing more than a nested loops join involving two tables that are stored together in a cluster."


SQL> create cluster dept_emp(deptno number);

Cluster created.

SQL> drop table emp;

Table dropped.

SQL> drop table dept;

Table dropped.

SQL> create table emp(empno number,deptno number) cluster dept_emp(deptno);

Table created.

SQL> create table dept(deptno number) cluster dept_emp(deptno);

Table created.

SQL> create index dept_emp_idx on cluster dept_emp;

Index created.

SQL>  select * from emp,dept where emp.deptno=dept.deptno;

no rows selected

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   NESTED LOOPS
   2    1     TABLE ACCESS (FULL) OF 'DEPT'
   3    1     TABLE ACCESS (CLUSTER) OF 'EMP'

-- thats a cluster join

Never used hash clusters.

SQL> create table t1(x int);

Table created.

SQL> create table t2(x int);

Table created.

SQL> select * from t1,t2 where t1.x=t2.x;

no rows selected

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   MERGE JOIN
   2    1     SORT (JOIN)
   3    2       TABLE ACCESS (FULL) OF 'T2'
   4    1     SORT (JOIN)
   5    4       TABLE ACCESS (FULL) OF 'T1'

-- thats sort merge join. A sort on the individual tables followed by a merge.

You could use hints to get a specific join, if you think its going to improve the performance. 

For eg,
SQL> select /*+ USE_MERGE (t1 t2) */ * from t1,t2 where t1.x=t2.x;

no rows selected

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=8 Card=82 Bytes=2132
          )

   1    0   MERGE JOIN (Cost=8 Card=82 Bytes=2132)
   2    1     SORT (JOIN) (Cost=4 Card=82 Bytes=1066)
   3    2       TABLE ACCESS (FULL) OF 'T1' (Cost=2 Card=82 Bytes=1066
          )

   4    1     SORT (JOIN) (Cost=4 Card=82 Bytes=1066)
   5    4       TABLE ACCESS (FULL) OF 'T2' (Cost=2 Card=82 Bytes=1066
          )



A join method may not be *the best* join method in all cases. It all depends.. Otherwise,we wouldnt have so many join methods!

-Thiru
Previous Topic: Forcing optimizer to use full scan
Next Topic: Parallel sub-queries
Goto Forum:
  


Current Time: Thu Apr 18 22:34:34 CDT 2024