Changed 1z0-071 Exam Questions

INVISIBLE indexes are a feature in Oracle Database 12c and later versions that allow an index to be maintained but not used by the optimizer unless explicitly hinted.

A. False. An INVISIBLE index still consumes storage space as it is maintained in the background.

B. False. There is no such restriction. You can create multiple INVISIBLE indexes on the same column list.

C. False. The optimizer can consider INVISIBLE indexes if they are hinted at in the query.

D. True. You can alter the visibility of an index using the ALTER INDEX command to make an INVISIBLE index VISIBLE.

E. True. Even though they are invisible to the optimizer by default, all DML operations such as INSERT, UPDATE, and DELETE continue to maintain the index as they would with a VISIBLE index.

Single-row functions in SQL operate on each row independently and can modify the returned value:

Option A: Incorrect. Single row functions can be used in multiple parts of a SELECT statement, including SELECT, WHERE, and ORDER BY clauses.

Option B: Incorrect. Single row functions can accept more than one argument, such as the CONCAT function, which can accept multiple string arguments.

Option C: Incorrect. They return one result for each row processed, not per table.

Option D: Correct. Single row functions can take various types of arguments including column names, literals, variables, and other expressions.

Option E: Correct. Functions can be nested within other functions, allowing complex expressions and calculations.

Option F: Correct. The data type of the result can differ from the arguments’ data types, such as the SUBSTR function returning a VARCHAR2 even when used on a number after converting it to a string.

The data dictionary contains metadata (data about data) about the database and its objects.

A. False. DBA_ views display metadata for objects accessible to the database administrator, not only for the SYS schema.

B. True. ALL_ views display information about all the objects that the current user has access to. This does not necessarily mean the user has privileges on these objects, only that the user can see them.

C. False. The data dictionary is not accessible when the database is closed because it requires the database to be open in order to access the system tables.

D. True. Not all types of metadata are available in all three prefixes (ALL_, DBA_, USER_). Some specific metadata might only be available in one or two of these view types.

E. False. The data dictionary stores metadata in tables. The various views (ALL_, DBA_, USER_, etc.) provide different perspectives on this data.

References:

Oracle Documentation on Data Dictionary and Dynamic Performance Views: https://docs.oracle.com/cd/B28359_01/server.111/b28310/datadict.htm

E. True. Indexes can be created on global temporary tables just like they can on permanent tables. The indexes on global temporary tables are also temporary and only exist for the duration of the session or transaction, based on how the table was defined.

F. True. If the ON COMMIT clause is specified as DELETE ROWS, then all rows in the global temporary table are deleted after each COMMIT or ROLLBACK within the transaction. This clause defines the scope of the data's persistence within the global temporary table.