| | Sams Teach Yourself SQL in 24 Hours, Third Edition By Ronald R. Plew, Ryan K. Stephens | | Table of Contents | | | | Appendix C. Answers to Quizzes and Exercises |
Hour 17, "Improving Database Performance" Quiz Answers | 1: | Would the use of a unique index on a small table be of any benefit? | | A1: | The index may not be of any use for performance issues; but, the unique index would keep referential integrity intact. Referential integrity is discussed in Hour 3, "Managing Database Objects." | | 2: | What happens when the optimizer chooses not to use an index on a table when a query has been executed? | | A2: | A full table scan occurs. | | 3: | Should the most restrictive clause(s) be placed before the join condition(s) or after the join conditions in the WHERE clause? | | A3: | The most restrictive clause(s) should be evaluated before the join condition(s) because join conditions normally return a large number of rows. | Exercise Answers | Q1: | Rewrite the following SQL statements to improve their performance. Use the EMPLOYEE_TBL and the EMPLOYEE_PAY_TBL as described here: | EMPLOYEE_TBL | | EMP_ID | VARCHAR(9) | NOT NULL Primary key, | | LAST_NAME | VARCHAR(15) | NOT NULL, | | FIRST_NAME | VARCHAR(15) | NOT NULL, | | MIDDLE_NAME | VARCHAR(15), | | | ADDRESS | VARCHAR(30) | NOT NULL, | | CITY | VARCHAR(15) | NOT NULL, | | STATE | varCHAR(2) | NOT NULL, | | ZIP | INTEGER(5) | NOT NULL, | | PHONE | VARCHAR(10), | | | PAGER | VARCHAR(10), | | CONSTRAINT EMP_PK PRIMARY KEY (EMP_ID) | EMPLOYEE_PAY_TBL | | EMP_ID | VARCHAR(9) | NOT NULL primary key, | | POSITION | VARCHAR(15) | NOT NULL, | | DATE_HIRE | DATETIME, | | | PAY_RATE | DECIMAL(4,2) | NOT NULL, | | DATE_LAST_RAISE | DATETIME, | | | SALARY | DECIMAL(8,2), | | | BONUS | DECIMAL(8,2), | | CONSTRAINT EMP_FK FOREIGN KEY (EMP_ID) REFERENCES EMPLOYEE_TBL (EMP_ID) -
SELECT EMP_ID, LAST_NAME, FIRST_NAME, PHONE FROM EMPLOYEE_TBL WHERE SUBSTRING(PHONE, 1, 3) = '317' OR SUBSTRING(PHONE, 1, 3) = '812' OR SUBSTRING(PHONE, 1, 3) = '765'; -
SELECT LAST_NAME, FIRST_NAME FROM EMPLOYEE_TBL WHERE LAST_NAME LIKE '%ALL%; -
SELECT E.EMP_ID, E.LAST_NAME, E.FIRST_NAME, EP.SALARY FROM EMPLOYEE_TBL E, EMPLOYEE_PAY_TBL EP WHERE LAST_NAME LIKE 'S%' AND E.EMP_ID = EP.EMP_ID; | | A1: | -
SELECT EMP_ID, LAST_NAME, FIRST_NAME, PHONE FROM EMPLOYEE_TBL WHERE SUBSTRING(PHONE, 1, 3) = '317' OR SUBSTRING(PHONE, 1, 3) = '812' OR SUBSTRING(PHONE, 1, 3) = '765'; SELECT EMP_ID, LAST_NAME, FIRST_NAME, PHONE FROM EMPLOYEE_TBL WHERE SUBSTRING(PHONE, 1, 3) IN ('317', '812', '765'); From our experience, it is better to convert multiple OR conditions to an IN list. -
SELECT LAST_NAME, FIRST_NAME FROM EMPLOYEE_TBL WHERE LAST_NAME LIKE '%ALL%; SELECT LAST_NAME, FIRST_NAME FROM EMPLOYEE_TBL WHERE LAST_NAME LIKE 'WAL%; You cannot take advantage of an index if you do not include the first character in a condition's value. -
SELECT E.EMP_ID, E.LAST_NAME, E.FIRST_NAME, EP.SALARY FROM EMPLOYEE_TBL E, EMPLOYEE_PAY_TBL EP WHERE LAST_NAME LIKE 'S%' AND E.EMP_ID = EP.EMP_ID; SELECT E.EMP_ID, E.LAST_NAME, E.FIRST_NAME, EP.SALARY FROM EMPLOYEE_TBL E, EMPLOYEE_PAY_TBL EP WHERE E.EMP_ID = EP.EMP_ID AND LAST_NAME LIKE 'S%'; List join operations first in the WHERE clause (check with your implementation of SQL on how the optimizer reads conditions in the WHERE clause). Many implementations ' optimizers evaluate data listed last in the WHERE clause first. It is important to filter data before all rows between tables are joined. Also try to evaluate indexed conditions first. | |
|