Index Coalesce/Shrink

*** First start with an Index Coalesce in a Non-ASSM tablespace

SQL> CREATE TABLE rakesh_stuff (id NUMBER, name VARCHAR2(20));

Table created.

SQL> INSERT INTO rakesh_stuff SELECT rownum, ‘DAVID rakesh’ FROM dual CONNECT
BY LEVEL <= 1000000; 2

1000000 rows created.

SQL> commit;

Commit complete.

SQL> CREATE INDEX rakesh_stuff_i ON rakesh_stuff(id);

Index created.

*** Delete 25% of rows

SQL> DELETE rakesh_stuff WHERE mod(id,4) = 1;
250000 rows deleted.

SQL> SQL> commit;

Commit complete.

SQL> ANALYZE INDEX rakesh_stuff_i VALIDATE STRUCTURE;

Index analyzed.

SQL> SELECT height, blocks, lf_blks, br_blks, btree_space, pct_used FROM
index_stats; 2

HEIGHT BLOCKS LF_BLKS BR_BLKS BTREE_SPACE PCT_USED
———- ———- ———- ———- ———– ———-
3 3207 2226 5 17839236 76
*** Note before and after value of redo size

SQL> SELECT n.name, s.value FROM v$mystat s, v$statname n WHERE
s.statistic# = n.statistic# AND n.name = ‘redo size’; 2

NAME VALUE
—————————————————————- ———-
redo size 147374784

SQL> ALTER INDEX rakesh_stuff_i COALESCE;

Index altered.

SQL> SELECT n.name, s.value FROM v$mystat s, v$statname n WHERE
s.statistic# = n.statistic# AND n.name = ‘redo size’; 2

NAME VALUE
—————————————————————- ———-
redo size 183946868

*** Note that 183946868 bytes of redo generated

SQL> ANALYZE INDEX rakesh_stuff_i VALIDATE STRUCTURE;

Index analyzed.

SQL> SELECT height, blocks, lf_blks, br_blks, btree_space, pct_used FROM
index_stats; 2

HEIGHT BLOCKS LF_BLKS BR_BLKS BTREE_SPACE PCT_USED
———- ———- ———- ———- ———– ———-
3 3207 1671 5 13401456 90

SQL> ALTER INDEX rakesh_stuff_i SHRINK SPACE COMPACT;
ALTER INDEX rakesh_stuff_i SHRINK SPACE COMPACT
*
ERROR at line 1:
ORA-10635: Invalid segment or tablespace type

*
ERROR at line 1:
ORA-10635: Invalid segment or tablespace type
*** Unable to use SHRINK as index is not in a ASSM tablespace …
*** Repeat but this time create the index in an ASSM tablespace …

SQL> drop table rakesh_stuff;

Table dropped.
SQL> CREATE TABLE rakesh_stuff (id NUMBER, name VARCHAR2(20)) tablespace users;

Table created.

SQL> INSERT INTO rakesh_stuff SELECT rownum, ‘DAVID rakesh’ FROM dual CONNECT
BY LEVEL <= 1000000; 2

1000000 rows created.

SQL> commit;

Commit complete.

SQL> CREATE INDEX rakesh_stuff_i ON rakesh_stuff(id) TABLESPACE USERS;

Index created.

SQL> DELETE rakesh_stuff WHERE mod(id,4) = 1;

250000 rows deleted.

SQL> commit;

Commit complete.

SQL> SELECT n.name, s.value FROM v$mystat s, v$statname n WHERE
s.statistic# = n.statistic# AND n.name = ‘redo size’; 2

NAME VALUE
—————————————————————- ———-
redo size 331477552

SQL> ALTER INDEX rakesh_stuff_i COALESCE;

Index altered.

SQL> SELECT n.name, s.value FROM v$mystat s, v$statname n WHERE
s.statistic# = n.statistic# AND n.name = ‘redo size’; 2

NAME VALUE
—————————————————————- ———-

redo size 370531720

SQL> ANALYZE INDEX rakesh_stuff_i VALIDATE STRUCTURE;

Index analyzed.

SQL> SELECT height, blocks, lf_blks, br_blks, btree_space, pct_used FROM
index_stats; 2

HEIGHT BLOCKS LF_BLKS BR_BLKS BTREE_SPACE PCT_USED
———- ———- ———- ———- ———– ———-
3 2304 1671 5 13401456 90

SQL> SELECT n.name, s.value FROM v$mystat s, v$statname n WHERE
s.statistic# = n.statistic# AND n.name = ‘redo size’; 2

