Shulou(Shulou.com)06/01 Report--

The ROW MOVEMENT feature was first introduced in 8i to improve the flexibility of the partition table. this feature is turned off by default and needs to be turned on only by using three functions: 1.Flashback Table can help us roll back some misoperations in time to prevent accidental loss of data. Before you can use this feature, you must open ROW MOVEMENT, otherwise an ORA-08189 error will be thrown. Select username, rowid from test_move; delete from test_move where username='MYTBC'; commit; alter table test_move enable row movement; flashback table test_move to timestamp (systimestamp-interval'3' minute);-- flashes back to the state it was 3 minutes ago, when the username='MYTBC' record was not deleted. Select username, rowid from test_move;-- the query shows that the data is recovered. At this time, comparing the recorded ROWID before and after flashback, the physical location of most records changes. The internal operation of this process can be further observed by SQL Trace the Flashback Table. Through Trace, it is not difficult to find that Flashback Table actually deletes and inserts the data in the table through Flashback Query, so ROWID will change. 2.Shrink Segment (lowering the high water level of the meter) Shrink Segment can help us compress data segments, defragment data, and lower the high water level to improve performance and save space. It also requires ROW MOVEMENT to be turned on. Select username, rowid from test_move; delete from test_move where username = 'MYTBC';-at this time shrink space will report a 10636 error alter table test_move enable row movement; alter table test_move shrink space; select username, rowid from test_move; we can see that ROWID has changed after Shrink. From the point of view of the Trace of its process, the change of data by Shrink is not achieved through SQL, but through lower-level functions. 3. Updating Partition Key when updating the Partition Key in a record may cause the record to go beyond the scope of the current partition and need to be transferred to another corresponding partition, so ROW MOVEMENT is required to be enabled. Drop table test_move; create table test_move partition by list (owner) (partition p1 values ('SYS'), partition p2 values (' DEMO'), partition p3 values ('SYSTEM'), partition def values (default)) as select * from dba_tables;-this time update will report a 14402 error alter table test_move enable row movement; update test_move set owner='SYS' where owner='DEMO' and table_name='T_TEST' The special thing about the impact of this operation is that it is a DML operation, which is closely related to online transaction. For such a UPDATE, there are actually three steps: first, delete the data from the original partition; transfer the original data to the new partition; and update the data. The impact lies in the following aspects: a UPDATE is decomposed into three operations: DELET, INSERT, and UPDATE, which increases the performance burden. Among them, the query condition of DELETE is the same as that of the original UPDATE, and the query condition of the new UPDATE is based on the new ROWID; generated by INSERT. The corresponding Redo Log and Undo Log will be increased; if the Update statement also involves the field of Local Index, the Local Index on the new and old partitions will be updated. Also, Row Movement conflicts with domain indexes (Domain Index): if a domain index is defined on the table, opening Row Movement will fail, and vice versa.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.