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 KeyCREATE TABLE table
(
field1 VARCHAR2(1) CONSTRAINT pk_constraint PRIMARY KEY
,field2 NUMBER NOT NULL
,field3 DATE
);
Create with Composite Primary KeyCREATE TABLE table
(
field1 VARCHAR2(5),
field2 NUMBER(7,2),
field3 DATE ,
CONSTRAINT pk_table1 PRIMARY KEY (field1, field2)
USING INDEX TABLESPACE tablespace
);
Create Foreign KeyCREATE 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 RangeCREATE 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 SubpartitionCREATE 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 ListCREATE 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 OnlyCREATE 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);
DisableALTER TABLE table
DISABLE PRIMARY KEY [CASCADE];
DISABLE CONSTRAINT uk_constraint;
DropALTER TABLE table
DROP PRIMARY KEY CASCADE;
DROP CONSTRAINT constraint;
DROP UNIQUE (field);
MODIFY (field NULL);
EnableALTER 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 KeyALTER 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 SubpartitionCREATE 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 RowsDELETE FROM table;
ROLLBACK;
Delete Some RowsDELETE FROM table where field1 = value;
COMMIT;
R E C Y C L E B I N
PurgePURGE 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;
When that is successfully created, you can write any select against it. If there are errors, look in the log file. Any rejected records are in the bad file.
Posts
