You can drop a partition??™s local index.
You can set a partition to nologging, reducing the impact of large transactions.
From a performance perspective, the chief advantage of partitions lies in their ability to be
managed apart from the rest of the table. For example, being able to truncate a partition enables
you to delete a large amount of data from a table (but not all of the table??™s data) without generating
any redo information. In the short term, the beneficiary of this performance improvement is the
DBA; in the longer term, the entire enterprise benefits from the improved availability of the data.
See Chapter 16 for details on implementing partitions and subpartitions.
You can use the exchange partition option to greatly reduce the impact your data-loading
processes have on system availability. Start by creating an empty table that has the same column
structure as your partitioned table. Load your data into the new table and then analyze the new
table. Create indexes on the new table to match the partitioned table??™s indexes; the indexes must
be local, and not global, indexes. When these steps are complete, alter the partitioned table using
the exchange partition clause to exchange an empty partition with the new table you populated.
All the loaded data will now be accessible via the partitioned table. There is little impact to the
system availability during this step because it is a DDL operation.
?–
?–
?–
?–
Chapter 8: Database Tuning 267
Tuning Physical Storage
Database I/O should be evenly distributed across as many devices as possible.
Pages:
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454