On the move:

I'm currently moving the following to their own blogs: Unix, HTML for Blogs, Perl & Java.
The links are on the left side.

Friday, June 6, 2008

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;

No comments:

Post a Comment