If users are frequently executing
range queries??”selecting those values that are within a specified range??”then having the data
ordered may require fewer data blocks to be read while resolving the query, thus improving
performance. The ordered entries in the index will point to a set of neighboring blocks in the
table rather than blocks that are scattered throughout the datafile(s).
For example, consider a range query of the following type:
select *
from EMPLOYEE
where Empno between 1 and 100;
This range query will require fewer data blocks to be read if the physical rows in the EMPLOYEE
table are ordered by the EMPNO column. To guarantee that the rows are properly ordered in the
table, extract the rows to a flat file (or another table), sort the rows there, and then delete the old
rows and reload them from the sorted data set. In addition, you should use online segment shrink
to reclaim fragmented free space below the high water mark for tables with frequent DML activity;
this improves cache utilization and requires fewer blocks to be scanned in full table scans. You
use the alter table . . . shrink space command to compact the free space in a table.
Impact of Order on Load Rates
Indexes impact the performance of both queries and data loads. During insert operations, the
rows??™ order has a significant impact on load performance. Even in heavily indexed environments,
properly ordering the rows prior to insert may improve load performance by 50 percent.
Pages:
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417