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

Non – Unique index- Primary Key

Post is to explain that a non-unique index can police a primary constraint
SQL> create table rakesh as select rownum id, ‘rakesh’ text from dual connect by level <= 10000;

Table created.

SQL> create index rakesh_i on rakesh(id);

Index created.

SQL> alter table rakesh add constraint rakesh_pk primary key(id);

Table altered.

SQL> select ic.index_name, ic.column_name, i.uniqueness, c.constraint_name, c.constraint_type from user_ind_columns ic, user_indexes i, user_constraints c where ic.index_name = i.index_name and i.index_name = c.index_name and i.index_name =’RAKESH_I’;

INDEX_NAME
——————————
COLUMN_NAME
——————————————————————————–
UNIQUENES CONSTRAINT_NAME C
——— —————————— –
RAKESH_I
ID
NONUNIQUE RAKESH_PK P

Even if there are additional columns within the index, the fact the leading columns match the PK constraint means Oracle can still use the index to police the constraint as the index must be in the same logical order of these leading columns.
SQL> alter table rakesh drop primary key;

Table altered.

SQL> drop index RAKESH_I;

Index dropped.

SQL> create index rakesh_i on rakesh(id,text);

Index created.

SQL> alter table rakesh add constraint rakesh_pk primary key(id);

Table altered.

SQL> select ic.index_name, ic.column_name, i.uniqueness, c.constraint_name, c.constraint_type from user_ind_columns ic, user_indexes i, user_constraints c where ic.index_name = i.index_name and i.index_name = c.index_name and i.index_name =’RAKESH_I’
2 ;

INDEX_NAME
——————————
COLUMN_NAME
——————————————————————————–
UNIQUENES CONSTRAINT_NAME C
——— —————————— –
RAKESH_I
TEXT
NONUNIQUE RAKESH_PK P

RAKESH_I
ID
NONUNIQUE RAKESH_PK P

INDEX_NAME
——————————
COLUMN_NAME
——————————————————————————–
UNIQUENES CONSTRAINT_NAME C
——— —————————— –

using Deferrable clause:
This means the constraint could be used to defer the policing of the constraint until the time of the COMMIT, rather than at the time of the DML statement.
Oracle has to create a non-unique index in this case.
SQL> alter table rakesh drop primary key;

Table altered.

SQL> drop index rakesh_i;

Index dropped.

SQL> alter table rakesh add constraint rakesh_pk primary key(id) deferrable;

Table altered.

SQL> select ic.index_name, ic.column_name, i.uniqueness, c.constraint_name, c.constraint_type from user_ind_columns ic, user_indexes i, user_constraints c where ic.index_name = i.index_name and i.index_name = c.index_name and i.index_name =’RAKESH_I’;

no rows selected

SQL> select ic.index_name, ic.column_name, i.uniqueness, c.constraint_name, c.constraint_type from user_ind_columns ic, user_indexes i, user_constraints c where ic.index_name = i.index_name and i.index_name = c.index_name and i.index_name =’RAKESH_PK’;

INDEX_NAME
——————————
COLUMN_NAME
——————————————————————————–
UNIQUENES CONSTRAINT_NAME C
——— —————————— –
RAKESH_PK
ID
NONUNIQUE RAKESH_PK P

we can create non-unique index while defining the primary constraint

SQL> alter table rakesh drop primary key;

Table altered.

SQL> drop index rakesh_pk;

Index dropped.

SQL> alter table rakesh add constraint rakesh_pk primary key (id) using index (create index rakesh_1 on rakesh(id));

Table altered.

select ic.index_name, ic.column_name, i.uniqueness, c.constraint_name, c.constraint_type from user_ind_columns ic, user_indexes i, user_constraints c where ic.index_name = i.index_name and i.index_name = c.index_name and i.table_name = ‘RAKESH’;SQL>

INDEX_NAME
——————————
COLUMN_NAME
——————————————————————————–
UNIQUENES CONSTRAINT_NAME C
——— —————————— –
RAKESH_1
ID
NONUNIQUE RAKESH_PK P

 

Ref: https://richardfoote.wordpress.com/2008/06/04/primary-keys-and-non-unique-indexes-whats-really-happening/