source /usr/caen/oracle/local/muscle


/* Declare tables
   Note that "primary key" declarations in Oracle automatically create indexes,
   so for simplicity I have not declared primary keys in these examples */

create table students (
sid integer,
sname varchar(25),
age real, 
gpa real);

create table courses (
cid integer,
cname varchar(25),
credits integer);

create table enroll (
sid integer,
cid integer);



/* Insert dummy data */

DECLARE
i NUMBER;
BEGIN
/* enrollments */
FOR i in 1..10000
LOOP
	INSERT INTO students values(i, 'name'||i, 20, 3.5);
END LOOP;
END;
.
run;


INSERT INTO courses values(1, 'eecs484', 4);
INSERT INTO courses values(2, 'eecs280', 4);
INSERT INTO courses values(3, 'engl101', 3);
INSERT INTO courses values(4, 'eecs485', 4);
INSERT INTO courses values(5, 'span200', 3);

DECLARE
i NUMBER;
BEGIN
/* enrollments */
FOR i in 1..50000
LOOP
	INSERT INTO enroll values(mod(i,10000)+1, mod(i, 5) + 1);
END LOOP;
END;
.
run;




/* How many students ? */
EXPLAIN PLAN 
FOR 
SELECT COUNT(*) 
FROM Students;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);


/* All students with name 'name500' */
EXPLAIN PLAN 
FOR 
SELECT sid, sname
FROM Students
WHERE sname = 'name500';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);


/* Create an index on name */
CREATE INDEX name_index ON students(sname);


/* Try the same query again */
EXPLAIN PLAN 
FOR 
SELECT sid, sname
FROM Students
WHERE sname = 'name500';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);


/* Drop the index */
DROP INDEX name_index;


/* Students and enrollments */
EXPLAIN PLAN FOR
SELECT * 
FROM Students S, Enroll E, Courses C
WHERE S.sid = E.sid AND C.cid = E.cid;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);




/* Clean up */

drop table enroll;
drop table students;
drop table courses;