Databases Reference
In-Depth Information
See also
F
The
Avoiding Full Table Scan
recipe in
Chapter 4
,
Optimizing SQL Code
and
Tuning the buffer cache
in
Chapter 9
,
Tuning Memory
Avoiding row migration
When we update a row and it does not it entirely within the original database block due to
the corresponding growth in size, we have a row migration. In the original place (where the
row was stored) we have placed a pointer to the new location of the row.
How to do it...
In this recipe, we will see how to detect row migration issues, and how to avoid migrating
rows in our tables. Follow these steps:
1.
Connect to HR schema:
CONNECT hr@TESTDB/hr
2.
Create the table
BIG_ROWS
:
CREATE TABLE HR.BIG_ROWS (
id number NOT NULL,
field1 char(2000) DEFAULT 'A' NOT NULL,
field2 char(2000),
field3 char(2000),
field4 char(1000),
constraint PK_BIG_ROWS primary key (ID))
TABLESPACE EXAMPLE PCTFREE 10;
3.
Populate the table:
INSERT INTO HR.BIG_ROWS (id)
select rownum from all_objects where rownum < 101;
4.
Analyze the table to refresh the statistics:
ANALYZE TABLE HR.BIG_ROWS COMPUTE STATISTICS;
5.
Verify if there are migrated rows:
SELECT CHAIN_CNT FROM ALL_TABLES
WHERE OWNER = ‹HR› AND TABLE_NAME = ‹BIG_ROWS›;3
