O'Reilly logo

Oracle Database 11gR2 Performance Tuning Cookbook by Ciro Fiorillo

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Avoiding row migration

When we update a row and it does not fit 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) ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required