One of the methods of removing Row Chaining which hinders Performance is by Use of the MOVE Command
One method for resolving the row chaining within a table is to use the MOVE statement. When you move a table, Oracle requires an exclusive lock on the table; therefore you should perform this operation when there are no active transactions associated with the table being moved.
Also, as part of a MOVE operation, all of the rows are assigned a new ROWID. This will invalidate any indexes that are associated with the table. Therefore, as part of the move operation, you should rebuild all indexes associated with the table being moved. This example moves the EMP table:
SQL> alter table <Table_name> move
One method for resolving the row chaining within a table is to use the MOVE statement. When you move a table, Oracle requires an exclusive lock on the table; therefore you should perform this operation when there are no active transactions associated with the table being moved.
Also, as part of a MOVE operation, all of the rows are assigned a new ROWID. This will invalidate any indexes that are associated with the table. Therefore, as part of the move operation, you should rebuild all indexes associated with the table being moved. This example moves the EMP table:
SQL> alter table <Table_name> move
Shrinking Un Used Space
You can instruct Oracle to not re-adjust the high-water mark when shrinking a table. This is done via the COMPACT clause—for example:
SQL> alter table <Table_name> shrink space compact;
You can instruct Oracle to not re-adjust the high-water mark when shrinking a table. This is done via the COMPACT clause—for example:
SQL> alter table <Table_name> shrink space compact;
Automatic Segment Space Management
You cannot create a temporary tablespace with ASSM. This is because of the transient nature of temporary segments when sorting is performed.
Use ASSM when you want to waste a bit of space for increased concurrency - or want to take advantage of a feature that is only available with ASSM (like shrinking is only available with ASSM). System and undo tablespaces would not benefit from ASSM and default to "non-ASSM". You do not walk freelists in the undo tablespace to find space for transaction data - it is managed very different from 'regular' tablespaces. They are manual segment space managed.
You cannot create a temporary tablespace with ASSM. This is because of the transient nature of temporary segments when sorting is performed.
Use ASSM when you want to waste a bit of space for increased concurrency - or want to take advantage of a feature that is only available with ASSM (like shrinking is only available with ASSM). System and undo tablespaces would not benefit from ASSM and default to "non-ASSM". You do not walk freelists in the undo tablespace to find space for transaction data - it is managed very different from 'regular' tablespaces. They are manual segment space managed.
Concatenated Index
When frequently accessing two or more columns in conjunction in the WHERE clause, a concatenated index is often more selective than two single indexes.
In the case of a Concatenated Index, The ability of an Oracle Optimizer to use a Concatenated index without reference to leading edge columns is known as the skip-scan feature.A concatenated index that is used for skip-scanning is more efficient than a full table scan. Index compression is useful for indexes that contain multiple columns where the leading index column value is often repeated.
When frequently accessing two or more columns in conjunction in the WHERE clause, a concatenated index is often more selective than two single indexes.
In the case of a Concatenated Index, The ability of an Oracle Optimizer to use a Concatenated index without reference to leading edge columns is known as the skip-scan feature.A concatenated index that is used for skip-scanning is more efficient than a full table scan. Index compression is useful for indexes that contain multiple columns where the leading index column value is often repeated.
Compressed indexes - Advantages
* Reduced storage
* More rows stored in leaf blocks, which can result in less I/O when accessing a compressed index
No comments:
Post a Comment