Execution Plan

[tabreaz]

These tables are from sh schema.

I erase the SQL statments which select the execution plan from plan_table.

1 explain plan for
2 select c.CUST_LAST_NAME
3 from customers c, sales s, products p
4 where c.cust_id = s.cust_id and
5* p.prod_id = s.prod_id
SQL> /

Explained.

rows plan
---------- ------------------------------------
1016271 SELECT STATEMENT
1016271 NESTED LOOPS
1016271 NESTED LOOPS
PARTITION RANGE ALL
1016271 TABLE ACCESS FULL SALES
1 INDEX UNIQUE SCAN PRODUCTS_PK
1 INDEX UNIQUE SCAN CUSTOMERS_PK

7 rows selected.

After sometime i run the same query it gives the following result, why this kind of instability in chosing the execution plan by rdbms??

1 explain plan for
2 select c.CUST_LAST_NAME
3 from customers c, sales s, products p
4 where c.cust_id = s.cust_id and
5* p.prod_id = s.prod_id
SQL> /

Explained.

rows plan
---------- -----------------------------------
1016271 SELECT STATEMENT
1016271 NESTED LOOPS
1016271 MERGE JOIN
50000 SORT JOIN
50000 TABLE ACCESS FULL CUSTOMERS
1016271 SORT JOIN
PARTITION RANGE ALL
1016271 TABLE ACCESS FULL SALES
1 INDEX UNIQUE SCAN PRODUCTS_PK

9 rows selected.

1 explain plan for
2 select /*+ USE_HASH(c s)*/ c.CUST_LAST_NAME
3 from customers c, sales s, products p
4 where c.cust_id = s.cust_id and
5* p.prod_id = s.prod_id
SQL> /

Explained.

rows plan
---------- -----------------------------------
1016271 SELECT STATEMENT
1016271 NESTED LOOPS
1016271 HASH JOIN
50000 TABLE ACCESS FULL CUSTOMERS
PARTITION RANGE ALL
1016271 TABLE ACCESS FULL SALES
1 INDEX UNIQUE SCAN PRODUCTS_PK

7 rows selected.

1 explain plan for
2 select /*+ USE_NL(c s)*/ c.CUST_LAST_NAME
3 from customers c, sales s, products p
4 where c.cust_id = s.cust_id and
5* p.prod_id = s.prod_id
SQL> /

Explained.

rows plan
---------- --------------------------------------------
1016271 SELECT STATEMENT
1016271 NESTED LOOPS
1016271 NESTED LOOPS
PARTITION RANGE ALL
1016271 TABLE ACCESS FULL SALES
1 INDEX UNIQUE SCAN PRODUCTS_PK
1 TABLE ACCESS BY INDEX ROWID CUSTOMERS
1 INDEX UNIQUE SCAN CUSTOMERS_PK

8 rows selected.

After that i analyzed all the tables and indexes

SQL> analyze table customers compute statistics;

Table analyzed.

SQL> analyze table sales compute statistics;

Table analyzed.

SQL> analyze table products compute statistics;

Table analyzed.

SQL> analyze index products_pk compute statistics;

Index analyzed.

SQL> analyze index customers_pk compute statistics;

Index analyzed.

and rerun the explain plan again

1 explain plan for
2 select c.CUST_LAST_NAME
3 from customers c, sales s, products p
4 where c.cust_id = s.cust_id and
5* p.prod_id = s.prod_id
SQL> /

rows plan
---------- -------------------------------------
1016271 SELECT STATEMENT
1016271 NESTED LOOPS
1016271 MERGE JOIN
50000 SORT JOIN
50000 TABLE ACCESS FULL CUSTOMERS
1016271 SORT JOIN
PARTITION RANGE ALL
1016271 TABLE ACCESS FULL SALES
1 INDEX UNIQUE SCAN PRODUCTS_PK


my question is, after analyzing the tables and indexes oracle must chose the best plan but why it is not chosing the paln which uses Nested Loops, which are best from the above explain plans???

Is it means hints are the best way to choose good execution plan and we may not need those expensive statistics gathering???

[slimdave]

Why is the nested loop plan the best?

[tabreaz]

Becoz the merge join performance additional steps, to sort the data

[slimdave]

Which of the plans uses the most resources on execution?

[slimdave]

by the way, try using ...
select from table(dbms_xplan.display('plan_table',null,'TYPICAL'))

... to get the execution plans, It's a better formatting IMHO

[tabreaz]

anyhow, Which is the best guide for tuning the SQL statements and database??

[tamilselvan]

==
Which is the best guide for tuning the SQL statements and database??
==

Use TKPROF.

Tamil

[pando]

In your example NESTED LOOP is the worse plan you can get.
Imagine SALES has 1 million rows, nested loop that!