Oracle DBA Interview Questions and Answers for Freshers - 3

Question: 11

What is an index?

Index is a database object, which is used to improve query performance. An index creates an entry for each value that appears in the indexed columns.

There are following two types of indexes:

Simple – Refers to an index which is based on a single column.

Composite – refers to an index which is based on a combination of columns.

Question: 12

On which columns you should create indexes?

You should create indexes on columns which have high number of distinct values and are expected to be used in the Where condition.

Question: 13

How can you rebuild an index?

An index can be rebuilt by using the ALTER INDEX <index_name> REBUILD; statement.

Question: 14

What is the difference between range partitioning and hash partitioning?

In case of range partitioning, the table is partitioned based on the range of values while in case of hash partitioning, the partitions are based on hash key values.

Range partitioning is helpful when you are interested in a subset of data based on range value most of the time.

Hash partitioning is helpful when you are interested in full table scan or a specific ID value. Hash partitioning distributes data randomly, and it can be distributed on multiple disks, which are processed in parallel.

Question: 15

Which default database roles are created when you create a database?

Three roles are created by default when you create a database, namely connect, resource and dba.