1Z0-082 Managing Schemas and Database Objects 3

Question 1

Which constraint type enforces uniqueness of values in one or more columns but allows NULL values?

Accepted Answer

UNIQUE

Answer

A UNIQUE constraint ensures no two rows have the same non-null value(s) in the constrained column(s), but unlike PRIMARY KEY, it permits NULL values.

Question 2

What is an Index-Organized Table (IOT) in Oracle?

Accepted Answer

A table whose rows are stored in primary key order within a B-tree index structure

Answer

An IOT stores table data in a B-tree index ordered by the primary key, eliminating a separate heap table and improving access performance for primary key lookups.

Question 3

Which data dictionary view lists all indexes owned by the current user?

Accepted Answer

USER_INDEXES

Answer

USER_INDEXES shows all indexes owned by the currently connected user, providing details such as index type, uniqueness, and associated table.

Question 4

What is the primary purpose of the DBMS_STATS package in Oracle?

Accepted Answer

To collect optimizer statistics on tables, indexes, and schemas

Answer

DBMS_STATS gathers, manages, and exports optimizer statistics that the Cost-Based Optimizer uses to generate efficient execution plans for SQL statements.

Question 5

Which index type is most appropriate for a column with very few distinct values, such as a gender or status flag?

Accepted Answer

Bitmap index

Answer

Bitmap indexes are ideal for low-cardinality columns because they store a bit for each possible value per row, enabling efficient AND/OR operations in analytical queries.

1Z0-082 - Oracle Database Administration I Practice Test

1Z0-082 - Oracle Database Administration I Practice Test

1Z0-082 Managing Schemas and Database Objects 3