DML/DDL

T A B L E S P A C E S

Create

CREATE TABLESPACE tablespacename
DATAFILE datfile_name filesize; -- size 100m autoextend on;

T A B L E S

Create with Primary Key

CREATE TABLE table
(
 field1 VARCHAR2(1) CONSTRAINT pk_constraint PRIMARY KEY
 ,field2 NUMBER NOT NULL
 ,field3 DATE
);

Create with Composite Primary Key

CREATE TABLE table
(
 field1 VARCHAR2(5),
  field2 NUMBER(7,2),
  field3 DATE       ,
    CONSTRAINT pk_table1 PRIMARY KEY (field1, field2)
        USING INDEX TABLESPACE tablespace
);

Create Foreign Key

CREATE TABLE table
(
 field1 VARCHAR2(5) CONSTRAINT pk_constraint PRIMARY KEY,
  field2 VARCHAR2(5) REFERENCES table2 (field2) ON DELETE SET NULL,
  field3 VARCHAR2(5)
);

Create Partitioned Table By Range

CREATE TABLE table
(
  field1 VARCHAR2(5)  CONSTRAINT pk_constraint PRIMARY KEY,
   field2 NUMBER(7,2),
   field3 DATE      
)
PARTITION BY RANGE (field2)
(
  PARTITION part1 VALUES LESS THAN ('value')  TABLESPACE tablespace
  PARTITION part2 VALUES LESS THAN (MAXVALUE) TABLESPACE tablespace
);

Create Partitioned Table By Hash

• Placement is determined by hashing the partition key.
• Decreases I/O contention by a greater distribution of data.

CREATE TABLE table
(
 field1 VARCHAR2(1) CONSTRAINT pk_constraint PRIMARY KEY
,field2 NUMBER NOT NULL
,field3 DATE
)
PARTITION BY HASH (field2)
(
 PARTITION part1 TABLESPACE tablespace1
,PARTITION part2 TABLESPACE tablespace2
);

Create Subpartition

CREATE TABLE table
(
  field1 VARCHAR2(1) CONSTRAINT pk_constraint PRIMARY KEY
 ,field2 NUMBER NOT NULL
 ,field3 DATE
)
PARTITION BY RANGE (field2)
 SUBPARTITION BY HASH (field3) SUBPARTITIONS number
 (
   SUBPARTITION part1 VALUES LESS THAN ('value')  TABLESPACE tablespace
  ,SUBPARTITION part2 VALUES LESS THAN (MAXVALUE) TABLESPACE tablespace
 );

Create Partitioned Table By List

CREATE TABLE table
(
  field1 VARCHAR2(5)  CONSTRAINT pk_constraint PRIMARY KEY
  ,field2 NUMBER(7,2)
  ,field3 DATE      
)
PARTITION BY LIST (field2)
(
  PARTITION part1 VALUES ('value1','value2') TABLESPACE tablespace1
 ,PARTITION part2 VALUES ('value3','value4') TABLESPACE tablespace2
);

Create Index-Organized Table

• Sorted by primary key.
• All data is stored in the index.
• If 'Where' always uses primary key
• Use only if the data is very static
• Most effective when primary key is most of the table
• You can use the 'move online'* option of 'alter table'

CREATE TABLE table
(
 field1 VARCHAR2(5) CONSTRAINT pk_constraint PRIMARY KEY,
  field2 VARCHAR2(5) ,
  field3 VARCHAR2(5)
)
ORGANIZATION INDEX

Copy Some Rows & Columns (but not constraints) (not allowed if LONG type)

CREATE TABLE table2
AS SELECT field1, field2, field3+field4 as field34
     FROM table1
    WHERE table1.field = value;

Copy Structure Only

CREATE TABLE table2
AS SELECT *
    FROM table1
   WHERE 1 = 2;

Copy Table without Generating Redo Log

CREATE TABLE table2
NOLOGGING
AS SELECT *
    FROM table1;

Drop (2 ways)

DROP TABLE table [CASCADE CONSTRAINTS] PURGE;

DROP TABLE table;
PURGE TABLE table;

