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.
Syntax
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.