4.2 Managing Views

Views are powerful tool to present data for different group of users. A view is a logical representation of based tables. A base table could be a table or a view. View can be used as table in most cases. The update, deletion and insert into statement can apply to view and the correspond base table will be affected by the operations.

Use CREATE VIEW statement to create view.

You can create view with errors. You can also create view without required privilege. For instance if a view refers a non-existing table or field, a view can still be created with the following syntax:

CREATE FORCE VIEW v_name AS .select-statement ;

You replace or create view in one statement:

CREATE OR REPLACE VIEW v_name AS select-statement ;

After a view is replaced, all PL/SQL units dependent on a replaced view become invalid.

4.2.1 Restrictions on DML for Views

· If a view is defined by a query that contains SET or DISTINCT operators, a group by clause, or a group function, rows cannot be inserted into, updated in or deleted from the base tables using the view.

· If a view is defined with the WITH CHECK OPTION, a row cannot be inserted into, or update in, the base table if the view cannot select the row from the base table.

· If a NOT NULL column without DEFAULT clause is omitted from the view, an row cannot be inserted into the base table using the view.

· If a view is created with expression, rows cannot be inserted into or update in the base table using the view.

4.2.2 Modifiable Join View

A view based on more than one base table is called a join view. Rows in base tables can be updated through view if the following restrictions on view hold:

· No DISTINCT operator

· No aggregate functions: AVG, COUNT, GLB, MAX, MIN, STDDEV, SUM, VARIANCE

· No SET operators: UNION, UNION ALL, INTESERT, MINUS

· No GROUP BY or HAVING clause.

· No START WITH or CONNECT BY clauses.

· No ROWNUM pseudocolumn

4.2.3 Rules for DML Statements on Join Views

Key-preserved Table: A table in a join view is a key-preserved table if every key of the table can also be a key of result of the join. For example, select * from emp , dept where emp.dept_no = dept.dept_no; The department no is a key in dept and not a key in the join result, since there more than one employee in each department. However, emp is key-preserved table in the join view.

· Any UPDATE, INSERT or DELETE statement on a join view can modify only one underlying base table.

· In general the modifiable fields are the field in the key-preserved tables. And the fields from the non key-preserved tables cannot be updated

· You can delete from a join view provided there is on and only one key-preserved table in the join.

· If a view is created with CHECK option and view refers the same table twice is not deletable since the join has either no key-preserved table or has more than one key-preserved table.

· An insert statement cannot implicitly or explicitly refer to columns of a non-key-preserved table.

· If a view is defined with CHECK option clause, then INSERT cannot applied to the view.

Three Views for observing the join view. Is a field of a table or view updatable? Oracle creates three view to serve that purpose.

· USER_UPDATABLE_COLUMNS : Show all columns in all tables and views in the user’s schema.

· DBA_UPDATABLE_COLUMNS: Show all columns in all tables and views in the DBA schema that are modifiable.

· ALL_UPDATABLE_VIEW: Show all columns in all tables and views that are modifiable.

· View created with outer join is modifiable in some cases.

4.2 Managing Sequences

Sequences are used to generate unique sequential number as primary keys in tables. Numerical primary keys can also be generated by retrieving the maximum value and increasing it. However, the second method needs a lock on the table and every transactions that need a primary key have to wait.

A sequence can be created with CREATE SEQUENCE statement. Each sequence object has two pseudo columns called NEXTVAL and CURRVAL. The NEXTVAL will get the next unused sequence value and CURRVAL returns the last sequence value is generated (and used usually).

In a program, NEXTVAL has to be referred before CURRVAL can be referred in one session. Otherwise, seq_name.CURRVAL does not return any value.

Where Can Sequence Value be Used?

The sequence value can be used in the following places:

· VALUES clause of INSERT statements

· SELECT list of a SELECT statement

· SET clause of an UPDATE statement

The NEXTVAL and CURRVAL cannot be used in the following cases

