+1-415-670-9189
info@expertsmind.com
Create a sql statement and execute the same in sqlplus
Course:- Database Management System
Reference No.:- EM13192822




Assignment Help
Expertsmind Rated 4.9 / 5 based on 47215 reviews.
Review Site
Assignment Help >> Database Management System

1. Not having an appropriate index can cause a full table scan while performing the select statement by using a non-index field in the WHERE clause. Such queries will increase the logical read of the table and increase the disk input/output calls, i.e because such SQL statement will performs a row by row search to find the data that a query is looking for. For example consider a SQL statement and execute the same in SQLPLUS with the Explain plan option.

setautotracetraceonly explain

Select * from customer

Where Custlname = 'Dobson';

Execution Plan

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

Plan hash value: 2844954298

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

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

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

|   0 | SELECT STATEMENT  |          |     7 |   910 |   171   (1)| 00:00:03 |

|*  1 |  TABLE ACCESS FULL| CUSTOMER |     7 |   910 |   171   (1)| 00:00:03 |

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

This query will perform the full table scan on "CUSTOMER" table, because in the where condition we are using the non-index column "CUSTLNAME".

Now consider another example where we use Index column in our where condition.

setautotracetraceonly explain

Select * from customer

Where CustID = 98;

Execution Plan

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

Plan hash value: 908218400

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

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

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

|   0 | SELECT STATEMENT            |            |     1 |   130 |     0   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| CUSTOMER   |     1 |   130 |     0   (0)| 00:00:01 |

|*  2 |   INDEX UNIQUE SCAN         | CUSTOMERPK |     1 |       |     0   (0)| 00:00:01 |

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

This query will perform the Index unique scan on index "CUSTOMERPK" and will perform the table access by Index rowid.  Even we can see the execution time, CPU usage and cost difference between both the queries based on the Index usage.

One of the factor that can cause the problem by having too many index are,

For example consider a table "customerorderitem" in retaildba. This table is modified very often on daily basis, as many customers will be ordering the products or they will be returning the purchased product. If such tables contains too many indexes then this could cause a slow performance problem, i.e. because for every data modification in that table, each index needs to be updated. Which in turn will use more CPU, more memory and more I/O operation. But if any table is modified less often, then having more indexes most likely won't be a problem.

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. This is because most RDBMS's have query optimizers that use those statistics to figure out if using an index is worth or not. The use of statistic will quickly determine which execution plan might product the fastest and most efficient execution plan.And if those statistics are not available, then the RDBMS may wrongly determine that doing a full table scan is more efficient than using an index.(Larsen,2013)




Put your comment
 
Minimize


Ask Question & Get Answers from Experts
Browse some more (Database Management System) Materials
Design and develop the below queries using professional principles and standards:A set of SQL Statements that returns all rows and all data for each table in your database.Two
Find the flno of all flights that depart from Madison. Find the flno of all flights that leave Chicago after Flight 101 arrives in Chicago and nolater than one hour after.
Company's databases do not give them with information required for market planning, lead generation, and account management. What does author say is root cause of this problem
Generate the view called RepairSummary which shows only RepairInvoiceNumber, TotalCost, and TotalPaid. Illustrate the SQL statement to retrieve all RepairSummary data sorted b
Identify the business processes which would be evaluated and tracked with a DW/BI application and state your reasoning or justification for including these in the DW/BI.
Write an SQL statement that displays the training names and training costs for courses that cost between 1000 and 1200. Don't use the BETWEEN Clause.Using the BETWEEN Claus
Create an ERD and relatioal schema in third normal form based upon the following business rules. (Hint: ensure that all attributes are FULLY DETERMINED by the primary key.)
Design and develop the below queries using professional principles and standards: Two SQL Statements that return a subset of columns and a subset of rows using the WHERE claus