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?