Test Your Oracle Index Types Knowledge with This Quiz

Progress

True / False

In ORACLE, a B-tree index is the default type of index created.

True False

The B-tree index is the default and most commonly used type of index in ORACLE, providing efficient access for a wide variety of queries.

True / False

In ORACLE, a unique index ensures that no two rows have the same value in the indexed column(s).

True False

A unique index enforces the uniqueness of the values in the indexed columns, preventing duplicate entries.

True / False

In ORACLE, indexes can only be created on single columns.

True False

ORACLE allows the creation of composite indexes, which can index multiple columns in a single index.

True / False

In ORACLE, bitmap indexes are suitable for high cardinality columns.

True False

Bitmap indexes are suitable for columns with low cardinality, where the number of distinct values is relatively small.

True / False

In ORACLE, a function-based index can be created on an expression or a function.

True False

Function-based indexes allow indexing based on expressions or functions applied to one or more columns, enhancing performance for queries using those expressions.

True / False

In ORACLE, global indexes are partitioned indexes that span all partitions of a table.

True False

Global indexes are not tied to any specific partition and span all the partitions of a partitioned table.

True / False

In ORACLE, a reverse key index is used to evenly distribute insertions across all leaf blocks.

True False

A reverse key index reverses the bytes of the indexed columns, helping to distribute insertions more evenly and reduce contention.

True / False

In ORACLE, domain indexes are user-defined indexes specific to a particular application.

True False

Domain indexes are custom indexes created for specialized applications and data types, allowing indexing of complex data structures.

True / False

In ORACLE, a cluster index is used to enforce unique constraints on cluster keys.

True False

A cluster index is created on a cluster and is used to physically store rows of different tables that are related by common column values in the same data blocks.

Q10

True / False

In ORACLE, index-organized tables (IOTs) store the entire table data in the index structure.

True False

Index-organized tables store their data in a B-tree index structure, which allows for fast access to both the key and the non-key columns, making the index and table storage more efficient.

Q11

Multiple Choice

Identify the SQL statements that correctly create a B-tree index on a column in an Oracle table.

SQL Code

CREATE TABLE employees (
 employee_id NUMBER PRIMARY KEY,
 employee_name VARCHAR2(100),
 department_id NUMBER
);

CREATE INDEX idx_employee_name ON employees (employee_name); CREATE INDEX idx_employee_name_btree ON employees (employee_name) INDEXTYPE IS 'BTREE'; CREATE INDEX idx_employee_name ON employees (employee_name) INDEXTYPE IS 'BTREE';CREATE INDEX idx_employee_name ON employees (employee_id);

A B-tree index is the default index type in Oracle and is well-suited for columns with a wide range of values. The correct SQL statements will create a B-tree index using the CREATE INDEX command.

Q12

Multiple Choice

Select the SQL statements that correctly create a unique index on a combination of columns.

CREATE UNIQUE INDEX idx_dept_emp_name ON employees (department_id, employee_name); CREATE INDEX idx_dept_emp_name ON employees (department_id, employee_name) UNIQUE; CREATE UNIQUE INDEX idx_dept_emp_name ON employees (employee_name, department_id);ALTER TABLE employees ADD CONSTRAINT uq_dept_emp_name UNIQUE (department_id, employee_name);

A unique index ensures that the combination of values in the indexed columns is unique across the table. The correct SQL statements will use the UNIQUE keyword in the CREATE INDEX command.

Q13

Multiple Choice

Determine the SQL statements that demonstrate the creation of a bitmap index in Oracle.

CREATE BITMAP INDEX idx_dept_id ON employees (department_id); CREATE INDEX idx_dept_id ON employees (department_id) BITMAP; CREATE INDEX idx_dept_id_bitmap ON employees (department_id) INDEXTYPE IS 'BITMAP';CREATE BITMAP INDEX idx_dept_id ON employees (employee_name);

Bitmap indexes are suitable for columns with a small number of distinct values. The correct SQL statements will include the BITMAP keyword in the CREATE INDEX command.

Q14

Multiple Choice

Identify the SQL statements that correctly create a function-based index in Oracle.

CREATE INDEX idx_lower_emp_name ON employees (LOWER(employee_name)); CREATE INDEX idx_emp_name_lower ON employees (LOWER(employee_name)) INDEXTYPE IS 'FUNCTION-BASED'; CREATE INDEX idx_emp_name_lower ON employees (employee_name);CREATE INDEX idx_lower_emp_name ON employees (LOWER(employee_id));

Function-based indexes allow indexing on expressions and can significantly improve performance for queries involving functions. The correct SQL statements will use the expression in the CREATE INDEX command.

Q15

Multiple Choice

Choose the SQL statements that correctly demonstrate the creation of a reverse key index in Oracle.

CREATE INDEX idx_account_number ON accounts (account_number) REVERSE; CREATE REVERSE INDEX idx_account_number ON accounts (account_number); CREATE INDEX idx_account_number_reverse ON accounts (account_number) REVERSE;CREATE INDEX idx_account_number ON accounts (REVERSE(account_number));

