Test Your Oracle Database Index Knowledge with This Quiz

Progress

True / False

In ORACLE, an index can improve the performance of SELECT queries.

True False

Indexes can significantly improve the performance of SELECT queries by allowing the database to quickly locate rows.

True / False

In ORACLE, an index is automatically created on the Primary Key of a table.

True False

When a Primary Key is defined on a table, ORACLE automatically creates a unique index on the Primary Key column(s).

True / False

In ORACLE, creating an index on a column automatically enforces uniqueness.

True False

Creating a standard index does not enforce uniqueness. To enforce uniqueness, a unique index must be created.

True / False

In ORACLE, composite indexes can be created on multiple columns.

True False

Composite indexes can be created on two or more columns to improve the performance of queries that filter on multiple columns.

True / False

In ORACLE, bitmap indexes are efficient for columns with low cardinality.

True False

Bitmap indexes are particularly efficient for columns with low cardinality, such as gender or status fields, where the number of distinct values is small.

True / False

In ORACLE, indexes are automatically used in every query that references the indexed columns.

True False

The ORACLE optimizer decides whether to use an index based on various factors, including the structure of the query and the distribution of data.

True / False

In ORACLE, an index can become fragmented and require rebuilding to maintain performance.

True False

Indexes can become fragmented over time, especially with frequent insertions, deletions, and updates, and may require rebuilding to maintain optimal performance.

True / False

In ORACLE, the use of indexes always results in faster query performance.

True False

While indexes generally improve query performance, there are cases where they can slow down DML operations (INSERT, UPDATE, DELETE) due to the overhead of maintaining the index.

True / False

In ORACLE, function-based indexes can be created to index expressions or functions.

True False

Function-based indexes can be created on expressions or functions, allowing queries that use these expressions to benefit from indexing.

Q10

True / False

In ORACLE, a reverse key index reverses the bytes of each column indexed to improve performance in certain scenarios.

True False

A reverse key index reverses the bytes of each column indexed to help evenly distribute insertions across all leaf blocks, which can improve performance for certain types of workloads, such as those with sequential key values.

Q11

Multiple Choice

Which SQL code snippet demonstrates creating a basic index on a single column in Oracle?

SQL Code

CREATE INDEX idx_employee_name
ON employees (last_name);

CREATE INDEX idx_employee_name ON employees (last_name); CREATE INDEX idx_employee_name ON employees (last_name); CREATE INDEX idx_employee_name ON employees (last_name);CREATE INDEX idx_employee_name ON employees (first_name);

Options A, B, and C correctly demonstrate creating a basic index on the `last_name` column of the `employees` table. Option D is incorrect because it attempts to create the index on a non-existing column.

Q12

Multiple Choice

Which SQL code snippet demonstrates creating a unique index on multiple columns in Oracle?

SQL Code

CREATE UNIQUE INDEX idx_unique_employee
ON employees (first_name, last_name);

CREATE UNIQUE INDEX idx_unique_employee ON employees (first_name, last_name); CREATE UNIQUE INDEX idx_unique_employee ON employees (first_name, last_name); CREATE UNIQUE INDEX idx_unique_employee ON employees (first_name, last_name);CREATE INDEX idx_employee_name ON employees (first_name, last_name);

Options A, B, and C correctly demonstrate creating a unique index on the `first_name` and `last_name` columns of the `employees` table. Option D is incorrect because it does not enforce uniqueness.

Q13

Multiple Choice

Which SQL code snippet demonstrates using an index in a SELECT query for faster retrieval?

SQL Code

SELECT employee_id, last_name
FROM employees
WHERE last_name = 'Smith';

SELECT employee_id, last_name FROM employees WHERE last_name = 'Smith'; SELECT employee_id, last_name FROM employees WHERE last_name = 'Smith'; SELECT employee_id, last_name FROM employees WHERE last_name = 'Smith';SELECT employee_id, last_name FROM employees WHERE first_name = 'John';

Options A, B, and C correctly demonstrate using an index on the `last_name` column in a SELECT query for faster data retrieval. Option D is incorrect because it does not include the indexed column in the WHERE clause.

Q14

Multiple Choice

Which SQL code snippet demonstrates creating a bitmap index in Oracle?

SQL Code

CREATE BITMAP INDEX idx_emp_dept
ON employees (department_id);

CREATE BITMAP INDEX idx_emp_dept ON employees (department_id); CREATE BITMAP INDEX idx_emp_dept ON employees (department_id); CREATE BITMAP INDEX idx_emp_dept ON employees (department_id);CREATE INDEX idx_emp_dept ON employees (department_id);

Options A, B, and C correctly demonstrate creating a bitmap index on the `department_id` column, which is useful in situations with low-cardinality columns. Option D is incorrect because it creates a regular index instead of a bitmap index.

Q15

Multiple Choice

