oracle sql and dba tutorial logo

Oracle DBA

Learn Oracle 11g / 12c Database Admin step by step



Oracle SQL

Tutorial for Oracle SQL

 

Oracle DBA Interview Questions

Most asked Oracle DBA Interview Questions.

60 Technical Questions
42 Backup & Recovery Questions

Unix For Oracle DBA 20 Questions


Download Oracle 11g / 12 c Software

Links to Download Oracle 12c / 11g for Linux, Windows etc.



Managing Indexes and Clusters in Oracle

INDEXES

Use indexes to speed up queries. Indexes speeds up searching of information in tables. So create indexes on those columns which are frequently used in WHERE conditions. Indexes are helpful if the operations return only small portion of data i.e. less than 15% of data is retrieved from tables.

Follow these guidelines for creating indexes

Creating Indexes

To create an Index give the create index command. For example the following statement creates an index on empno column of emp table.

create index empno_ind on emp (empno);

If two columns are frequently used together in WHERE conditions then create a composite index on these columns. For example, suppose we use EMPNO and DEPTNO oftenly together in WHERE condition. Then create a composite index on these column as given below

create index empdept_ind on emp (empno,deptno);

The above index will be used whenever you use empno or deptno column together, or you just use empno column in WHERE condition. The above index will not be used if you use only deptno column alone

BITMAP INDEXES

Create Bitmap indexes on those columns which contains many repeated values and when tables are large. City column in EMP table is a good canditate for Bitmap index because it contain many repeated values.  To create a composite index give the following command.

create bitmap index city_ind on emp (city);

FUNCTION BASED Indexes

Function Based indexes are built on expressions rather  than on column values. For example if you frequently use the expression SAL+COMM  in WHERE conditions then create a Function base index on this expression like this

create index salcomm_ind on emp (sal+comm);

Now, whenever you use the expression SAL+COMM in where condition then oracle will use SALCOMM_IND index.

DROPPING INDEXES

To drop indexes use DROP INDEX statement. For example to drop SALCOMM_IND give the following statement

drop index salcomm_ind;

Listing Information about indexes

To see how many indexes are there in your schema and its information, give the following statement.

select * from user_indexes;

CLUSTERS

If you two are more tables are joined together on a single column and most of the time you issue join queries on them, then consider creating a cluster of these tables.

A cluster is a group tables that share the same data blocks i.e. all the tables are physically stored together.

For example EMP and DEPT table are joined on DEPTNO column. If you cluster them, Oracle physically stores all rows for each department from both the emp and dept tables in the same data blocks.

Therefore, less storage might be required to store related table and index data in a cluster than is necessary in non-clustered table format.

CREATING A CLUSTER

To create clustered tables. First, create a cluster and create index on it. Then create tables in it.

For example to create a cluster of EMP and DEPT tables in which the DEPTNO will be cluster key, first create the cluster by typing the following command.

create cluster emp_dept (deptno number(2));

Then create index on it.

create index on cluster emp_dept;

Now create table in the cluster like this

create table dept (deptno number(2),
                    name varchar2(20),
                    loc varchar2(20))
                    cluster emp_dept (deptno);

create table emp (empno number(5),
            name varchar2(20),
            sal number(10,2),
            deptno number(2)) cluster emp_dept (deptno)      

Dropping Clusters

To drop a cluster use DROP CLUSTER statement. For example to drop the emp_dept cluster give the following command.

drop cluster emp_dept;

This will drop the cluster, if the cluster is empty i.e. no tables are existing it it. If tables are there in the cluster first drop the tables and then drop the cluster. If you want to drop the cluster even when tables are there then give the following command.

drop cluster emp_dept including tables;

Listing Information about Clusters

To see how many clusters are there in your schema give the following statement.

select * from user_clusters;

To see which tables are part of a cluster. Give the following command.

select * from tab

TABLE_NAME    TYPE      CLUSTER_ID
----------    ----      -----------
EMP           TABLE         1
SALGRADE      TABLE        
CUSTOMER      TABLE        
DEPT          TABLE         1

In the above example notice the CLUSTER_ID column, for EMP and DEPT table the cluster_id is 1. That means these tables are in cluster whose cluster_id is 1. You can see the cluster_id’s name in USER_CLUSTERS table.

 

 


HomeContact Us

Data Loader

Data Loader is a simple yet powerful tool to
export and import Data between many common database formats


Forms Data Loader

Tool to load data into Oracle E-Business Suite R12 / Oracle Apps using Macros and Forms Record and Playback

Interface Computers Academy © 2007-2017 All Rights Reserved