Examples-1
Assume: The tables are very large.
Reason: If a table is small, the optimizer will likely not use any access structure. Rather, it
will read it into MM and perform a scan
Given the following range query, comment on its performance based upon each one of the
following design choices.
SELECT E.dno
FROM Emp E
WHERE E.age>40
1. Providing and using a clustered index on age; and almost every employee is >40.
2. Providing and using a clustered index on age; and about 25% of employees are >40.
3. Providing and using an unclustered index on age; and about 25% of employees are >40.
Given the following range query, analyze the expected performance under each one of the
different scenarios that follow
SELECT E.dno, COUNT (*)
FROM Emp E
WHERE E.age>40
GROUP BY E.dno
1. Scenario-1: Providing and using a clustered index on age; and almost every employee is
>40.
Not worth, It is almost "Scan" solution: sort by dno, sort is a time
2. Scenario-2: Providing and using an unclustered index on age; and almost every
employee is >40.
Really bad
Costing operation
3. Scenario-3: Providing and using a clustered index on dno; and almost every employee is
>40.
very good / sorted by dno
4. Scenario-4: Providing and using an unclustered index on dno; and almost every
employee is >40.
Terrible
storageAndIndexing-673-EN.doc
Page 7 of 8
