SkillBuilders | Oracle 9i Administration | Appendix C: Other Table Types

Instructor's Notes

(If applicable)

Appendix C: Other Table Types
This lesson covers:

IOT's
Temporary Tables
Index Clustered Tables
Hash Clustered Tables
Nested Tables
Object Tables

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

IOTs store the table data in a B-tree index - there is no actual table. The data is sorted according to the primary key. A primary key is required. It is useful as a performance benefit for tables that are always accessed by primary key.
Rows greater than 50 bytes use an overflow segment. This can diminish the performance benefit.
Note that ALTER TABLE MOVE does work with Index Organized Tables. See more on ALTER TABLE MOVE later in this section.

Other Text:
(Examples or comments displayed on slide, if any).

CREATE TABLE state
(state_code CHAR(2) PRIMARY KEY,
state VARCHAR2(30) )
ORGANIZATION INDEX;

Instructor's Notes

(If applicable)

Temporary tables are managed with DDL and DCL (data control language) similar to permanent (normal) tables.
You can create public synonyms for them, need to grant permissions on them (to allow other users to access them).
The data is temporary. Duration is for a session or transaction.
The data is private. Visible only to the user in the same session. No other user or session can access another session’s temporary data.
Supplemental Notes
- Since the data is temporary and private, note that an import or export on a temporary table will only import/export the structure.
- Session specific temporary tables:
  - data exists within the current session
  - when the session terminates, the data for that session is automatically deleted by Oracle
- Transaction specific temporary tables:
  - data exists within the current transaction
  - when the transaction terminates, the data for that transaction is automatically deleted by Oracle

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

No segment/extent information is available in the DBA/USER/ALL views on the object. Information shows up as “another” temporary segment.
Only information available in the data dictionary is in %_TABLES, that shows it is temporary.
Temporary Segment Example:
create user scott . . .
temporary tablespace temp1;

create user john . . .
temporary tablespace temp2;
as scott
create global temporary table scott.temp_emp . . .
grant select, update, insert, delete on scott.temp_emp
to john;

as john
insert into scott.temp_emp . . .

The temporary segment is created in john’s temporary tablespace (TEMP2), not scott’s (TEMP1).

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

The phrase “GLOBAL TEMPORARY “ (both words) is required. Note also that you must GRANT privileges on the temporary table to any user who needs to make use of the table. The example above shows granting “all” - SELECT , UPDATE, DELETE and INSERT privileges on the table to user DAVE.
Temporary Tables Limitations:

No referential integrity allowed.

Other constraint types OK

No partitioning or clustering.
Tablespace cannot be specified.
Table cannot be modified to

Become permanent
Change data duration (session, transaction)

Data cannot be exported or imported.
Data cannot be replicated.
Primary keys are okay, just not foreign keys.
The temporary segment is assigned to the temporary tablespace of the user running the DML operation, not the owner of the temporary table.

Other Text:
(Examples or comments displayed on slide, if any).

CREATE GLOBAL TEMPORARY TABLE current_orders
(order_no number,
cust_no number,
order_date date,
total_order_price number(7,2))
ON COMMIT PRESERVE ROWS;
GRANT ALL on current_orders TO dave;

Instructor's Notes

(If applicable)

With Index Clustered Tables, Oracle stores data from multiple tables in the same blocks. This reduces the number of blocks that need to be read in order to join these tables during a query. Careful consideration should be given prior to deciding on the use of clusters. If the tables involved happen to be accessed individually and not via a join then the performance of your queries may suffer.
Before tables can be clustered, a cluster and a cluster key index must first be created.
CREATE CLUSTER emp_dept_cl
(deptno NUMBER(2));

CREATE INDEX emp_dept_cl_index
ON CLUSTER emp_dept_cl
STORAGE (INITIAL 2M NEXT 1M)
TABLESPACE data_index;

Once the cluster and cluster key index have been created then the tables to be included in the cluster can be created as shown in the slides.

Other Text:
(Examples or comments displayed on slide, if any).

CREATE TABLE dept
(deptno NUMBER(2),
dname VARCHAR2(20),
loc VARCHAR2(20))
CLUSTER emp_dept_cl (deptno);

CREATE TABLE emp
(empno NUMBER(2),
ename VARCHAR2(20),
job VARCHAR2(20),
deptno NUMBER(2))
CLUSTER emp_dept_cl (deptno);

Instructor's Notes

(If applicable)

The Hash Clustered Table is similar to an Index Clustered Table except the physical location of data as it is entered is decided by a hash function, either an internal function or one that you provide.
CREATE CLUSTER emp_dept_hcl
(hash_key NUMBER)
hashkeys 5000;

Tables are added to the hash cluster just as there are with the index cluster.
CREATE TABLE dept
(deptno NUMBER(2),
dname VARCHAR2(20),
loc VARCHAR2(20))
CLUSTER emp_dept_hcl (deptno);

CREATE TABLE emp
(empno NUMBER(2),
ename VARCHAR2(20),
job VARCHAR2(20),
deptno NUMBER(2))
CLUSTER emp_dept_hcl (deptno);

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

A nested table is a “table within a table”. It is often used to model 1:N relationships using a single table, e.g. list of courses taken by a student, or offered by a department.
The nested table can contain many columns, and could itself be:

a variable
a parameter to a function
result of a function
attribute of a larger table

Nested tables are “bags”, in that they do not retain the subscripts (ordering) when stored in the database. A "bag" is a random collection. It does not need an order, but the objects within it are all related.
They are “sparse” in nature (when retrieved into a PL/SQL variable, they are given continuous subscripts).

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

Nested Table Example
- The last clause is required to identify the nested table, and to name the storage table.
- More than one NESTED TABLE clauses are possible, separated with commas.
- The TABLE type could not have been put directly in the CREATE TABLE statement (the CREATE TYPE statement was necessary).
Supplemental Notes
- Indexing the column of a nested table is possible, as long as it is legal to index the column outside the nested table.

CREATE INDEX class_locn_idx ON courses_stor_tab(locn);
The table name used must be the storage table name.

Restrictions
- It is not possible to partition a nested table.
- Nested tables cannot contain nested tables.

Other Text:
(Examples or comments displayed on slide, if any).

CREATE TYPE CourseType AS OBJECT (
cnum char(3),
credits number,
coursedescription varchar2(40),
locn varchar2(40));

CREATE TYPE CourseList AS TABLE OF CourseType;

CREATE TABLE DEPT (dNo integer(4),
dName varchar2(32),
offerings courseList)
NESTED TABLE offerings STORE AS courses_stor_tab;

Instructor's Notes

(If applicable)

This kind of table can be viewed in two ways:

As a multi-column table in which each attribute of the object type occupies a column (which allows relational operations).

INSERT INTO skill_table VALUES
('Typing', 'Proficient using typewriters');

As a single-column table in which each row is an object (which allows object-oriented operations).

INSERT INTO skill_table VALUES
(skill_type ('Typing', 'Proficient using typewriters'));

The first example inserts a SKILL_TYPE object into SKILL_TABLE as a multi-column table. The second example Inserts into SKILL_TABLE a single-column table.

Other Text:
(Examples or comments displayed on slide, if any).

CREATE TABLE skill_table OF skill_type;