What problems could be caused by not having indexes

Assignment Help Database Management System

Reference no: EM13192819

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

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

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

| 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

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

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

| 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?

Reference no: EM13192819

Questions Cloud

State methanol burns in air according to the equation below : Methanol burns in air according to the equation below. How many grams of methanol can be burned by 10 grams of oxygen?

Create a sql statement and execute the same in sqlplus : Statistics can be created on tables, indexes columns and as well as on the individual columns. But, if for some reason table or index statistics have not been updated, then this may result in a full table scan.

State what temperature is needed to double the equilibrium : What temperature is needed to double the equilibrium constant from its value at 298 K? What temperature is needed to increase the equilibrium constant by a factor of 10? What if the standard enthalpy change were 20.0 kJ?

State what actions should an exposed person : What actions should an exposed person take during the immediate aftermath of the explosion? D. Lastly, if radioactive material is involved in a disaster incident such as this and there is a fire ongoing in the area where the container is located, ..

What problems could be caused by not having indexes : What problems could be caused by not having appropriate indexes and what problems could be cause by having too many indexes?

Depict correlation diagram the homo of allyl anion : draw correlation diagram the HOMO of allyl anion and the LUMO of ethylene and comment on the symmetry match of the two.

How much does the chemical potential of each molecule : Consider one mole of a monatomic ideal gas at 300 K that udergoes a free expansion from a volume of 1m^3 to 10 m^3. a). How much does the chemical potential of each molecule in the gas change?

How does the order of joins in an sql statement : How does the order of joins in an SQL statement affect the performance of the join and what can the DBA do to determine the preferred order of joins for an SQL statement that includes the join of at least three tables?

Define amino acid in an ion exchange chromatography column : particular amino acid in an ion exchange chromatography column. What is the peak width after the amino acid has migrated 100 cm?

User Account

All Pages