Oracle Updated 1z0-071 Exam Questions and Answers by arlen

Indexes are structures that can improve the retrieval time of data from a database table and have certain characteristics:

• A. A SELECT statement can access one or more indices without accessing any tables: If the index contains all the columns needed for the query, Oracle can retrieve the data from the index alone without accessing the table. This is known as an index-only scan.
• D. A UNIQUE index can be altered to be non-unique: It is possible to alter a unique index to become non-unique by using the ALTER INDEX command.
• F. An update to a table can result in updates to any or all of the table's indexes: If the updated column is part of one or more indexes, those indexes will need to be updated to reflect the change. If the updated column is not part of an index, then no index update is required.

References:

• Oracle Database SQL Language Reference 12c, especially sections on index management and the behavior of DML operations with respect to indexes.

When comparing the performance of different SQL join types and conditions, Oracle SQL optimizations generally ensure that performance is consistent across different syntactical forms of expressing the same logic:

• Option A: False. The BETWEEN condition does not inherently perform worse than using >= and <=. Oracle's optimizer typically evaluates these conditions similarly, optimizing the underlying execution based on the data distribution and available indexes.
• Option B: False. Oracle's optimizer is designed to handle both Oracle-specific join syntax and ANSI join syntax with equal competence. Performance differences would typically be negligible because the optimizer translates both into an optimal execution plan based on the same underlying mechanisms.
• Option C: True. The join syntax used (whether Oracle's traditional syntax or ANSI standard syntax) generally does not affect the performance. Oracle's query optimizer is adept at translating different syntaxes into efficient execution plans.
• Option D: False. The assertion that BETWEEN always performs better than >= and <= is incorrect. Performance depends more on factors like indexing, the specific data and distribution, and the Oracle optimizer's capabilities than on the mere choice of syntax.
• Option E: False. While table aliases help improve query readability and can prevent ambiguity in SQL queries, they do not inherently improve performance. Their use is a best practice for code clarity and maintenance, not performance enhancement.

Substitution variables in Oracle are used to replace a value dynamically during the execution of SQL statements. The behavior of these variables is well-documented:

• C. A substitution variable prefixed with & always prompts only once for a value in a session: This is true. In a session, when you use a single ampersand (&), SQL*Plus or SQL Developer will prompt for the value the first time the variable is encountered. The value for this variable will then be reused for the remainder of the session unless it is redefined.
• D. A substitution variable can be used with any clause in a SELECT statement: Substitution variables can be placed in any part of a SQL statement, including the SELECT, WHERE, GROUP BY, ORDER BY, etc. They are not limited to any specific clause.

References:

• Oracle SQL*Plus User's Guide and Reference, which discusses substitution variables.

An index is created automatically on columns that are defined with a UNIQUE constraint and a PRIMARY KEY constraint.

A. True. Since the serial_no column is defined with a UNIQUE constraint, Oracle will automatically create an index for this column.

E. True. The order_id column is defined with a PRIMARY KEY constraint, and Oracle automatically creates a unique index on the primary key column.