Which SQL code snippet demonstrates dropping an index in Oracle?

SQL Code

DROP INDEX idx_employee_name;

DROP INDEX idx_employee_name; DROP INDEX idx_employee_name; DROP INDEX idx_employee_name;DROP TABLE idx_employee_name;

Options A, B, and C correctly demonstrate dropping an index named `idx_employee_name`. Option D is incorrect because it attempts to drop a table instead of an index.

Q16

Multiple Choice

Which SQL code snippet demonstrates creating a function-based index in Oracle?

SQL Code

CREATE INDEX idx_upper_last_name
ON employees (UPPER(last_name));

CREATE INDEX idx_upper_last_name ON employees (UPPER(last_name)); CREATE INDEX idx_upper_last_name ON employees (UPPER(last_name)); CREATE INDEX idx_upper_last_name ON employees (UPPER(last_name));CREATE INDEX idx_last_name ON employees (last_name);

Options A, B, and C correctly demonstrate creating a function-based index on the `last_name` column using the `UPPER` function. Option D is incorrect because it creates a standard index without using a function.

Q17

Multiple Choice

Which SQL code snippet demonstrates creating a reverse key index in Oracle?

SQL Code

CREATE INDEX idx_rev_emp_id
ON employees (employee_id) REVERSE;

CREATE INDEX idx_rev_emp_id ON employees (employee_id) REVERSE; CREATE INDEX idx_rev_emp_id ON employees (employee_id) REVERSE; CREATE INDEX idx_rev_emp_id ON employees (employee_id) REVERSE;CREATE INDEX idx_emp_id ON employees (employee_id);

Options A, B, and C correctly demonstrate creating a reverse key index on the `employee_id` column, which is useful for evenly distributing index values in large tables. Option D is incorrect because it creates a standard index instead of a reverse key index.

Q18

Multiple Choice

Which SQL code snippet demonstrates certification-level use of indexing strategies for query optimization in Oracle?

SQL Code

SELECT /*+ INDEX(emp idx_employee_name) */ employee_id, last_name
FROM employees emp
WHERE last_name = 'Smith';

SELECT /*+ INDEX(emp idx_employee_name) */ employee_id, last_name FROM employees emp WHERE last_name = 'Smith'; SELECT /*+ INDEX(emp idx_employee_name) */ employee_id, last_name FROM employees emp WHERE last_name = 'Smith'; SELECT /*+ INDEX(emp idx_employee_name) */ employee_id, last_name FROM employees emp WHERE last_name = 'Smith';SELECT employee_id, last_name FROM employees emp WHERE last_name = 'Smith';

Options A, B, and C correctly demonstrate the use of an index hint to optimize a query by specifying which index to use. Option D is incorrect because it does not include the index hint, which can lead to less optimized query execution.

Q19

Multiple Choice

Which SQL code snippet demonstrates certification-level indexing of a large table with partitioning?

SQL Code

CREATE INDEX idx_partition_emp
ON employees (department_id)
LOCAL (PARTITION BY HASH (department_id));

CREATE INDEX idx_partition_emp ON employees (department_id) LOCAL (PARTITION BY HASH (department_id)); CREATE INDEX idx_partition_emp ON employees (department_id) LOCAL (PARTITION BY HASH (department_id)); CREATE INDEX idx_partition_emp ON employees (department_id) LOCAL (PARTITION BY HASH (department_id));CREATE INDEX idx_emp ON employees (department_id);

Options A, B, and C correctly demonstrate creating a partitioned index on the `department_id` column, which is useful for indexing large tables. Option D is incorrect because it does not include the partitioning strategy.

Q20

Multiple Choice

Which SQL code snippet demonstrates certification-level creation of a composite index in Oracle?

SQL Code

CREATE INDEX idx_emp_composite
ON employees (last_name, first_name, department_id);

CREATE INDEX idx_emp_composite ON employees (last_name, first_name, department_id); CREATE INDEX idx_emp_composite ON employees (last_name, first_name, department_id); CREATE INDEX idx_emp_composite ON employees (last_name, first_name, department_id);CREATE INDEX idx_emp_dept ON employees (department_id);

Options A, B, and C correctly demonstrate creating a composite index on the `last_name`, `first_name`, and `department_id` columns. Option D is incorrect because it only creates an index on a single column.

Q21

Single Choice

Which index type would you use in Oracle to optimize the following query?

SQL Code

SELECT employee_id, last_name, salary
FROM employees
WHERE salary > 50000
ORDER BY last_name ASC;

CREATE INDEX idx_salary ON employees (salary); CREATE BITMAP INDEX idx_salary_bmp ON employees (salary); CREATE UNIQUE INDEX idx_unique_salary ON employees (salary);CREATE REVERSE INDEX idx_reverse_salary ON employees (salary);

