Practical Web Programming

Sunday, January 27, 2008

Oracle DDL Basics: A Simple Tutorial on Oracle Data Definition Language

Here's a simple tutorial on Oracle Data Definition Language. I always make this handy at the office as I sometimes forget some of the basis of the Oracle DDL.

1. Data Type.

Type                Storage     Range/Length
----------------- ---------- --------------
NUMBER 16 40 digit floating point
FLOAT 16 40 digit floating point
SMALLINT 16 40 digit floating point
NUMBER(a,b) varies a digits, b precision
FLOAT(a,b) varies a digits, b precision
DECIMAL 16 40 digit
INTEGER 16 40 digits
INTEGER(a) varies a digits
CHAR(a) a a=(1-255)
VARCHAR(a) varies 1 - 255
VARCHAR2(a) varies 1 - 2000
DATE 8 1/1/4217BC - 12/31/4712AD
LONG varies 0 - 2 GB
LONG RAW varies 0 - 2 GB
LONG VARCHAR varies 0 - 2 GB
BLOB varies 0 - 4 GB
CLOB varies 0 - 4 GB
NCLOB varies 0 - 4 GB
BFILE ?? ??
ROWID 8 n/a


* Long datatypes are discouraged in Oracle 8.
Note that are long and blob datatypes are incompatible.

2. Creating a Table.

PCTFREE = Amount of space to leave in block during insert operations. Allows room for records to grow within the same area.
PCUSED = The threshold at which the block is placed back on the free block list.
INITIAL/NEXT = The initial disk allocated, and the next extent size.
LOGGING = Indicates whether operations are written to the redo logs.

CREATE TABLE EMPLOYEE (
EMP_ID NUMBER(8),
LNAME VARCHAR2(30),
FNAME VARCHAR2(15),
HIRE_DT DATE,
SALARY NUMBER(8,2) )

PCTFREE 20
PCTUSED 50
STORAGE (INITIAL 200K NEXT 200K PCTINCREASE 0 MAXEXTENTS 50 )
TABLESPACE ts01 LOGGING;


3. Creating indexes

CREATE UNIQUE INDEX EMP_IDX ON EMPLOYEE (EMP_ID);


/* index create - table has sorted data */
CREATE UNIQUE INDEX IDX_INVOICE_ITEMS   ON INVOICE_ITEMS
(INVOICE_ID,YEAR,FREQ_CODE,FREQ_NUMBER,FIELD_NUMBER,RECORD_SEQ)
TABLESPACE TS_07
NOLOGGING
NOSORT;


/* create index - constraint */
ALTER TABLE  INVOICE_FORMAT
ADD CONSTRAINT PK_INVOICE_FORMAT PRIMARY KEY(INVOICE_ID)
USING INDEX TABLESPACE PROD_IDX_01;


/* Get index information */
SELECT A.COLUMN_NAME,
A.COLUMN_POSITION,
A.INDEX_NAME,
B.UNIQUENESS
FROM USER_IND_COLUMNS A,
USER_INDEXES B
WHERE A.INDEX_NAME = B.INDEX_NAME AND
A.TABLE_NAME = 'IDX_INVOICE_ITEMS'
ORDER BY A.INDEX_NAME, A.COLUMN_POSITION;


/* create bitmap index - table is fairly static, and has less than 1000 distinct values in the indexed column */
CREATE BITMAP INDEX BIX_INVOICE_ARCHIVE
ON INVOICE_ARCHIVE (SALES_ID)
TABLESPACE PROD_IDX_01;


4. Creating constraints

/* primary key constraint */
ALTER TABLE EMPLOYEE (
CONSTRAINT EMP_PK
PRIMARY KEY (EMP_ID));


ALTER TABLE  REPORT_FORMAT
ADD CONSTRAINT PK_REPORT_FORMAT PRIMARY KEY(REPORT_ID)
USING INDEX TABLESPACE PROD_IDX_01;


/* foreign key constraint */
ALTER TABLE EMPLOYEE ADD (
CONSTRAINT EMP_LOC_ASSIGN_FK
FOREIGN KEY (EMP_ID,
LOC_CD)
REFERENCES LOC_REGISTRY (
EMP_ID,
LOC_CD));


5. Creating and using a sequence.

/* create a sequence for employee ids */
CREATE SEQUENCE EMP_ID_SEQ
INCREMENT BY 1
NOMINVALUE
NOMAXVALUE
NOCYCLE
CACHE 20
NOORDER ;