· A subquery

· A view’s query or a snapshot’s query

· A SELECT statement with DISTINCT operator

· A SELECT statement with GROUP BY or ORDER BY clause.

· A SELECT statement combined with another SELECT with UNION, INSERSECT, OR MINUS set operator.

· The WHERE caluse of a SELECT statement

· DEAFAULT value of a SELECT statement

· The conidtion of a CHECK constraint

Sequence values of sequence objects can be stored in the cache. Accessing sequence values from cache is much faster than accessing sequence number from disk. The total number of sequence values of all sequences in your application is limited by SEQUENCE_CACHE_ENTRIES initialization parameter

Use DROP SEQUENCE to drop a sequence object.

4.3 Managing Synonyms

A synonym is an alias for a table, view, snapshot, sequence, procedure, function, or package.

· To create a synonym, use the following syntax

CREATE [PUBLIC] SYNONYM [schema.]synonym_name FOR

[schema.]objet_name [@dblink]

where the object can be table, view, sequence, stored procedure, function or package, snapshot, synonym.

· After a synonym is created, the synonym can be used in the way in which the underlying object is used.

· Use DROP SYNONYM to drop a synonym from schema.

4.4 Managing Indexes

Indexes are used to speed up retrieval of small number of rows from a table. Oracle puts no limits on the number of indexes you can created per table. For efficiency, you should create indexes after the data is loaded.

When a index is created, temporary tablespace is used to swap sorted data. When creating large index on the table with data the following steps are suggested:

· Create a new temporary tablespace using CREATE TABLESPACE command

· Use the TEMPORARY TABLESPACE optin of the ALTER USER command to make this your new temporary tablespace.

· Create index using CREATE INDEX command.

· Drop this tablespace using DROP TABLESPACE command. Then use ALTER USER command to reset your temporary tablespace to your original temporary tablespace.

4.4.1 Which Columns Should be Indexed

· Create an index if you want to retrieve less than 15% of the row in large table. The percentage varies greatly according to the relative speed of a table scan and how clustered the row data is about the index key. The faster the table scan, the lower the percentage, the more clustered the row data, the higher the percentage.

· Index columns used for joins to improve the performance on joins of multiple tables.

· Primary and unique keys are index automatically while you may want to create index on foreign keys.

· Small table don’t need indexes.

· Column with many different value are strong candidate for indexing.

· Column has few distinct values ( for example, sex)

· Column with many NULL value and you don’t search non-null values.

( select * from tmp where col IS NOT NULL is frequently used.)

· Order Index Columns for Performance: When most of queris select rows based on more than one columns, you may create a composite index (index with more than one column). When a composite index is created, the order or columns appear in index is important. Put the most frequently used (in WHERE clause) first. Usually, indexes speed retrieval on any query using leading position of the index.

4.4.2 Creating Indexes

Oracle automatically creates indexes for primary keys and columns with UNIQUE column constraint. The CREATE INDEX statement can be used to create unique or duplicate indexes on any field. The syntax of creating index is given below:

CREATE INDEX [schema.]index_name ON [schema.]table_name

( col_name [ASC|DEC] [,col_name [ASC|DEC]] )

INITTRANS int

MAXTRANS int

TABLESPACE tablespace_name

STORAGE storage_clause

PCTFREE int

NOSORT

See SQL Language Reference Manual for how to create cluster index and specification of the cluases in the CREATE INDEX statement.

· To be able to create index, you must own or have the index object privilege for the corresponding table. The schema that contains the index must also have a quota for the tablespace intended to contain the index or the UNLIMITED TABLESPACE system privilege. To create an index in another user’s schema, you must have the CREATE ANY INDEX system privilege.

· Use DROP INDEX idx_name to drop the index. When a table is drop, all indexes on that table will be automatically dropped. To drop index in other user’s schema, you must have DROP ANY INDEX system privilege.