surface

Type of table:
The heap table contains randomly arranged variable length rows.
Tables in an index organization store rows in the order of index keys.
Index clusters can denormalize parent-child tables so that related rows from different tables can be stored together.
Hash clustering requires rows to be randomly distributed, which breaks the ordering by item sequence.
The partition table stores rows in a separate physical structure (partition) and allocates rows based on the values of the columns.
Note: table structure has no effect on SQL. Impact on performance.

To create a table using a column specification:

Syntax:

CREATE TABLE [schema.]table [ORGANIZATION HEAP]
[column datatype [DEFAULT expression]]
[,column datatype [DEFAULT expression]];

Give an example:

-- Create table 
-- create date      :2018-01-18
-- authors          :BlackHeart
create table HDM.CUX_BH_TEST_DATA(
    id  number not null,
    l_name  varchar2(200),
    l_code  varchar2(200)  not null,
    created_by        NUMBER(15) not null,
    creation_date     DATE not null,
    last_updated_by   NUMBER(15) not null,
    last_update_date  DATE not null
)
tablespace HDM_DATA
  pctfree 10
  initrans 10
  maxtrans 255
  storage
  (
    initial 128K
    next 128K
    minextents 1
    maxextents unlimited
  );

Note: the default clause cannot generate default values through subqueries, only literal values or functions can be specified.

To create a table using subqueries:

CREATE TABLE [schema.]table AS subquery;

Change table definition

1 add columns:

alter table epm add (job_id number);

2 modify the column:

alter table epm modify (commission_pct number(4,2) default 0.05);

3 delete columns:

alter table epm drop column commission_pct;

4 marked as unused:

alter table epm set unused column job_id;

5 ranks:

alter table epm rename column hire_date to recruited;

6 mark the table read-only:

alter table epm read only;

Delete table

Syntax:

DROP TABLE [schema.]tablename;

Give an example:

drop table epm;

Create and use temporary tables

Syntax:

CREATE GLOBAL TEMPORARY TABLE temp_tab_name
(column datatype [,column datatype])
[ON COMMIT {DELETE|PRESERVE} ROWS];

SQL commands executed on temporary tables are much faster than those executed on permanent tables.

1 a temporary table is not a segment in a permanent table space.
2. DML for temporary tables does not generate redo data.