Database Performance

Abstract

What the reader will learn:

Appendix: Creation Scripts and Hints

To create the EMPHOURS table and add the rows, save this code to a file in your Oracle working directory and call it something like CreateEmpHours.sql. Then Log in as hr/hr and run the script. This generates 400,000 + rows and so it will take up to a minute or so to complete.

Hints on the Over to You Section

Scenario One

set timing on set autotrace on set linesize 200 spool test19.txt

set timing on SELECT COUNT (∗)

FROM emphours

WHERE fee_earning = ‘N’ AND hours < 30 UNION

SELECT COUNT (∗)

FROM emphours

WHERE fee_earning = ‘N’ AND hours BETWEEN 30 AND 40 UNION

SELECT COUNT (∗)

FROM emphours

WHERE fee_earning = ‘N’ AND hours > 40;

SELECT COUNT (case when fee_earning = ‘N’ AND hours < 30 THEN 1 ELSE null END) lessthan30,

COUNT (case when fee_earning = ‘N’ AND hours BETWEEN 30 AND 40 THEN 1

ELSE null END) betwix3040,

COUNT (case when fee_earning = ‘N’ AND hours > 40 THEN 1 ELSE null END) gt40

From EMPHOURS;

spool off

The CASE fails to use the index and so is much slower.

Scenario Two

drop table emphours2;

create table emphours2 (emp_id number, work_date date,

hours number, fee_earning varchar(1),

FOREIGN KEY (emp_id) REFERENCES HR.EMPLOYEES (EMPLOYEE_ID),

PRIMARY KEY (emp_id, work_date));

create or replace

PROCEDURE INSERTEMPHOURS AS BEGIN

DECLARE

eid employees.employee_id%TYPE; hdate employees.hire_date%TYPE; fee emphours2.fee_earning%TYPE; howrs emphours2.hours%TYPE; rnd Integer;

CURSOR c1 IS

SELECT EMPLOYEE_ID, hire_date FROM EMPLOYEES;

BEGIN

OPEN c1;

– loop through each of Employee rows LOOP.

FETCH c1 INTO eid, hdate; EXIT WHEN c1%NOTFOUND;

– loop through every day since they started to sysdate LOOP.

EXIT WHEN hdate > sysdate; hdate: = hdate + 1;

– check if weekend.

IF to_char(hdate, ‘D’) > 1 AND to_char(hdate, ‘D’) < 7 THEN.

– make fee_earning a random Y or N but with more Y values rnd: = DBMS_RANDOM.value(low =  > 1, high =  > 10);

IF rnd > 7 THEN

fee: = ‘N’; ELSE

fee: = ‘Y’; END IF;

– generate a random number of hours works between 20 and 50 howrs: = round(DBMS_RANDOM.value(low =  > 20, high =  > 50)); INSERT INTO emphours2 (emp_id, work_date, hours, fee_earning).

values (eid, hdate, howrs, fee); END IF;

END LOOP;

END LOOP;

CLOSE c1;

END;

END INSERTEMPHOURS;

/

Timing start InsertsNoIdx begin

INSERTEMPHOURS;

end;

/

Timing stop InsertsNoIdx

– now drop the table and recreate with an index drop table emphours2;

create table emphours2 (emp_id number, work_date date,

hours number, fee_earning varchar(1),

FOREIGN KEY (emp_id) REFERENCES HR.EMPLOYEES (EMPLOYEE_ID),

PRIMARY KEY (emp_id, work_date));

create bitmap index fee_bit_idx on Emphours2 (fee_earning);

Timing start InsertsWithIdx begin

INSERTEMPHOURS;

end;

/

Timing stop InsertsWithIdx spool off