Create a sql statement and execute the same in sqlplus

Assignment Help Database Management System
Reference no: EM13192822

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)

Reference no: EM13192822

Questions Cloud

Compute the change in the entropy of the universe : calculate the change in the entropy of the universe between the time that the contents of the glass enter the lake and when thermal equilibrium is subsequently restored. State any approximations made in your calculation.
Develop a swot analysis on the viability : Develop a SWOT analysis on the viability of upgrading to Windows 8 for the organization as a whole and provide a detailed upgrade plan for the organization, including the sales staff.
State what is the percent abundance of each isotope : Copper has two naturally occurring isotopes,63Cu (isotopic mass 62.9396 amu) and 65Cu (isotopic mass 64.9278 amu). If copper has an atomic mass of 63.546 amu, what is the percent abundance of each isotope?
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.

Reviews

Write a Review

 

Database Management System Questions & Answers

  Submit a consolidated word document with the diagram

Create and submit a screenshot of your database diagram based on the tables, and generate the data definition language (DDL) for each table.

  Evaluate the success wellco and pharmacare shareholders

Evaluate the success WellCo and PharmaCARE shareholders

  Create a data warehouse conceptual design

Create a Data Warehouse conceptual design using Star Schema Modelling and document all steps during the design process

  Design updateable database for storing customer and sales

Design an updateable database for storing customer and sales data. Explain how to deal with the problems of missing data.

  Calculate confidence and support of rule

Assume that 30% of science students are majoring in biology. Would you consider to be novel with respect to rule? Describe. Calculate confidence and support of rule.

  Key functions of organization-map to strategic information

From information system point of view, we should take the key functions of organization and map them to strategic information. Do your companies follow this process or are steps skipped?

  Describe binary lock function

Describe relationships with example. Also illustrate degree of relationship for that example. What do you mean by locks. Write dow a binary lock function.

  Convert table to 3nf and represent answer in dbdl

Convert the table to 3NF. Represent your answer in DBDL. (i.e. Give table name and fields. Underline the primary key. Draw an entity-relationship diagram showing all relationships.)

  Display table giving balance on loan at end of each month

Write a program to solve the following problem: A TV set is purchased with a loan of $563 to be paid off with 5 monthly payments of $116. The interest rate is 1 percent per month. Display a table giving the balance on the loan at the end of ea..

  Create a gis database of information about vietnam

You are asked to create a GIS database of information about Vietnam at scale of 1:100,000. Database will include the following: Which of these features are best represented by raster model? Explain why?

  Explain leaf of b tree which holds a sublist

Artificially small example of B+ tree is shown here (pdf). (Note only part of tree is shown in detail.) What nodes of example B+ tree are visited to find posting list for "dune"?

  Changes require to make-premiere products database

Point out the changes you require to make to Premiere Products database to support following situation: A customer is not necessarily represented by a single sales rep but can be represented by several reps.

Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd