Oracle SQL DDL and Schema Objects Flashcards

What does the INVISIBLE option do when creating an index in Oracle SQL (11g+)?

Answer: Creates the index but the optimizer ignores it by default

An invisible index exists and is maintained by DML, but the optimizer ignores it unless OPTIMIZER_USE_INVISIBLE_INDEXES is set or the index is hinted.

Which Oracle SQL object stores the result of a query and updates it periodically?

Answer: Materialized view

A materialized view physically stores query results and can be refreshed on a schedule or on demand, improving query performance.

What does the ENABLE NOVALIDATE constraint state do in Oracle SQL?

Answer: Enables the constraint for new rows only, without validating existing rows

ENABLE NOVALIDATE enforces the constraint only on new or modified rows without checking existing rows that may violate it.

Which Oracle SQL statement creates a table that exists only for the duration of a session?

Answer: CREATE GLOBAL TEMPORARY TABLE

CREATE GLOBAL TEMPORARY TABLE creates a temporary table whose data is private to each session and is deleted at session or transaction end.

What is the purpose of the GRANT statement in Oracle SQL?

Answer: Gives privileges on database objects to users or roles

GRANT gives specified privileges (SELECT, INSERT, UPDATE, etc.) on database objects to users or roles.

What does ALTER TABLE employees ADD CONSTRAINT emp_pk PRIMARY KEY (employee_id) do?

Answer: Adds a primary key constraint to an existing table

This ALTER TABLE statement adds a named PRIMARY KEY constraint on the employee_id column to an already-created table.