Truncate (can't rollback) (also see delete rows)

TRUNCATE TABLE table;

Rename (2 ways)

RENAME table1 TO table2; 

ALTER TABLE table RENAME TO table;

Comment

COMMENT ON TABLE table IS 'some table comment';

V I E W S

Create

CREATE [OR REPLACE] VIEW view
 AS SELECT *
     FROM table1
   [WITH READ ONLY];

CREATE [OR REPLACE] VIEW view
(
 field1
, field2
)
 AS SELECT fielda, fieldb
      FROM table1; 

Drop

DROP VIEW view;

C O N S T R A I N T S

Add

ALTER TABLE table
ADD CONSTRAINT pk_constraint PRIMARY KEY (field);
ADD CONSTRAINT fk_constraint FOREIGN KEY (field) REFERENCES table2 (field);
ADD CONSTRAINT uk_constraint UNIQUE      (field);
ADD CONSTRAINT ck_constraint CHECK       (field = value);
MODIFY (field NOT NULL);

Disable

ALTER TABLE table
DISABLE PRIMARY KEY [CASCADE];
DISABLE CONSTRAINT uk_constraint;

Drop

ALTER TABLE table
DROP PRIMARY KEY CASCADE;
DROP CONSTRAINT constraint;
DROP UNIQUE (field);
MODIFY (field NULL);

Enable

ALTER TABLE table
ENABLE [NOVALIDATE] PRIMARY KEY;
ENABLE CONSTRAINT uk_constraint;

I N D E X E S

• a B*-tree is typical - matches column values to their related RowIDs

Create

CREATE UNIQUE INDEX index
ON table1(field1,field2)         /* if multi-column, put most accessed column first */
[REVERSE];

Add Primary Key

ALTER TABLE table
ADD PRIMARY KEY (field);

Bitmap Index

• Use only if data infrequently updated
• Use only in batch processing
• Only on column with just a few values
• i.e.,

         Y 00010011
        N 11101100

CREATE BITMAP INDEX indexname ON table(column);

Create Index for Subpartition

CREATE INDEX index ON table1(field1)
  LOCAL         /* Create a separate index for each partition of table */
(
 PARTITION part1 TABLESPACE tablespace,
  PARTITION part2 TABLESPACE tablespace
);

Drop

DROP INDEX index;

Rebuild

• Rebuilt from the index so there must be enough space for original and new.
• 'Online' allows rebuild while being accessed.
  

ALTER INDEX index REBUILD [ONLINE]
STORAGE (INITIAL 10m NEXT 5m PCTINCREASE 0)
TABLESPACE tablespace; 

C O L U M N S

Add

ALTER TABLE table  ADD (field data_type);

Add a Not Null Column (3 steps)

ALTER TABLE table
 ADD (field data_type);
UPDATE table
 SET column =(
             SELECT column
               FROM table2
              WHERE table1.key = table2.key
            );
ALTER TABLE table
 MODIFY (field NOT NULL);

Drop (option 1 - 2 steps)

ALTER TABLE table  SET UNUSED COLUMN field;
ALTER TABLE table DROP UNUSED COLUMNS;

Drop (option 2)

ALTER TABLE table
DROP COLUMN field1        ; -- one column
DROP        (field1, field2); -- more than one column

Rename

ALTER TABLE table RENAME COLUMN current_column TO new_column;

Change Length

ALTER TABLE table  MODIFY (field data_type);

Decrease Length (4 steps)

CREATE TABLE table2 (key, column)
AS
  SELECT key, column
    FROM table1;

UPDATE table1
  SET column = NULL;

ALTER TABLE table1
 MODIFY (column VARCHAR2(5));

UPDATE table
  SET column =(SELECT column
                 FROM table2
                WHERE table1.key = table2.key); 

Comment

COMMENT ON COLUMN table.field IS 'column comment';

D A T A ( D M L )

Insert (2 ways)

INSERT INTO table
VALUES ('xyz',123);

INSERT /*+ APPEND */ INTO table      -- hint is optional
  SELECT  field1, field2
    FROM table1;
COMMIT;                              -- set autocommit off/on/#

Change

UPDATE table
  SET field1 = 'value' ,
       field2 =  value
WHERE key = value;
COMMIT;

Delete All Rows

DELETE FROM table;
ROLLBACK;

Delete Some Rows

DELETE FROM table where field1 = value;
COMMIT;

R E C Y C L E B I N

Purge

PURGE RECYCLEBIN;

C O M M I T / R O L L B A C K

Note: Exit, Quit or any DDL statement force a commit.
If the machine goes down, an automatic rollback occurs.
Set autocommit off;  -- preferred

Savepoint

Insert ...
Delete ...
Savepoint A;

Insert ...
Delete ...
Savepoint B;

Select ...
Rollback to savepoint B;