Oracle 10g Free Training : Creating Tables
In this training you will learn about Creating Tables and Parallelizing Table Creation.
Creating Tables
To create a new table in your schema, you must have the CREATE TABLE system privilege. To create a table in another user's schema, you must have the CREATE ANY TABLE system privilege. Additionally, the owner of the table must have a quota for the tablespace that contains the table, or the UNLIMITED TABLESPACE system privilege.
Create tables using the SQL statement CREATE TABLE.
Creating a Table
When you issue the following statement, you create a table named Employee in the your default schema and default tablespace. The below mentioned code can either be executed through SQL*PLUS or iSQL*PLUS.
CREATE TABLE employee (
..........empno NUMBER(5) PRIMARY KEY,
..........ename VARCHAR2(15) NOT NULL,
..........job VARCHAR2(10),
..........mgr NUMBER(5),
..........hiredate DATE DEFAULT (sysdate),
..........sal NUMBER(7,2),
..........comm NUMBER(7,2),
..........deptno NUMBER(3) NOT NULL
); ..........................................................................
Figure 1. Table creation through SQL*PLUS
Parallelizing Table Creation
When you specify the AS SELECT clause to create a table and populate it with data from another table, you can utilize parallel execution. The CREATE TABLE ... AS SELECT statement contains two parts: a CREATE part (DDL) and a SELECT part (query). Oracle Database can parallelize both parts of the statement. The CREATE part is parallelized if one of the following is true:
A PARALLEL clause is included in the CREATE TABLE ... AS SELECT statement
An ALTER SESSION FORCE PARALLEL DDL statement is specified
If you parallelize the creation of a table, that table then has a parallel declaration (the PARALLEL clause) associated with it. Any subsequent DML or queries on the table, for which parallelization is possible, will attempt to use parallel execution.
The following simple statement parallelizes the creation of a table and stores the result in a compressed format, using table compression:
CREATE TABLE admin_emp_dept
..........PARALLEL COMPRESS
..........AS SELECT * FROM employee
..........WHERE deptno = 10;
