Transcript Temporary SQL Tables

Temporary SQL Tables
Los Angeles Pierce College
Computer Science 560
How Do We Get One Column From
Two Tables?
• NCAA Table:
–
–
–
–
NCAA_SEQ_KEY
NCAA_YEAR
NCAA_PLACE
NCAA_SCHOOL
NOT NULL NUMBER(5)
NUMBER(4)
NUMBER(1)
CHAR(30)
• NIT Table:
–
–
–
–
–
NIT_SEQ_KEY
NIT_YEAR
NIT_PLACE
NIT_SCHOOL
NIT_TIE
NOT NULL NUMBER(5)
NUMBER(4)
NUMBER(1)
CHAR(30)
CHAR(1)
What’s The Desired Output?
• Three columns:
– School Name
– Tournament won
– Year
• Something like:
• School Tournament
• UCLA
NCAA
UCLA
NIT
UCLA
NCAA
Year
1972
1985
1995
The Problem
• How do we get two different columns from
two different tables into one result
column? (School, year)
• How do we insert the table name into a
result column?
1
•
Answer: cheat.
1 We’ll pretend we can’t do this with UNION
Temporary SQL Tables
• We really want a table which looks like:
• CREATE TABLE BB_WINNERS (
BB_SEQUENCE NUMBER(5)
CONSTRAINT bb_seq_pk
PRIMARY KEY,
SCHOOL
CHAR(30),
YEAR
NUMBER(4),
TOURNAMENT CHAR(4),
PLACE
NUMBER(1),
TIE
CHAR(1));
Ideal SQL Selection Statement
• SELECT
FROM
WHERE
ORDER
SCHOOL,TOURNAMENT, YEAR
BB_WINNERS
PLACE = 1
BY SCHOOL, YEAR;
Drawbacks To The Ideal Case
• A special table for one query is probably
wasteful
• Additional table space needed for
something which is rarely used
• Data is duplicated – not an issue in our
sample case, but potentially a problem in a
real world situation
Solution: Temporary SQL Tables
• Acts just like a normal table
• BUT – only exists for the duration of a
session or a transaction
• Disk space for table only needed when it is
actually in use
• Note: assume we have a sequence called
BB_SEQ already defined.
Creating A Temporary Table
• CREATE GLOBAL TEMPORARY TABLE BB_WINNERS (
BB_SEQUENCE NUMBER(5)
CONSTRAINT bb_seq_pk
PRIMARY KEY,
SCHOOL
CHAR(30),
YEAR
NUMBER(4),
TOURNAMENT CHAR(4),
PLACE
NUMBER(1),
TIE
CHAR(1))
ON COMMIT PRESERVE ROWS;
• Above shows the differences from our ideal
table.
Transaction versus Session
• Key concept: When to delete the rows in
the temporary table?
• Transaction level: delete rows in the
temporary table when a COMMIT is issued
• Session level: delete rows when the user’s
session ends (PRESERVE option)
– EXIT in SQL*Plus
• We can use either one, depending upon
how long we need the temporary data
Loading The Temporary Table
• CREATE GLOBAL TEMPORARY TABLE BB_WINNERS (
BB_SEQUENCE NUMBER(5)
CONSTRAINT bb_seq_pk
PRIMARY KEY,
SCHOOL
CHAR(30),
YEAR
NUMBER(4),
TOURNAMENT CHAR(4),
PLACE
NUMBER(1),
TIE
CHAR(1))
ON COMMIT PRESERVE ROWS;
INSERT INTO BB_WINNERS
(SELECT BB_SEQ.NEXTVAL, NCAA_SCHOOL, NCAA_YEAR,
‘NCAA’, NCAA_PLACE, NULL
FROM NCAA);
Adding In Our Other Table
• How to add the data from the NIT table:
• INSERT INTO BB_WINNERS
(SELECT BB_SEQ.NEXTVAL, NIT_SCHOOL,
NIT_YEAR, ‘NIT’, NIT_PLACE,
NIT_TIE
FROM NIT);
When To Use Temporary Tables?
• Performance may or may not suffer:
– “The prudent use of temporary tables can
dramatically improve Oracle SQL
performance.” – Don Burleson
– “Temporary tables slow performance
dramatically.” – Justin Gunther (talking about
Microsoft SQL Server)
• So…often a useful technique
• Use must be evaluated carefully in highactivity systems
Questions?
• For further information, refer to the
CREATE statement in the SQL reference
for the level of Oracle you are using.