Chapter 9 Managing Schema Objects

Create and Modify Tables
CREATE TABLE table_name (column_name datatype,...);
ALTER TABLE table_name ADD(column_name datatype,...);
ALTER TABLE table_name MODIFY(column_name newdatatype);
ALTER TABLE table_name DROP COLUMN column_name;

DROP TABLE table_name [CASCADE CONSTRAINTS]; the drop will fail if it is parent table unless cascade specified.


Manage Constraints
A constraint violation will force an automatic rollback of the entire statement that hit the problem, not just the single action within the statment, and not the entire transaction.

Type:
UNIQUE: NULL value accepted ok
NOT NULL: Can specify default value
PRIMARY KEY: UNIQUE + NOT NULL, one table only one primary key
FOREIGN KEY: parent-child relationship, in child's column same datatype as parent, null value ok orphan row, parent can be primary or unique

ON DELETE CASCADE
ON DELETE SET NULL

It is not possible to drop or truncate the parent table in a foreign key relationship, even if there are no rows in the child table.

self-referencing possible.

CHECK: e.g. NOT NULL is preconfigured CHECK. Rule evaluate to TRUE or FALSE

Constraint State: apply to all sessions
ENABLE VALIDATE (Default)
DISABLE NOVALIDATE -> ENABLE NOVALIDATE -> Some Update -> ENABLE VALIDATE
DISABLE VALIDATE: impossible. The end result is that the table is locked against DML commands.

ALTER TABLE table_name MODIFY CONSTRAINT constraint_name ENABLE/DISABLE VALIDATE/NOVALIDATE;

Constraint Checking: change to deferrable would be session specific
IMMEDIATE: when statement is executed (Default)
DEFERRED: when transaction is committed

SET CONSTRAINT constraint_name DEFERRED;
DML commands;
COMMIT;
SET CONSTRAINT constraint_name IMMEDIATE;

For the constraint to be deferrable:
ALTER TABLE table_name ADD CONSTRAINT constraint_name
CHECK (column_name IS NOT NULL) DEFERRABLE INITIALLY IMMEIDATE;


Create Indexes (Good for Query not for DML)
to enforce primary, foreign and unique key constraints
to improve performance of queries
sorted list of key values with pointer(rowid) to the row in the table
rowid is globally unique

For search, WHERE clause
For sorting, ORDER BY, GROUP BY, UNION
For join tables, JOIN (nested loop join, hash join, sort merge join)

Type of Index
B* Tree Indexes:
cardinality in the column is high, column is used in WHERE or JOIN conditions

Bitmap Indexes:
cardinality in the column is low (1,0), columns is used in Boolean Algebra operations

Index Type Options
Unique or non-unique
Reverse Key
Compressed
Composite
Function based
Ascending/Descending

Creating and Using Indexes
CREATE [UNIQUEBITMAP] INDEX [schema.]index_name
ON [schema.]table_name (column [, column...]);

DROP INDEX index_name;

Explicit index would survive if constraint is dropped.



Create and Use Temporary Tables
CREATE GLOBAL TEMPORARY TABLE temp_table_name
(column datatype [, column datatype...])
[ON COMMIT {DELETE PRESERVE} ROWS];

table definition is visible to all sessions
rows are private to the session that inserted them
PRESERVE will keep the rows till the session end
exist only in the PGA of the session (if PGA not enough, goes to temporary segment in the user's temporary tablespace (not go via database buffer cache))
no disk activity or even database buffer cache
DML does not generate REDO