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

1: Rewrite the following SQL statements to improve their performance. Use the EMPLOYEE_TBL and the EMPLOYEE_PAY_TBL as described here:
EMPLOYEE_TBL   
EMP_IDVARCHAR2(9)NOT NULLPRIMARY KEY,
LAST_NAMEVARCHAR2(15)NOT NULL, 
FIRST_NAMEVARCHAR2(15)NOT NULL, 
MIDDLE_NAMEVARCHAR2(15),  
ADDRESSVARCHAR2(30)NOT NULL, 
CITYVARCHAR2(15)NOT NULL, 
STATECHAR(2)NOT NULL, 
ZIPNUMBER(5)NOT NULL, 
PHONECHAR(10),  
PAGERCHAR(10),  

CONSTRAINT EMP_PK PRIMARY KEY (EMP_ID)

EMPLOYEE_PAY_TBL   
EMP_IDVARCHAR2(9)NOT NULLPRIMARY KEY,
POSITIONVARCHAR2(15)NOT NULL, 
DATE_HIREDATE,  
PAY_RATENUMBER(4,2)NOT NULL, 
DATE_LAST_RAISEDATE,  
SALARYNUMBER(8,2),  
BONUSNUMBER(8,2),  

CONSTRAINT EMP_FK FOREIGN KEY (EMP_ID)
  REFERENCES EMPLOYEE_TBL (EMP_ID)


  1. SELECT EMP_ID, LAST_NAME, FIRST_NAME,
          PHONE
   FROM EMPLOYEE_TBL
   WHERE SUBSTR(PHONE, 1, 3) = '317' OR
         SUBSTR(PHONE, 1, 3) = '812' OR
         SUBSTR(PHONE, 1, 3) = '765';
										
  2. SELECT LAST_NAME, FIRST_NAME
   FROM EMPLOYEE_TBL
   WHERE LAST_NAME LIKE '%ALL%;
										
  3. 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: 
  1. SELECT EMP_ID, LAST_NAME, FIRST_NAME, PHONE
   FROM EMPLOYEE_TBL
   WHERE SUBSTR(PHONE, 1, 3) IN ('317', '812', '765'),

  2. SELECT LAST_NAME, FIRST_NAME
 FROM EMPLOYEE_TBL
 WHERE LAST_NAME LIKE 'WAL%;

  3. SELECT E.EMP_ID, E.LAST_NAME, E.FIRST_NAME,
          EP.SALARY
   FROM EMPLOYEE_PAY_TBL EP,
        EMPLOYEE_TBL E
   WHERE E.EMP_ID = EP.EMP_ID
   AND LAST_NAME LIKE 'S';

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset