02-06-2014, 04:54 PM
Oracle For Beginners -INDEXING, CLUSTERING, SEQUENCE AND PSEUDO COLUMNS
1370274444-ch12.pdf (Size: 209.54 KB / Downloads: 111)
What is an index
Why to use an index
Creating an index
When Oracle does not use index
Clustering
Sequence
Pseudo column
What is an Index?
I believe, the best way of understanding an index in Oracle (or any database system) is by
comparing it with the index that we find at the end of a textbook. For instance, if you want to
read about indexing in an Oracle textbook, you will use index of the book to locate the topic
indexing. Once the topic is found in index then you take the page number of that topic from
index. Then you go to the page with that page number and start reading about indexing.
The concept of indexing in Oracle is same as the concept of book index. First let us look at the
features of an index of a textbook.
It is at the end of the textbox so that you need not search for the index in the first place.
It contains all topics of the book in the ascending (alphabetical) order of the topics.
After the topic the page number(s) where the topic is found in the book is listed.
Index does increase the size of the book by a few pages.
We use index only when we need it. In other words, we use index when we feel it is going
to help locating the topic quickly.
All the characteristics of an index in a textbook will be found in an index of Oracle. The
following are the characteristics of an index in Oracle.
Index is used to search for required rows quickly.
Index occupies extra space. Index is stored separately from table.
Using index to improving performance
Just like how you can quickly locate a particular topic in the book by using index at the end of
the book, Oracle uses index to quickly locate the row with the given value in the indexed
column. Indexed column is the one on which index is created.
For example if you want to search for a particular student by name then Oracle does the
following without and with index.
Without index, Oracle will start looking for the given name at the first row of the table and
continues until it finds the name or until end of the table is reached. This could be very
time consuming process especially for tables with many rows.
With index, Oracle will use index to search for the given name. Since index is stored in the
form of binary tree, locating the name in the index is going to be very fast. Then by using
the ROWID obtained from index entry, Oracle will take the row from the table.
When Oracle does not use index?
The best part of Oracle index is; it is completely automatic. That means, you never have to
explicitly refer to an index. Oracle decides whether to use an index or not depending upon the
query.
Oracle can understand whether using an index will improve the performance in the given
query. If Oracle thinks using an index will improve performance, it will use the index otherwise
it will ignore the index.
Let us assume we have an index on NAME column of STUDETNS table. Then the
following query will use the index as we are trying to get information about a student
based on the name.
Creating function-based index
Prior to Oracle8i, it is not possible to create an index with an expression as the index column.
Index column must be column of the table. If any function is used with indexed column in the
query then Oracle does not use index. For example, if we created an index on NAME column
of STUDENTS table as follows:
create index students_name_idx
on students (name);
Then the above index cannot be used for the following query as indexed column is used with
UPPER function.
select * from students
where upper(name) = 'RICHARD MARX';
It is also not possible to create any index based on an expression.
Oracle8i allows indexes to be created on the result of the expression. For example, the
following command creates an index on the result of UPPER function.
Dropping an index
You can drop an index using DROP INDEX command. It removes the named index. Removing
an index will effect the performance of existing applications but not the functionality in any
way.
Using and not using an index is transparent to users. Oracle decides whether to use or not on
its own. However, it is possible for users to control the usage of index to certain extent using
hints, which are directive to Oracle regarding how to execute a command. But hints are too
heavy in a book meant for beginners.
Clustering
Clustering is a method of storing tables that are often used together (in joining) in one area of
the disk. As tables that are related are stored in one area of the disk, performance of joining
will improve.
In order to place tables in a cluster, tables have to have a common column. For example the
following steps will create a cluster and place COURSES and BATCHES tables in the cluster.
A cluster is created with a name and a key. The key is the column, which must be present in
each table that is placed in the cluster. The key is also called as cluster key.
Creating cluster index
After the cluster is created and before any rows can be inserted into tables in the cluster, an
index on the cluster must be created.
CREATE INDEX command is used to create an index on the cluster. Unless an index is created
on the cluster no data manipulation can be done on the tables that are placed in the cluster.
Placing tables into a cluster
Once a cluster is created, it may be used to store tables that are related. Each table loaded
into cluster, must have a column that matches with cluster key.
A table must be placed in to cluster at the time of creation. CLUSTER option of CREATE TABLE
command specifies the cluster in to which the table must be placed. It also specifies the name
of the column in the table that matches the cluster key of the cluster.
The following commands create COURSES and BATCHES tables and place them into cluster.
Storage of clustered tables
When two tables are placed in a cluster, they are stored together on the disk making joining
these tables faster. Apart from that storing table in a cluster will also reduce the space
requirement. This is because of cluster storing common column of the clustered tables only for
once. In the above example, each unique CCODE is stored only for once. That means for
course ORA though there are multiple batches, the value ORA is stored only for once in the
cluster.
Sequence
Sequence is an object in Oracle database, which is used by multiple users to generate unique
numbers. Sequence is typically used to generate primary keys like account number,
employee number etc., where uniqueness and sequence matter.
In order to use a sequence, first it is to be created using CREATE SEQUENCE command. Then
pseudo columns NEXTVAL and CURRVAL are used to retrieve unique values from sequence.
The following is the syntax of CREATE SEQUENCE command .