DDL and Schema Objects

一. Categorize the Main Database Objects

1. Tables,Views,Synonyms,Indexes,Sequences.

• Table: basically store data in row segmented by columns.
• View: A stored SELECT statement that can be referenced as though it were a table.
• Synonyms: An alias of a table or a view, it provides pointer to an object.
• Index: A means of improving access times to rows in tables, it provides pointer to a row's exact location.
• Sequence: A construct that generates unique numbers.

2. Naming Schema Objects ( with double quotes, all rules can be broken )

• Between 1 to 30 characters long ( database link names may be up to 128 ）
• Reserved words cannot be used.
• Begin with a letter of the alphabet.
• Only include letters, numbers, the underscore(_), the dollar sign($), the bash symbol(#).
• Lowercase will be automatically converted to uppercase.

3. Object Namespaces

• Defines a group of object types, within which all names must be uniquely identified - by schema and name.
• Objects in different namespaces can share the same name.
• These object types all share the same namespace:Tables, Views, Sequences, Private synonyms, Stand-alone procedures, Stand-alone stored functions, Packages, Materialized views, User-defined types.
• These object types each have their own namespace: Indexes, Constraints, Clusters, Database triggers, Private database links, Dimensions.

4. List the Data Types That Are Avaliable for Columns

• Alphanumeric data:
  

             VARCHAR2 : Stored in the database character set, variable-length.

             NVARCHAR2 : Stored in the alternative national language character set, variable-length.

             CHAR: Fixed-length in the database character set.

• Numeric data, all variable length:

             NUMBER: NUMBER( precision, scale)

             FLOAT

             INTEGER

• Date and time data, all fixed length:
  

             DATE: Includes century, year, month, day, hour, minute, second.

             TIMESTAMP:

             TIMESTAMP WITH TIMEZONE:

             INTERVAL YEAR TO MONTH: Record a period in years and months between two DATEs or TIMESTAMPs.

             INTERVAL DAY TO SECOND:Record a period in days and seconds between two DATEs or TIMESTAMPs.

• Large object data:

             CLOB: Stored in the database character set, unlimited size.

             NCLOB: Stored in the alternative national language character set.

             BLOB:

             BFILE: A locator pointing to a file stored on OS.

             LONG - CLOB

             LONG RAW - BLOB

• RAW and ROWID data:

             RAW:

             ROWID:

二. Create a Table

1. Creating Tables with Column Specification

CREATE TABLE [schema.]table [ORGANIZATION HEAP]

(column datatype [DEFAULT expression]

[,column datatype [DEFAULT expression]);

2. Creating Tables from Subqueries

CREATE TABLE [schema.]table AS subquery;

3. Altering Table Definition after Creation

DDL commands with a build-in COMMIT.

• Adding columns

             ALTER TABLE tablename ADD (column datatype);

• Modifying columns

             ALTER TABLE tablename MODIFY (column datatype [DEFAULTexpression]);

• Dropping columns

             ALTER TABLE tablename DROP COLUMN column;

• Marking columns as unused

             ALTER TABLE tablename SET UNUSED COLUMN column;

             ALTER TABLE tablename SET UNUSED COLUMNS; -- will drop all the unused columns in one pass through the table.

• Renaming columns

             ALTER TABLE tablename RENAME COLUMN columnold TOcolumnnew;

• Marking the table as read-only

             ALTER TABLE tablename READ ONLY;

4. Dropping and Truncating Tables

• Difference: DROP TABLE deletes the table and its sturecture.

                     TRUNCATE TABLE empties it, but leaves its structure for future data.

• DROP TABLE [schema.]tablename;
  

             TRUNCATE TABLE [schema.]tablename;

5. Create and Use Temporary Tables

CREATE GLOBAL TEMPORARY TABLE tablename

(column datatype[,column datatype])

[ON COMMIT {DELETE | PRESERVER} ROWS];

• The data in temporary table will be private to each session, different users insert into there own copy, and will never see each other's rows.
• Private to session, all SQL will be faster than permanent tables:

             Exist only on PGAs, no disk activity or even database buffer cache activity involved.

             DML against temporary tables dose not generate redo.

三. Index

1. Why use index

• Enforce primary keys and unique constraints, improve performance for data retrieval but reduce performance for DML operations.
  
• An index gives (near) immediate access to key values:
• Automatically create an index when:
  

                       primary key constraint is defined    

                       unique constraint id defined --- can be left null

• WHERE, ORDER BY, GROUP BY, UNION, jioned table

2. Types of Index

• B*Tree Indexes
  
• bitmap Indexes

3. Index Type Options

                  |--- Unique or nonunique
                  |--- Reverse key
                  |--- Compressed
B*Tree ---|--- Composite -----------------------|
                  |--- Function based -----------------|-- bitmap

                  |--- Ascending or descending ---|

4. Creating and Using Indexes

CREATE [UNIQUE | BITMAP] INDEX [schema.]indexname

ON [schema.]tablename ( column[, column...]);

四. Constraints

五. Views

1. Why Use Views

• Enforce Security: Users should only see certain rows or columns of a table.
  
• Simplify User SQL: Hard work is done by code defines the view.
  
• Prevent Errors
• Make Data Comprehensible
• Performance

2. Simple and Complex Views

• Simple View : Draws data from one detail table, uses no functions, and does no aggregation.

• Complex View : Can join detail tables, use functions, and perform aggregations.

                                          Cannot execute INSERT, UPDATE, DELETE commands against a complex view

3. Create view, alter view and drop view

CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW
[schema.]viewname [(alias [,alias]…)]
AS subquery
[WITH CHECK OPTION [CONSTRAINT constraintname]]
[WITH READ ONLY [CONSTRAINT constraintname]] ;

DROP VIEW [schema.]viewname ;

六. Synonyms

CREATE [PUBLIC] SYNONYM synonym FOR object ;

DROP [PUBLIC] SYNONYM synonym ;

ALTER SYNONYM synonym COMPILE;

七. Sequences

CREATE SEQUENCE [schema.]sequencename
[INCREMENT BY number]
[START WITH number]
[MAXVALUE number | NOMAXVALUE]
[MINVALUE number | NOMINVALUE]
[CYCLE | NOCYCLE]
[CACHE number | NOCACHE]
[ORDER | NOORDER] ;

ALTER SEQUENCE sequencename
[INCREMENT BY number]
[START WITH number]
[MAXVALUE number | NOMAXVALUE]
[MINVALUE number | NOMINVALUE]
[CYCLE | NOCYCLE]
[CACHE number | NOCACHE]
[ORDER | NOORDER] ;

sequencename.nextval

sequencename.currval