The optimal index for this query would be a B-tree index on the 'salary' column, as it allows efficient retrieval of ranges of values.

Q22

Single Choice

How would you create an index to improve the performance of a query filtering by both department_id and hire_date?

SQL Code

SELECT employee_id, department_id, hire_date
FROM employees
WHERE department_id = 10
AND hire_date > '01-JAN-2020';

CREATE INDEX idx_dept_hire ON employees (department_id, hire_date); CREATE BITMAP INDEX idx_dept_hire_bmp ON employees (department_id, hire_date); CREATE UNIQUE INDEX idx_unique_dept_hire ON employees (department_id, hire_date);CREATE REVERSE INDEX idx_reverse_dept_hire ON employees (department_id, hire_date);

A composite index on both 'department_id' and 'hire_date' would improve the performance of queries filtering by these columns.

Q23

Single Choice

Which index would you create to improve the following query where a function is applied to the column in the WHERE clause?

SQL Code

SELECT product_id, product_name
FROM products
WHERE UPPER(product_name) = 'LAPTOP';

CREATE INDEX idx_fn_product_name ON products (product_name); CREATE FUNCTION-BASED INDEX idx_fn_product_name ON products (UPPER(product_name)); CREATE UNIQUE INDEX idx_fn_unique_product_name ON products (UPPER(product_name));CREATE REVERSE INDEX idx_fn_reverse_product_name ON products (UPPER(product_name));

A function-based index is required to optimize queries where a function is applied to a column in the WHERE clause.

Q24

Single Choice

What type of index would you create to optimize queries filtering by multiple columns including one with a high number of distinct values?

SQL Code

SELECT order_id, customer_id
FROM orders
WHERE customer_id = 102
AND order_date > '01-JUL-2022';

CREATE BITMAP INDEX idx_customer_date_bmp ON orders (customer_id, order_date); CREATE UNIQUE INDEX idx_unique_customer_date ON orders (customer_id, order_date); CREATE INDEX idx_customer_date ON orders (customer_id, order_date);CREATE REVERSE INDEX idx_reverse_customer_date ON orders (customer_id, order_date);

A B-tree index is effective for filtering by columns with a high number of distinct values.

Q25

Single Choice

How would you create an index to optimize a query that performs a case-insensitive search on the email column?

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 (LOWER(email)); CREATE FUNCTION-BASED INDEX idx_fn_email ON users (LOWER(email));CREATE UNIQUE INDEX idx_unique_email ON users (LOWER(email));

A function-based index on the LOWER(email) column would allow Oracle to use the index for case-insensitive searches.

Q26

Single Choice

What is the best index type for optimizing queries that involve frequently updated columns?

SQL Code

SELECT product_id, stock
FROM inventory
WHERE stock < 100
AND product_category = 'Electronics';

CREATE BITMAP INDEX idx_stock_category_bmp ON inventory (stock, product_category); CREATE UNIQUE INDEX idx_unique_stock_category ON inventory (stock, product_category); CREATE INDEX idx_stock_category ON inventory (stock, product_category);CREATE REVERSE INDEX idx_reverse_stock_category ON inventory (stock, product_category);

A B-tree index is more suitable for columns that are frequently updated, as it maintains a balanced structure and performs well with DML operations.

Q27

Single Choice

Which index type would you create to improve the performance of a query that joins two tables on non-primary key columns?

SQL Code

SELECT o.order_id, c.customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_date > '01-JAN-2023';

CREATE UNIQUE INDEX idx_orders_customers ON orders (customer_id); CREATE INDEX idx_orders_customers ON orders (customer_id); CREATE BITMAP INDEX idx_bmp_orders_customers ON orders (customer_id);CREATE REVERSE INDEX idx_reverse_orders_customers ON orders (customer_id);

A non-unique index on the join columns in both tables would optimize the performance of the join operation.

Q28

Single Choice

How would you create an index on a large table to speed up full table scans on a specific column?

SQL Code

SELECT employee_id, department_id
FROM employees
WHERE department_id = 20;

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);

A bitmap index is suitable for columns with low cardinality, as it speeds up full table scans and is efficient in space utilization.

Q29

Single Choice

What type of index should you create to improve the query performance on a column that has a significant number of null values?

SQL Code

SELECT product_id, discount
FROM products
WHERE discount IS NOT NULL;

CREATE INDEX idx_discount ON products (discount); CREATE BITMAP INDEX idx_bmp_discount ON products (discount); CREATE UNIQUE INDEX idx_unique_discount ON products (discount);CREATE REVERSE INDEX idx_reverse_discount ON products (discount);

A B-tree index would work well on a column with many null values, especially when querying for non-null values.

Q30

Single Choice

Which type of index would you use for optimizing queries that include wildcards in the WHERE clause?

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 on the product_name column would optimize wildcard searches, especially when used with the LIKE operator.

Oracle Database Quiz Questions