Performance Tuning - Question 096

In which two ways can you reduce the amount of sorting that is performed? (Choose two)

A. By using UNION instead of UNION ALL.

B. By using NOSORT when creating tables.

C. By using NOSORT when creating indexes.

D. By using COMPUTE instead of ESTIMATE when analyzing objects.

E. By reducing the number of users that have the sort privilege.

F. By creating appropriate indexes on tables that are joined often.

Answer: B, F

Explanation:

You can avoid a sorting operation by using NOSORT clause when creating table or by creating appropriate indexes on tables that are joined often.

Incorrect Answers

A: The UNION type SQL statement will cause sort operation.

C: You cannot avoid sorting by using NOSORT when creating indexes.

D: By using COMPUTE instead of ESTIMATE when analyzing objects you will process ALL records in the table. It will cause sorting also.

E: There is no sort privilege in Oracle. All users can sort data in Oracle tables.

OCP: Oracle 9i Performance Tuning Study Guide, Joseph C. Johnson, p. 411-412

Chapter 8: Tuning Disk I/O