Databases Reference
In-Depth Information
Creating Tables
The table is the most basic and most important object you will create in a data-
base. Essentially, you could do without every other database object in a database
except for tables. Without tables, you cannot store anything in a database.
You can create tables with the
statement or “on the fly” with
a method known as Create Table As Select, or CTAS.
Once you know that you need to create a table, you must decide what kind of
table you want. In this section, we'll cover the most common types of tables:
CREATE
TABLE
Relational tables
◆
Tables created directly from the result of a query
◆
Tables whose data resides outside the database
◆
Tables with a definition that is available to all sessions but whose data is
local to the session that created the data
◆
Relational Tables
A
is the most common form of a table in the Oracle database. It
is created with the
relational table
relational table
The most common form of a table in the
Oracle database; the default type cre-
ated with the
statement, its data is stored in the database,
and it can be partitioned. When you partition a table, the data for the table is
internally stored in two or more pieces to potentially improve performance and
to make the table easier for the DBA to manage if the table has many rows. Par-
titioning tables is covered in more detail in Chapter 12, “Making Things Run
Fast (Enough).”
The basic syntax for the
CREATE
TABLE
state-
ment. A relational table is permanent
and can be partitioned.
CREATE
TABLE
CREATE
TABLE
statement is as follows:
CREATE TABLE [
schema.
]
tablename
(
column1 datatype1
[DEFAULT
expression
]
[
, ...
]);
The table that Scott, the company founder, created back in Chapter 2,
“SQL*Plus and iSQL*Plus Basics,” was built with this statement:
create table emp_hourly (
empno number(4) not null,
ename varchar2(10),
job varchar2(9),
mgr number(4),
hiredate date,
hourrate number(5,2) not null default 6.50,
deptno number(2),
constraint pk_emp
primary key ( empno ) ) ;
