Temporary SQL Tables

1
Temporary SQL Tables

• Los Angeles Pierce CollegeComputer Science 560

2
How Do We Get One Column From Two Tables?

• NCAA Table
• NCAA_SEQ_KEY NOT NULL NUMBER(5)
  
  
  
  
• NCAA_YEAR NUMBER(4)
  
  
  
  
• NCAA_PLACE NUMBER(1)
  
  
  
  
• NCAA_SCHOOL CHAR(30)
  
  
  
  
• NIT Table
• NIT_SEQ_KEY NOT NULL NUMBER(5)
  
  
  
  
• NIT_YEAR NUMBER(4)
  
  
  
  
• NIT_PLACE NUMBER(1)
  
  
  
  
• NIT_SCHOOL CHAR(30)
  
  
  
  
• NIT_TIE CHAR(1)
  
  
  
  

3
Whats The Desired Output?

• Three columns
• School Name
• Tournament won
• Year
• Something like
• School Tournament Year
• UCLA NCAA 1972UCLA NIT
  1985UCLA NCAA 1995

4
The Problem

• How do we get two different columns from two
  different tables into one result column?
  (School, year)1
• How do we insert the table name into a result
  column?
• Answer cheat.1 Well pretend we cant
  do this with UNION

5
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))

6
Ideal SQL Selection Statement

• SELECT SCHOOL,TOURNAMENT, YEAR FROM BB_WINNERS
  WHERE PLACE 1 ORDER BY SCHOOL, YEAR

7
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

8
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.

9
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.

10
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 users
  session ends (PRESERVE option)
• EXIT in SQLPlus
• We can use either one, depending upon how long we
  need the temporary data

11
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)

12
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)

13
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)
• Sooften a useful technique
• Use must be evaluated carefully in high-activity
  systems

14
Questions?

• For further information, refer to the CREATE
  statement in the SQL reference for the level of
  Oracle you are using.