1Z0-006 Database Objects and Constraints 3

Question 1

What is the primary purpose of an index in Oracle Database?

Accepted Answer

To speed up data retrieval operations on a table

Answer

An index improves query performance by allowing Oracle to quickly locate rows matching a search condition without performing a full table scan.

Question 2

Which type of index does Oracle automatically create when you define a PRIMARY KEY constraint?

Accepted Answer

Unique B-tree index

Answer

When a PRIMARY KEY constraint is defined, Oracle automatically creates a unique B-tree index on the primary key column(s) to enforce uniqueness and enable fast lookups.

Question 3

What is a composite index in Oracle Database?

Accepted Answer

An index that spans two or more columns of a table

Answer

A composite (concatenated) index is built on two or more columns, allowing Oracle to use it efficiently for queries that filter on those columns together.

Question 4

When should you consider dropping an index in Oracle Database?

Accepted Answer

When the index is rarely used by queries but causes significant overhead on DML operations

Answer

Indexes add overhead to INSERT, UPDATE, and DELETE operations because Oracle must keep them synchronized; an index that is rarely used by queries provides little benefit while slowing down data modifications.

Question 5

Which statement about indexes and DML operations in Oracle Database is TRUE?

Accepted Answer

Oracle automatically updates all indexes when DML operations modify indexed column values

Answer

Oracle transparently maintains all indexes on a table whenever DML (INSERT, UPDATE, DELETE) modifies the data, which is why having too many indexes can slow down write-intensive workloads.

(1Z0-006) Oracle Database Foundations Associate Practice Test

(1Z0-006) Oracle Database Foundations Associate Practice Test

1Z0-006 Database Objects and Constraints 3