Reverse key indexes are useful for reducing contention in index blocks by reversing the bytes of the indexed column values. The correct SQL statements will include the REVERSE keyword in the CREATE INDEX command.

Q16

Multiple Choice

Which SQL statements demonstrate the creation of an index-organized table (IOT) in Oracle?

CREATE TABLE orders ( order_id NUMBER PRIMARY KEY, order_date DATE, customer_id NUMBER ) ORGANIZATION INDEX; CREATE TABLE orders ( order_id NUMBER, order_date DATE, customer_id NUMBER, PRIMARY KEY (order_id) ) ORGANIZATION INDEX; CREATE INDEX idx_orders ON orders (order_id) ORGANIZATION INDEX;CREATE TABLE orders ( order_id NUMBER PRIMARY KEY, order_date DATE, customer_id NUMBER ) INDEXTYPE IS 'IOT';

Index-organized tables (IOTs) store the entire table data within a B-tree index structure, optimizing access to rows based on the primary key. The correct SQL statements will use the ORGANIZATION INDEX clause.

Q17

Multiple Choice

Determine the SQL statements that correctly create a clustered index in Oracle.

CREATE CLUSTERED INDEX idx_department_id ON employees (department_id); ALTER TABLE employees ORGANIZE BY CLUSTER (department_id); CREATE TABLE employees_clustered ( employee_id NUMBER PRIMARY KEY, employee_name VARCHAR2(100), department_id NUMBER ) CLUSTERING BY department_id;Partitioning the table by 'department_id' can achieve similar clustering behavior.

Oracle does not natively support clustered indexes as in some other RDBMS, but similar behavior can be achieved using partitioned tables or by careful use of indexing and physical ordering. The correct SQL statements will reflect this approach.

Q18

Multiple Choice

Identify the SQL statements that correctly create a global partitioned index in Oracle.

