Performance Tuning - Question 082

Which statement could require a sort?

A. SELECT DISTINCT dept_id FROM emp:

B. UPDATE emp SET salary=salary*1.1 WHERE id =7722;

C. SELECT emp_id, name FROM emp WHERE emp-id= 7722;

D. SELECT emp_id, name FROM emp WHERE emp_id BETWEEN 7722 and 7100;

Answer: A

Explanation:

The types of SQL statements that can cause database sorts to occur include the following: ORDER BY, GROUP BY, SELECT DISTINCT, UNION, INTERSECT, MINUS, ANALYZE, CREATE INDEX, joins between tables on columns that are not indexed.

Incorrect Answers

B: This UPDATE statement does not require any sort operation.

C: This SELECT statement does not require any sort operation.

D: Usage of BETWEEN clause in the SELECT statement will not cause any sort operation.

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

Chapter 8: Tuning Disk I/O