oracle sql and dba tutorial logo

Tutorial for Oracle DBA


Oracle DBA Interview Questions

Most asked Oracle DBA Interview Questions.

60 Technical Questions

42 Backup & Recovery Questions

Unix For Oracle DBA 20 Questions


 

Oracle SQL Tutorial Contents

Introduction to Databases

CODD'S Rules

Datatypes and Create Table

Oracle SELECT Statement

Formatting in SQL*Plus

UNION, INTERSECT, MINUS Operators and Sorting Query Result

 

Oracle ALTER TABLE ADD COLUMN

Oracle ALTER TABLE MODIFY COLUMN

Oracle SQL Functions

Oracle NVL() Function with Examples

Oracle SUBSTR() function with Examples

Oracle TO_DATE() with complete examples

Oracle DECODE function

Oracle INSTR() function with examples

Oracle TO_CHAR() function

Oracle TO_TIMESTAMP

Number Functions (Math Functions)

Character Functions

Miscellaneous Functions

Aggregate Functions

Date and Time Functions

Oracle Join Queries

GROUP BY Queries, SUB Queries

CUBE, ROLLUP Functions

Oracle DML (INSERT, UPDATE, DELETE...)

Oracle DDL (CREATE, ALTER, DROP...)

COMMIT, ROLLBACK,SAVEPOINT

Data Control Language (GRANT, REVOKE)

 

 

Integrity Constraints (PRIMARY KEY, NOT NULL...)

DEFAULT Values

Dropping Constraints

Disabling and Enabling

Differing Constraints Check

View Info about Constraints

Working with Dates

Oracle Views

Oracle Sequences

Oracle Synonyms

Indexes and Clusters

Table Partitioning

Altering Partition Tables

Dropping Partitions

Merging Partitions

Splitting Partitions

Coalescing Partitions

Oracle Objects and Object Types

 

 

 

 

 

 

 

 

 

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