/ * use the next emp id, and increment the sequence */
INSERT INTO EMPLOYEE(EMP_ID, LNAME, FNAME)
VALUES (EMP_ID_SEQ.NEXTVAL, 'SMITH', 'JIM');


/* get the current value of the sequence */
INSERT INTO EMPLOYEE(EMP_ID, LNAME, FNAME)
VALUES (EMP_ID_SEQ.CURRVAL, 'SMITH', 'JIM');


6. Creating triggers.

The example below illustrates versioning of the EMP_RESUME table, which contains a blob field.

CREATE OR REPLACE TRIGGER EMP_RES_INS_TR
AFTER INSERT ON EMP_RES
FOR EACH ROW
DECLARE
VER1 NUMBER ;
EBLOB BLOB ;
VBLOB BLOB ;
BEGIN
EBLOB := EMPTY_BLOB();

SELECT (COUNT(*) + 1) INTO VER1
FROM VEMP_RES
WHERE EMP_ID =:NEW.EMP_ID ;

VBLOB := :NEW.RESUME ;

INSERT INTO VEMP_RES
( EMP_ID, DOC_URL,
A_USERID, D_MODIFIED, VER_NO, RESUME)
VALUES (
:NEW.EMP_ID, :NEW.DOC_URL,
USER, SYSDATE, VER1, EBLOB ) ;

SELECT RESUME
INTO EBLOB
FROM VEMP_RES
WHERE EMP_ID =:NEW.EMP_ID AND
VER_NO = VER1
FOR UPDATE ;

UPDATE VEMP_RES
SET RESUME = VBLOB
WHERE EMP_ID =:NEW.EMP_ID AND
VER_NO = VER1 ;
END;



7. Renaming a table.

RENAME EMPLOYEE TO MANAGER;


8. Synonyms and Database Links.

/* Synonym Creation */
GRANT SELECT ON USER5.COMPANY TO USER6 ;
CREATE SYNONYM USER6.COMPANY5 FOR USER5.COMPANY;


/* Database Link */
CREATE DATABASE LINK ARCHIVE_DATA CONNECT TO
USER5 IDENTIFIED BY TIGER USING 'SERVER5';


/* user within this system can now reference tables using ARCHIVE_DATA.tablename */

9. Changing a column's type or name.

/*Change Type*/
ALTER TABLE CORPORATION MODIFY (COMPANY_NM VARCHAR2(100));


ALTER TABLE employee MODIFY(last_name varchar2(40));


/*Change Name*/
ALTER TABLE EMPLOYEE RENAME COLUMN LAST_NAME TO LNAME;


10. Moving a table.

ALTER TABLE COMPANY MOVE
STORAGE (
INITIAL 200K
NEXT 200K
PCTINCREASE 0
MAXEXTENTS 50 )
TABLESPACE TS_01;


11. Partitioned Tables.

Table partitioning is the best feature ever added to the Oracle RDBMS.
Chunks of data can be appended, replaced, or purged very easily when
using table partitioning. When you have more than 20 million rows in a
non-static table, partitioning is recommended. We found that bitmap
indexes work better than standard indexes on partitioned tables.

/*Add a partition*/
ALTER TABLE PHONE_DATA ADD PARTITION
p2004JUL VALUES (200407) TABLESPACE TS01 ;


/*Populate a partition, replaces existing data, if it exists*/
ALTER TABLE PHONE_DATA EXCHANGE PARTITION
p2004JUL WITH TABLE TMP_SWITCH_DATA ;


/*Move a partition*/
ALTER TABLE PHONE_DATA MOVE PARTITION 
P2004JUL TABLESPACE TS01 NOLOGGING;


/*Truncate a partition*/
ALTER TABLE PHONE_DATA TRUNCATE PARTITION;


/*Drop a partition*/
ALTER TABLE PHONE_DATA DROP PARTITION p2004JUL 
update global indexes;


/*Get partition information*/
SET PAGESIZE 0
SET LINESIZE 120
SELECT TABLE_NAME, PARTITION_NAME, BLOCKS, TABLESPACE_NAME
FROM USER_TAB_PARTITIONS
ORDER BY TABLE_NAME, PARTITION_NAME;


/*Create a local partition index*/
CREATE BITMAP INDEX BIX_PHONE_DATA ON PHONE_DATA
(PERIOD_KEY)
TABLESPACE TS02
LOCAL;


/*Rebuild*/
ALTER TABLE PHONE_DATA MODIFY PARTITION
p2004JUL REBUILD UNUSABLE LOCAL INDEXES;

0 comments:

Recent Post