NAME VALUE
—————————————————————- ———-
redo size 370532508

SQL> ALTER INDEX rakesh_stuff_i SHRINK SPACE;

Index altered.

SQL> SELECT n.name, s.value FROM v$mystat s, v$statname n WHERE
s.statistic# = n.statistic# AND n.name = ‘redo size’; 2

NAME VALUE
—————————————————————- ———-
redo size 378059212

SQL> ANALYZE INDEX rakesh_stuff_i VALIDATE STRUCTURE;

Index analyzed.

SQL> SELECT height, blocks, lf_blks, br_blks, btree_space, pct_used FROM
index_stats; 2

HEIGHT BLOCKS LF_BLKS BR_BLKS BTREE_SPACE PCT_USED
———- ———- ———- ———- ———– ———-
3 1720 1671 5 13401456 90

*** And it has also reduced the number of blocks allocated to the index
segment (1720 down from 2304)
*** Now repeat with just SHRINK options

SQL> CREATE TABLE rakesh_stuff (id NUMBER, name VARCHAR2(20)) tablespace users;

Table created.

SQL> INSERT INTO rakesh_stuff SELECT rownum, ‘DAVID rakesh’ FROM dual CONNECT
BY LEVEL <= 1000000; 2

1000000 rows created.

SQL> commit;

Commit complete.

SQL> CREATE INDEX rakesh_stuff_i ON rakesh_stuff(id) TABLESPACE USERS;

Index created.

SQL> DELETE rakesh_stuff WHERE mod(id,4) = 1;

250000 rows deleted.

SQL> commit;

Commit complete.

SQL> SELECT n.name, s.value FROM v$mystat s, v$statname n WHERE
s.statistic# = n.statistic# AND n.name = ‘redo size’; 2

NAME VALUE
—————————————————————- ———-
redo size 525622436
*** Now use SHRINK SPACE COMPACT which defragments the index but does not
actually deallocate any freed space

SQL> ALTER INDEX rakesh_stuff_i SHRINK SPACE COMPACT;

Index altered.

SQL> SELECT n.name, s.value FROM v$mystat s, v$statname n WHERE
s.statistic# = n.statistic# AND n.name = ‘redo size’; 2

NAME VALUE
—————————————————————- ———-
redo size 559028460

*** Note it uses significantly more redo than the corresponding Index
Coalesce

*** (roughly the cost of the Coalesce plus the cost of the following Shrink
Space in previous example)

QL> ANALYZE INDEX rakesh_stuff_i VALIDATE STRUCTURE;

Index analyzed.

SQL> SELECT height, blocks, lf_blks, br_blks, btree_space, pct_used FROM
index_stats; 2

HEIGHT BLOCKS LF_BLKS BR_BLKS BTREE_SPACE PCT_USED
———- ———- ———- ———- ———– ———-
3 2304 2223 6 17823276 71

*** Note that the state of the index is identical to that of the Index
Coalesce …
SQL> SELECT n.name, s.value FROM v$mystat s, v$statname n WHERE
s.statistic# = n.statistic# AND n.name = ‘redo size’; 2

NAME VALUE
—————————————————————- ———-
redo size 559103368
SQL> ALTER INDEX rakesh_stuff_i SHRINK SPACE;

Index altered.

SQL> SELECT n.name, s.value FROM v$mystat s, v$statname n WHERE
s.statistic# = n.statistic# AND n.name = ‘redo size’;
NAME VALUE 2
NAME VALUE
—————————————————————- ———-
redo size 581532664

*** It required very little additonal redo to complete the job
SQL> ANALYZE INDEX rakesh_stuff_i VALIDATE STRUCTURE;

Index analyzed.

SQL> SELECT height, blocks, lf_blks, br_blks, btree_space, pct_used FROM
index_stats; 2

HEIGHT BLOCKS LF_BLKS BR_BLKS BTREE_SPACE PCT_USED
———- ———- ———- ———- ———– ———-
3 1720 1672 6 13417480 90

*** And it has also reduced the index segment blocks down to 1720 …

Ref:

https://richardfoote.wordpress.com/2008/02/06/differences-and-similarities-between-index-coalesce-and-shrink-space/

Rebuild/Coalesce/Shrink

https://richardfoote.wordpress.com/2008/02/08/index-rebuild-vs-coalesce-vs-shrink-space-pigs-3-different-ones/

 

 

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s