Oracle Certification - SQL Fundamentals I - Schema Objects

Views

A view is a stored SQL statement, presented to the user as a table that can be queried like any other. The syntax to create a view is as follows:

• OR REPLACE if the view already exists it will be dropped before the statement is executed
• FORCE / NOFORCE FORCE creates the view even if the detail table(s) do not exist (NOFORCE is the default which raises an error)
• WITH CHECK OPTION forces DML to conform to the view's defining SQL
• WITH READ ONLY prevents and DML on the view

Note that views and tables share the same namespace, so a table and view cannot share the same name.

Note: If a view is created with SELECT * the column names are actually expanded automatically by Oracle and stored with the definition of the view. This means that if you add a column to a table referenced by a view created in this way, the only way to update the view with the new column is to drop and re-created the view.

Security

Views can be used to enforce security, by restricting a user's access to data.

Simplify Data

Views can be used to simplify data retreival:

Simple vs Complex Views

There are two types of view, simple views which can have DML statements executed against them, and complex views which cannot. They can be differentiated as follows:

• Simple View A simple view access one table only, does not incorporate functions, and does no aggregation
• Complex View A complex view can join tables, use functions and aggregate data

As a general rule simple views can have INSERT, UPDATE and DELETE statements executed against them whereas complex views cannot. Be aware that there are execeptions to this.

Private and Public Synonyms

Synonyms are alternative names for objects. They provide a 'shortcut' to an object which may be in another schema, or another database even. The syntax is as follows:

A private schema object is a schema object, whereas public synonyms are not and can be accessed by any user that has been granted access. Oracle will always first look for a private synonym in the current schema, followed by public synonyms.

To drop a synonym the following syntax is used:

If they underlying objects that a synonym points to it can be refreshed with the following statment:

Sequences

Sequences are used to generate unique integer values for keys in the database. Only one session can read the next value at once so the values are guaranteed to be unique. Sequences are independant of row locking, commits and rollbacks are thus significantly faster than selecting a value from a table.

• INCREMENT BY increment between values, default +1 (can be negative for a descending sequence)
• START WITH the first value to be issued, defaults to 1 (-1 for descending sequences)
• MAXVALUE maximum value before an error is returned, default is NOMAXVALUE for ascending sequences, and -1 for descending sequences
• MINVALUE the lowest number in a descending sequence
• CYCLE the default behavoir at MAXVALUE / MINVALUE is to return an error, this allows the sequence to cycle round
• CACHE for performance Oracle caches the next X values, defaults to 20
• ORDER for a clustered database forces the clusters to co-ordinate incrementing the sequence

The pseudo columns NEXTVAL and CURRVAL are used to access to the sequence. Note that the CURRVAL returns the last selected value of the sequence for the current session, and may not be the last value issued. Also CURRVAL can only be selected once NEXTVAL has been accessed.

The syntax to adjust a sequence is as follows:

This does not allow the current value of the sequence to be adjusted however, and the only way to achieve this is to drop and recreate the sequence.

Indexes

Indexes enforce constraints, and enchance database performance. An index is a sorted list of key values, along with a pointer to the relevant row in the table.

Indexes improve perfomance, for example:

• Keys
  a primary key must be unique, for Oracle to enforce this constaint it must check that any new values do not already exist.Without an index this would require the entire table to be scanned for duplicate values every time a value was inserted. An index makes this process much faster, and avoids the need to scan the entire table
• WHERE
  Oracle must decide which rows or a table will be selected or modified, without an index a full table scan is necessary
• Sorting
  any statement that requires Oracle to sort the data (ORDER BY, GROUP BY, UNION)
• Joining
  there are various ways in which Oracle can join two tables, and having indexes on the columns improves this significantly

Note: Oracle will automatically determine which method is most efficient using statistics that it holds on the database objects involved

Types of Index

For the exam two types of index are of interest: B*Tree and Bitmap indexes. As a general rule of thumb indexes improve the performance of data retrieval, but hinder the performance of DML statments (as the index must be maintained).

B*Tree

The B*Tree (B stands for balanced) is a tree structure, the root node will point to many nodes at the second level, which can point to many nodes at the third level and so on. For a WHERE clause that has an equality predicate, Oracle navigates the tree to the leaf node containing the value, then uses the pointer to find the first row. The pointer used is the rowid which is Oracle's proprietary column that uniquely identifies every row in a table. Every row in every table in the database will have a unique rowid.

B*Tree indexes are very efficient when only a small portion of the table will be addressed, however when that approaches 2-4 percent of the rows a full table scan will be faster.

B*Tree indexes are used when:

• the cardinality (distinct rows) are high
• and the number of rows are high
• and the column is used in WHERE or JOIN clauses

Bitmap Indexes

Bitmap indexes store the rowids for each key in a value bitmap. So for example a bitmap of the DEPARTMENT_NAME column in the DEPARTMENTS table might be as follows:

Accounting     1001010001010111000
Administration 0010101010000000111
...

Each one or zero represents a row in the table, a one indicates a match for the key, and a zero a none match. When Oracle tests for multiple conditions the bitmaps for each key can simply be combined using boolean algebra.

Bitmap indexes are used when:

• the cardinality (distinct rows) is low
• and the number of rows are high
• and the column is used in Boolean operations (AND / OR / NOT)

The syntax for creating an index is:

Note: a unique / primary key constraint can be enforced by a nonunique index (that just happens to be unique).

Indexes are used transparently by the database, if one exists and Oracle considers it's use to be efficient it will be used automatically. This behavoir can be overridden by using hints.

There is an ALTER INDEX command, but it cannot alter the columns or whether the index is unique or not. To change the index in this way it must be dropped, and then recreated.

There's a lot to learn about table creation and manipulation, but it's all necessary for the exam.

To be continued...