一. 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