CREATE INDEX idx_order_date ON orders (order_date) GLOBAL PARTITION BY RANGE (order_date) ( PARTITION p1 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD')), PARTITION p2 VALUES LESS THAN (TO_DATE('2025-01-01', 'YYYY-MM-DD')) ); CREATE GLOBAL INDEX idx_order_date ON orders (order_date) PARTITION BY RANGE (order_date); CREATE INDEX idx_order_date ON orders (order_date) GLOBAL PARTITION BY RANGE (order_date);CREATE INDEX idx_order_date ON orders (order_date) PARTITION BY RANGE (order_date);

Global partitioned indexes are indexes where the index partitions do not align with the table's partitioning scheme. The correct SQL statements will include the GLOBAL PARTITION BY clause.

Q19

Multiple Choice

Choose the SQL statements that demonstrate the creation of a local partitioned index in Oracle.

CREATE INDEX idx_sale_id ON sales (sale_id) LOCAL; CREATE LOCAL INDEX idx_sale_id ON sales (sale_id) PARTITION BY HASH (sale_id); CREATE INDEX idx_sale_id_local ON sales (sale_id) LOCAL;CREATE LOCAL INDEX idx_sale_id ON sales (sale_id);

Local partitioned indexes are aligned with the table's partitioning scheme, meaning each index partition corresponds to a table partition. The correct SQL statements will use the LOCAL keyword in the CREATE INDEX command.

Q20

Multiple Choice

Select the SQL statements that demonstrate the creation of a domain index in Oracle.

CREATE INDEX idx_document_content ON documents (document_content) INDEXTYPE IS CTXSYS.CONTEXT; CREATE DOMAIN INDEX idx_document_content ON documents (document_content) INDEXTYPE IS CTXSYS.CONTEXT; CREATE INDEX idx_document_content ON documents (document_content);CREATE INDEX idx_document_content_domain ON documents (document_content) INDEXTYPE IS 'DOMAIN';

Domain indexes allow the creation of custom indexes tailored to specific data types or requirements. The correct SQL statements will include the INDEXTYPE IS clause to specify the domain index type.

Q21

Single Choice

Which type of index is best suited for optimizing queries that filter by a column with low cardinality?

SQL Code

SELECT employee_id, department_id
FROM employees
WHERE department_id = 30;

CREATE INDEX idx_dept ON employees (department_id); CREATE BITMAP INDEX idx_bmp_dept ON employees (department_id); CREATE UNIQUE INDEX idx_unique_dept ON employees (department_id);CREATE REVERSE INDEX idx_reverse_dept ON employees (department_id);

Bitmap indexes are ideal for columns with low cardinality as they efficiently store and process repeating values.

Q22

Single Choice

Which index type would be most efficient for a column that is frequently updated?

SQL Code

SELECT order_id, status
FROM orders
WHERE status = 'Shipped';

CREATE BITMAP INDEX idx_status_bmp ON orders (status); CREATE UNIQUE INDEX idx_unique_status ON orders (status); CREATE REVERSE INDEX idx_reverse_status ON orders (status);CREATE INDEX idx_status ON orders (status);

B-tree indexes are most efficient for columns that are frequently updated as they maintain balanced performance for DML operations.

Q23

Single Choice

What type of index should be used to improve query performance for a case-insensitive search?

SQL Code

SELECT user_id, email
FROM users
WHERE LOWER(email) = 'john.doe@example.com';

CREATE INDEX idx_email ON users (email); CREATE BITMAP INDEX idx_email_bmp ON users (email); CREATE FUNCTION-BASED INDEX idx_fn_email ON users (LOWER(email));CREATE UNIQUE INDEX idx_unique_email ON users (LOWER(email));

Function-based indexes are suitable for queries where functions are applied to columns in the WHERE clause.

Q24

Single Choice

Which index type is optimal for speeding up queries that join tables on non-unique columns?

SQL Code

SELECT e.employee_id, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

CREATE UNIQUE INDEX idx_emp_dept ON employees (department_id); CREATE BITMAP INDEX idx_bmp_emp_dept ON employees (department_id); CREATE INDEX idx_emp_dept ON employees (department_id);CREATE REVERSE INDEX idx_reverse_emp_dept ON employees (department_id);

A non-unique index is effective for optimizing join operations on non-primary key columns.

Q25

Single Choice

What type of index is most effective for queries that perform range searches on a date column?

SQL Code

SELECT order_id, order_date
FROM orders
WHERE order_date BETWEEN '01-JAN-2023' AND '31-JAN-2023';

CREATE BITMAP INDEX idx_order_date_bmp ON orders (order_date); CREATE UNIQUE INDEX idx_unique_order_date ON orders (order_date); CREATE REVERSE INDEX idx_reverse_order_date ON orders (order_date);CREATE INDEX idx_order_date ON orders (order_date);

B-tree indexes are most effective for range queries as they maintain a balanced tree structure that efficiently processes range searches.

Q26

Single Choice

Which index type is best suited for improving performance of queries that filter on a column with high cardinality?

SQL Code

SELECT customer_id, email
FROM customers
WHERE email LIKE 'john%';

CREATE BITMAP INDEX idx_email_bmp ON customers (email); CREATE INDEX idx_email ON customers (email); CREATE FUNCTION-BASED INDEX idx_fn_email ON customers (email);CREATE REVERSE INDEX idx_reverse_email ON customers (email);

A B-tree index is well-suited for columns with high cardinality, as it efficiently handles a wide range of distinct values.

Q27

Single Choice

What type of index would you create to optimize a query that performs a wildcard search?

SQL Code

SELECT product_id, product_name
FROM products
WHERE product_name LIKE '%Phone%';

CREATE FUNCTION-BASED INDEX idx_fn_product_name ON products (LOWER(product_name)); CREATE INDEX idx_product_name ON products (product_name); CREATE BITMAP INDEX idx_bmp_product_name ON products (product_name);CREATE UNIQUE INDEX idx_unique_product_name ON products (product_name);

A function-based index is recommended for optimizing queries that involve wildcard searches, especially when the LIKE operator is used.

Q28

Single Choice

How would you create an index on a column with a high number of distinct values for filtering purposes?

SQL Code

SELECT transaction_id, account_number
FROM transactions
WHERE account_number = '123456789';

CREATE BITMAP INDEX idx_account_number_bmp ON transactions (account_number); CREATE INDEX idx_account_number ON transactions (account_number); CREATE UNIQUE INDEX idx_unique_account_number ON transactions (account_number);CREATE REVERSE INDEX idx_reverse_account_number ON transactions (account_number);

A B-tree index is most suitable for columns with high cardinality, offering efficient search performance on distinct values.

Q29

Single Choice

Which index type would be appropriate for a column that is frequently queried with the IS NULL condition?

SQL Code

SELECT employee_id, bonus
FROM employees
WHERE bonus IS NULL;

CREATE BITMAP INDEX idx_bonus_bmp ON employees (bonus); CREATE INDEX idx_bonus ON employees (bonus); CREATE UNIQUE INDEX idx_unique_bonus ON employees (bonus);CREATE REVERSE INDEX idx_reverse_bonus ON employees (bonus);

A B-tree index is suitable for optimizing queries where the IS NULL condition is frequently used.

Q30

Single Choice

What index type should be used to enhance performance for a query that filters on a text column with a specific pattern?

SQL Code

SELECT user_id, username
FROM users
WHERE username LIKE 'john%';

CREATE FUNCTION-BASED INDEX idx_fn_username ON users (LOWER(username)); CREATE INDEX idx_username ON users (username); CREATE BITMAP INDEX idx_bmp_username ON users (username);CREATE UNIQUE INDEX idx_unique_username ON users (username);

A function-based index on the column with the specific pattern is optimal for enhancing query performance.

Oracle Database Quiz Questions