Oracle 10g Tutorials : Creating Tables

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.

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;

Figure 2. Parallelizing Table creation through SQL*PLUS