Oracle SQL Oracle SQL Advanced Features 3

Question 1

What is a bind variable in Oracle SQL and why is it important?

Accepted Answer

A placeholder in SQL that promotes cursor sharing and prevents hard parses

Answer

Bind variables replace literal values with placeholders (e.g., :var), allowing Oracle to reuse execution plans and preventing SQL injection.

Question 2

Which Oracle SQL feature enables row-level security by filtering rows based on the current user?

Accepted Answer

Virtual Private Database (VPD)

Answer

Virtual Private Database (VPD) uses DBMS_RLS to attach security policies to tables, automatically appending WHERE clauses based on the current user.

Question 3

What does the EXPLAIN PLAN statement do in Oracle SQL?

Accepted Answer

Shows the execution plan Oracle will use to run a query without executing it

Answer

EXPLAIN PLAN stores the optimizer's execution plan for a SQL statement in the PLAN_TABLE without actually running the query.

Question 4

Which Oracle SQL hint forces the optimizer to use a specific index?

Accepted Answer

/*+ INDEX(table index_name) */

Answer

The INDEX hint instructs the optimizer to use a specific index for the query, overriding its cost-based decision.

Question 5

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

Accepted Answer

Gathers optimizer statistics on tables, indexes, and columns

Answer

DBMS_STATS gathers and manages optimizer statistics (row counts, data distribution, column histograms) that Oracle uses to generate efficient execution plans.

Oracle SQL Practice Test

Oracle SQL Practice Test

Oracle SQL Oracle SQL Advanced Features 3