--------------------------------------------------------------------------
-- SQL EXAMPLES                                                         
-- Databasesystemer, IT University of Copenhagen, Spring 2005
-- Taught by Rasmus Pagh                                        
-- March 30, 2005                                                   
--------------------------------------------------------------------------

-- All of the below is for use in Oracle.

-- Preliminaries:
-- --------------

-- Before autotrace can be used, the following table must be created:

create table PLAN_TABLE (
	statement_id 	varchar2(30),
	timestamp    	date,
	remarks      	varchar2(80),
	operation    	varchar2(30),
	options       	varchar2(30),
	object_node  	varchar2(128),
	object_owner 	varchar2(30),
	object_name  	varchar2(30),
	object_instance numeric,
	object_type     varchar2(30),
	optimizer       varchar2(255),
	search_columns  number,
	id		numeric,
	parent_id	numeric,
	position	numeric,
	cost		numeric,
	cardinality	numeric,
	bytes		numeric,
	other_tag       varchar2(255),
	partition_start varchar2(255),
        partition_stop  varchar2(255),
        partition_id    numeric,
	other		long);

-- To set autotrace and timing on:

set autotrace on;
set timing on;

-- To get sample data (works at ITU until June 2005, may take a while):

CREATE TABLE myprojects (
  first1 CHAR(10), last1 CHAR(10), grade1 INT,
  first2 CHAR(10), last2 CHAR(10), grade2 INT,
  first3 CHAR(10), last3 CHAR(10), grade3 INT
);

INSERT INTO myprojects (SELECT * FROM pagh.projects);

-------------------------------------------------------------------
-- Please remove the table (by writing "DROP TABLE myprojects;") --
-- when you are done, to free space on the database server.      --
-------------------------------------------------------------------

-- Full table scan versus index scan:
-- (notice the execution plan and the number of db block gets)

SELECT count(*) FROM myprojects WHERE last2='Gearloose' and last3='Rabbit';

INSERT INTO myprojects values ('Rasmus','Pagh',11,'Toger','Norgaard',10,'Lars','Bengtsson',10);

CREATE INDEX groupmembers23 on myprojects (last2,last3,first2,first3);

INSERT INTO myprojects values ('Rasmus','Pagh',13,'Toger','Norgaard',11,'Lars','Bengtsson',11);

SELECT count(*) FROM myprojects WHERE last2='Gearloose' and last3='Rabbit';

SELECT count(*) FROM myprojects WHERE last2='Gearloose';

SELECT count(*) FROM myprojects WHERE last3='Rabbit';


-- Buffering
-- (notice the number of db block gets and physical reads)

SELECT count(*) FROM myprojects WHERE last1='Gearloose' and last2='Rabbit';

SELECT max(grade2) FROM myprojects WHERE last1='Gearloose' and last2='Rabbit';

DROP TABLE myprojects;