the order in which the tables in your queries are


The order in which the tables in your queries are joined can have an effect on the query performs. If your query is joining all the large tables first and then joins to a smaller table, then this can cause a lot of unnecessary processing. The join order in each step means that the fewest number of rows are being returned to the next step, which in turn makes query performance better. Usually DBA's will try to find the best possible access method of the tables by performing the Explainplan on the query or by verifying the optimizer statistics. They also try to optimize the SQL workload by identifying the proper indexesfields in the "Where" clause for joining the tables. Before applying WHERE clause, the DBA's will always try to provide the best suited condition in "ON" condition while performing the Join, this will filter the data and reduce the join result itself. The subsequent join conditions will be executed with filtered data which makes better performance. After that only WHERE condition will apply filter conditions.They even try to eliminate the unnecessary large full table scan.

Let us consider the below Join query. Here Explain Plan will show how the statements are executed and it also shows the number of rows performed and the cost and CPU time taken. It produces the final record set of 665 rows.

setautotracetraceonly explain

Select P.ProductID, PS.ProductID, COL.ProductID

From Customerorderitem COL

Inner Join ProductSupplier PS

On COL.ProductID = PS.ProductID

Inner Join Product P

On COL.ProductID=  P.ProductID

Execution Plan

----------------------------------------------------------

Plan hash value: 1711654722

 

--------------------------------------------------------------------------------------------

| Id  | Operation              | Name              | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT       |                   |  4882 |   185K|    11  (10)| 00:00:01 |

|   1 |  NESTED LOOPS          |                   |  4882 |   185K|    11  (10)| 00:00:01 |

|*  2 |   HASH JOIN            |                   |  4882 |   123K|    11  (10)| 00:00:01 |

|   3 |    INDEX FAST FULL SCAN| PRODSUPPLPK       |   288 |  3744 |     3   (0)| 00:00:01 |

|   4 |    TABLE ACCESS FULL   | CUSTOMERORDERITEM |  4882 | 63466 |     7   (0)| 00:00:01 |

|*  5 |   INDEX UNIQUE SCAN    | PRODUCTPK         |     1 |    13 |     0   (0)| 00:00:01 |

--------------------------------------------------------------------------------------------

 

 

After modifying the Join order, executed the query with Explain Plan and it executed with little less Cost than the previous query. It even reduced the number of rows processed based on the Nested loop of table "Productsupplier" and "Product"which returned 288 rows.It also produces the final record set of 665 rows. This Join order is preferred over the first query.

 

setautotracetraceonly explain

 

Select  P.ProductID, PS.ProductID, COL.ProductID

From Customerorderitem COL

Inner Join Product P

On COL.ProductID=  P.ProductID

Inner Join ProductSupplier PS

On P.ProductID = PS.ProductID;

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 862510404

 

--------------------------------------------------------------------------------------------

| Id  | Operation              | Name              | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT       |                   |  4882 |   185K|    11  (10)| 00:00:01 |

|*  1 |  HASH JOIN             |                   |  4882 |   185K|    11  (10)| 00:00:01 |

|   2 |   NESTED LOOPS         |                   |   288 |  7488 |     3   (0)| 00:00:01 |

|   3 |    INDEX FAST FULL SCAN| PRODSUPPLPK       |   288 |  3744 |     3   (0)| 00:00:01 |

|*  4 |    INDEX UNIQUE SCAN   | PRODUCTPK         |     1 |    13 |     0   (0)| 00:00:01 |

|   5 |   TABLE ACCESS FULL    | CUSTOMERORDERITEM |  4882 | 63466 |     7   (0)| 00:00:01 |

 Indexes. Consider the Global Engineering and the Retail Company databases in your answers to these questions.

a. What problems could be caused by not having appropriate indexes?

b. What problems could be cause by having too many indexes?

c. What do database statistics contribute to defining appropriate indexes?

Request for Solution File

Ask an Expert for Answer!!
Database Management System: the order in which the tables in your queries are
Reference No:- TGS0501663

Expected delivery within 24 Hours