[bbk3019] 第60集 - Chapter 15-Application Tuning(00)

Objectives

After completing this lesson,you should be able to do the following:

• Explain the role of the DBA in tuning applications.
• Move tables using the ALTER TABLE command.
• Redefine a table online
• Create different types of indexes
• Build and manage index-organized tables
• Explain and plan OLTP,DSS,and hybrid systems

The Role of DBA

• Application tuning is the most important part of tuning
• DBAs are not alwarys directly involved in application tuning.
• However,DBAs must be familiar with the impact that poorly written SQL statements can have upon database performance.

Moving Tables

Tables can be moved using the ALTER TABLE command.This allows:

• Privileges and constraints to be kept.
• The table structure to be changed.
• Movement to another tablespace.
• The command to be parallelized.

SQL>ALTER TABLE hr.employees

MOVE

TABLESPACE users;

通过moving table 方式，要比create table tab_name as select * from tab_name;方式要安全、有效、可靠的多.

Redefining a Table Online

Online table redefining enables you to:

• Modify the storage parameters of the table.
• Move the table to a different tablespace in the same schema.
• Add support for parallel queries
• Add or drop partitioning support
• Re-create the table to reduce fragmentation
• Change the organization of a normal table(heap organized) to an index-organized table and vice versa.
• Add or drop a column.

B-Tree Indexes

Rebuilding Indexes

To assist in the rebuilding of indexes use:

• ONLINE:Keeps the index avaliable during the rebuilding operation
• COMPUTE STATISTICS:Collects the statistics while rebuilding the index( 过时了)

Compressed Indexes

When creating the index:(very simply->add keyword COMPRESS when create index.)

CREATE INDEX emp_last_name_idx
ON hr.employees
(last_name,first_name)
COMPRESS;

When rebuilding the index:

ALTER INDEX emp_last_name_idx REBUILD COMPRESS;

Specify NOCOMPRESS(the default) to disable key compression.