A question on what would be the case if the Index were a REVERSE KEY index was raised. (I will not cover "why REVERSE KEY" here). When I thought about it, I realised that it would cause 50-50 block splits. The initial entries (as the table begins) would of course, be in a single block. But as the index grew, values would be switching between the two "ends" of the index and would also be extending the stored minimum and maximum values (that are reversed from the user-entered values).
So, here's a simple proof of Index Block Splits for a an index on a monotonously increasing sequence -- the splits are 50-50 and the index leaf blocks are not as tightly packed as in a "normal" index :
NOTE 1 : I had initially published this post with the table name as "DEMO_IBS_90_10_RK" but have now rerun the scripts with the more meaningful name "DEMO_IBS_RK".
NOTE 2 : Both these Test Runs (and the test runs for the previous blog posts on Index Block Splits) are on 10.2.0.4, 64bit Linux, ASSM Tablespaces @8KB block size.
There are 2 Test Runs. The first is with the same index definition as in the previous blog post. The second test run is with the index on only the numeric column EMPLOYEE_ID.
TEST RUN 1 with a multi-column Unique Index.
SQL> drop table demo_ibs_rk purge;
Table dropped.
SQL>
SQL> -- REM create the demo table
SQL> create table demo_ibs_rk (
2 employee_id number not null,
3 country_name varchar2(10) not null,
4 dept_name varchar2(18) not null,
5 employee_name varchar2(128) not null,
6 join_date date)
7 /
Table created.
SQL>
SQL> -- create the index with a PCTFREE of 1 to pack it tightly
SQL> -- BUT CREATE IT AS A REVERSE KEY INDEX
SQL> create unique index demo_ibs_rk_u1 on
2 demo_ibs_rk (employee_id,country_name,employee_name)
3 reverse pctfree 1;
Index created.
SQL>
SQL> drop table source_table;
Table dropped.
SQL> create table source_table
2 as select * from dba_objects
3 where 1=2;
Table created.
SQL> insert /*+ APPEND */ into source_table
2 select * from dba_objects
3 where object_id is not null;
50833 rows created.
SQL> commit;
Commit complete.
SQL> select max(object_id) from source_table;
MAX(OBJECT_ID)
--------------
58316
SQL>
SQL>
SQL> -- REM create a new session and run an insert
SQL> -- then check the statistics for the insert
SQL> connect hemant/hemant
Connected.
SQL> insert into demo_ibs_rk
2 select object_id, substr(owner,1,10),
3 substr(object_type,1,18),
4 rpad(object_name,20,dbms_random.string('X',3)),
5 created
6 from source_table
7 where object_id is not null
8 order by object_id
9 /
50833 rows created.
SQL> commit;
Commit complete.
SQL>
SQL> select sn.name, ms.value
2 from v$statname sn, v$mystat ms
3 where sn.statistic#=ms.statistic#
4 and sn.name like '%leaf node %'
5 order by 1
6 /
NAME VALUE
---------------------------------------------------------------- ----------
leaf node 90-10 splits 0
leaf node splits 386
SQL>
SQL> exec dbms_stats.gather_table_stats(-
> '','DEMO_IBS_RK',estimate_percent=>100,cascade=>TRUE);
PL/SQL procedure successfully completed.
SQL> select num_rows, distinct_keys, blevel, leaf_blocks,
2 distinct_keys/leaf_blocks
3 from user_indexes
4 where index_name = 'DEMO_IBS_RK_U1'
5 /
NUM_ROWS DISTINCT_KEYS BLEVEL LEAF_BLOCKS DISTINCT_KEYS/LEAF_BLOCKS
---------- ------------- ---------- ----------- -------------------------
50833 50833 1 387 131.351421
SQL> analyze index demo_ibs_rk_u1 validate structure;
Index analyzed.
SQL> select lf_rows, lf_blks, br_blks, del_lf_rows, pct_used
2 from index_stats;
LF_ROWS LF_BLKS BR_BLKS DEL_LF_ROWS PCT_USED
---------- ---------- ---------- ----------- ----------
50833 387 1 0 68
SQL>
SQL> REM #################
SQL> REM OBSERVATION !
SQL> REM The first set of block creations are all from Block Splits.
SQL> REM And all of these are now 50-50 Splits !
SQL> REM #################
SQL>
SQL>
SQL>
SQL> -- REM run another set of inserts
SQL> -- REM we now simulate a Row-By-Row Insert that would happen
SQL> -- REM for creating new Employees
SQL> connect hemant/hemant
Connected.
SQL>
SQL> declare
2 i number;
3
4 begin
5 for i in 1..1000
6 loop
7 insert into demo_ibs_rk
8 select object_id+100000+i,
9 substr(owner,1,10),
10 substr(object_type,1,18),
11 rpad(object_name,20,dbms_random.string('X',3)),
12 created+vsize(object_name)
13 from source_table
14 where object_id is not null
15 and object_id = 1000+i;
16 commit;
17 end loop;
18 end;
19 /
PL/SQL procedure successfully completed.
SQL>
SQL> select sn.name, ms.value
2 from v$statname sn, v$mystat ms
3 where sn.statistic#=ms.statistic#
4 and sn.name like '%leaf node %'
5 order by 1
6 /
NAME VALUE
---------------------------------------------------------------- ----------
leaf node 90-10 splits 0
leaf node splits 17
SQL>
SQL> exec dbms_stats.gather_table_stats(-
> '','DEMO_IBS_RK',estimate_percent=>100,cascade=>TRUE);
PL/SQL procedure successfully completed.
SQL> select num_rows, distinct_keys, blevel, leaf_blocks,
2 distinct_keys/leaf_blocks
3 from user_indexes
4 where index_name = 'DEMO_IBS_RK_U1'
5 /
NUM_ROWS DISTINCT_KEYS BLEVEL LEAF_BLOCKS DISTINCT_KEYS/LEAF_BLOCKS
---------- ------------- ---------- ----------- -------------------------
51833 51833 1 404 128.299505
SQL> analyze index demo_ibs_rk_u1 validate structure;
Index analyzed.
SQL> select lf_rows, lf_blks, br_blks, del_lf_rows, pct_used
2 from index_stats;
LF_ROWS LF_BLKS BR_BLKS DEL_LF_ROWS PCT_USED
---------- ---------- ---------- ----------- ----------
51833 404 1 0 67
SQL>
SQL>
SQL>
SQL> -- REM run another set of inserts
SQL> -- REM we now run a bulk insert again !
SQL> connect hemant/hemant
Connected.
SQL>
SQL> insert into demo_ibs_rk
2 select object_id+200000,
3 substr(owner,1,10),
4 substr(object_type,1,18),
5 rpad(object_name,20,dbms_random.string('X',3)),
6 created+vsize(object_name)
7 from source_table
8 where object_id is not null
9 and object_id between 1000 and 2000
10 /
1001 rows created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> select sn.name, ms.value
2 from v$statname sn, v$mystat ms
3 where sn.statistic#=ms.statistic#
4 and sn.name like '%leaf node %'
5 order by 1
6 /
NAME VALUE
---------------------------------------------------------------- ----------
leaf node 90-10 splits 0
leaf node splits 25
SQL>
SQL> exec dbms_stats.gather_table_stats(-
> '','DEMO_IBS_RK',estimate_percent=>100,cascade=>TRUE);
PL/SQL procedure successfully completed.
SQL> select num_rows, distinct_keys, blevel, leaf_blocks,
2 distinct_keys/leaf_blocks
3 from user_indexes
4 where index_name = 'DEMO_IBS_RK_U1'
5 /
NUM_ROWS DISTINCT_KEYS BLEVEL LEAF_BLOCKS DISTINCT_KEYS/LEAF_BLOCKS
---------- ------------- ---------- ----------- -------------------------
52834 52834 1 429 123.156177
SQL> analyze index demo_ibs_rk_u1 validate structure;
Index analyzed.
SQL> select lf_rows, lf_blks, br_blks, del_lf_rows, pct_used
2 from index_stats;
LF_ROWS LF_BLKS BR_BLKS DEL_LF_ROWS PCT_USED
---------- ---------- ---------- ----------- ----------
52834 429 1 0 64
SQL>
I increment the key by 100,000 and 200,000 for the next two rounds of inserts and create a "gap". However, in my opinion, this does not significantly alter the results. Even if I did a "gapless" insert (as in the first insert), the index block splits would be 50-50.
TEST RUN 2 with a single, numeric column Unique Index.
SQL> drop table demo_ibs_rk purge;
Table dropped.
SQL>
SQL> -- REM create the demo table
SQL> create table demo_ibs_rk (
2 employee_id number not null,
3 country_name varchar2(10) not null,
4 dept_name varchar2(18) not null,
5 employee_name varchar2(128) not null,
6 join_date date)
7 /
Table created.
SQL>
SQL> -- create the index with a PCTFREE of 1 to pack it tightly
SQL> -- BUT CREATE IT AS A REVERSE KEY INDEX
SQL> create unique index demo_ibs_rk_u1 on
2 demo_ibs_rk (employee_id)
3 reverse pctfree 1;
Index created.
SQL>
SQL> drop table source_table;
Table dropped.
SQL> create table source_table
2 as select * from dba_objects
3 where 1=2;
Table created.
SQL> insert /*+ APPEND */ into source_table
2 select * from dba_objects
3 where object_id is not null;
50833 rows created.
SQL> commit;
Commit complete.
SQL> select max(object_id) from source_table;
MAX(OBJECT_ID)
--------------
58322
SQL>
SQL>
SQL> -- REM create a new session and run an insert
SQL> -- then check the statistics for the insert
SQL> connect hemant/hemant
Connected.
SQL> insert into demo_ibs_rk
2 select object_id, substr(owner,1,10),
3 substr(object_type,1,18),
4 rpad(object_name,20,dbms_random.string('X',3)),
5 created
6 from source_table
7 where object_id is not null
8 order by object_id
9 /
50833 rows created.
SQL> commit;
Commit complete.
SQL>
SQL> select sn.name, ms.value
2 from v$statname sn, v$mystat ms
3 where sn.statistic#=ms.statistic#
4 and sn.name like '%leaf node %'
5 order by 1
6 /
NAME VALUE
---------------------------------------------------------------- ----------
leaf node 90-10 splits 0
leaf node splits 127
SQL>
SQL> exec dbms_stats.gather_table_stats(-
> '','DEMO_IBS_RK',estimate_percent=>100,cascade=>TRUE);
PL/SQL procedure successfully completed.
SQL> select num_rows, distinct_keys, blevel, leaf_blocks,
2 distinct_keys/leaf_blocks
3 from user_indexes
4 where index_name = 'DEMO_IBS_RK_U1'
5 /
NUM_ROWS DISTINCT_KEYS BLEVEL LEAF_BLOCKS DISTINCT_KEYS/LEAF_BLOCKS
---------- ------------- ---------- ----------- -------------------------
50833 50833 1 128 397.132813
SQL> analyze index demo_ibs_rk_u1 validate structure;
Index analyzed.
SQL> select lf_rows, lf_blks, br_blks, del_lf_rows, pct_used
2 from index_stats;
LF_ROWS LF_BLKS BR_BLKS DEL_LF_ROWS PCT_USED
---------- ---------- ---------- ----------- ----------
50833 128 1 0 74
SQL>
SQL> REM #################
SQL> REM OBSERVATION !
SQL> REM The first set of block creations are all from Block Splits.
SQL> REM And all of these are now 50-50 Splits !
SQL> REM #################
SQL>
SQL>
SQL>
SQL> -- REM run another set of inserts
SQL> -- REM we now simulate a Row-By-Row Insert that would happen
SQL> -- REM for creating new Employees
SQL> connect hemant/hemant
Connected.
SQL>
SQL> declare
2 i number;
3
4 begin
5 for i in 1..1000
6 loop
7 insert into demo_ibs_rk
8 select object_id+100000+i,
9 substr(owner,1,10),
10 substr(object_type,1,18),
11 rpad(object_name,20,dbms_random.string('X',3)),
12 created+vsize(object_name)
13 from source_table
14 where object_id is not null
15 and object_id = 1000+i;
16 commit;
17 end loop;
18 end;
19 /
PL/SQL procedure successfully completed.
SQL>
SQL> select sn.name, ms.value
2 from v$statname sn, v$mystat ms
3 where sn.statistic#=ms.statistic#
4 and sn.name like '%leaf node %'
5 order by 1
6 /
NAME VALUE
---------------------------------------------------------------- ----------
leaf node 90-10 splits 0
leaf node splits 0
SQL>
SQL> exec dbms_stats.gather_table_stats(-
> '','DEMO_IBS_RK',estimate_percent=>100,cascade=>TRUE);
PL/SQL procedure successfully completed.
SQL> select num_rows, distinct_keys, blevel, leaf_blocks,
2 distinct_keys/leaf_blocks
3 from user_indexes
4 where index_name = 'DEMO_IBS_RK_U1'
5 /
NUM_ROWS DISTINCT_KEYS BLEVEL LEAF_BLOCKS DISTINCT_KEYS/LEAF_BLOCKS
---------- ------------- ---------- ----------- -------------------------
51833 51833 1 128 404.945313
SQL> analyze index demo_ibs_rk_u1 validate structure;
Index analyzed.
SQL> select lf_rows, lf_blks, br_blks, del_lf_rows, pct_used
2 from index_stats;
LF_ROWS LF_BLKS BR_BLKS DEL_LF_ROWS PCT_USED
---------- ---------- ---------- ----------- ----------
51833 128 1 0 75
SQL>
SQL>
SQL>
SQL> -- REM run another set of inserts
SQL> -- REM we now run a bulk insert again !
SQL> connect hemant/hemant
Connected.
SQL>
SQL> insert into demo_ibs_rk
2 select object_id+200000,
3 substr(owner,1,10),
4 substr(object_type,1,18),
5 rpad(object_name,20,dbms_random.string('X',3)),
6 created+vsize(object_name)
7 from source_table
8 where object_id is not null
9 and object_id between 1000 and 2000
10 /
1001 rows created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> select sn.name, ms.value
2 from v$statname sn, v$mystat ms
3 where sn.statistic#=ms.statistic#
4 and sn.name like '%leaf node %'
5 order by 1
6 /
NAME VALUE
---------------------------------------------------------------- ----------
leaf node 90-10 splits 0
leaf node splits 0
SQL>
SQL> exec dbms_stats.gather_table_stats(-
> '','DEMO_IBS_RK',estimate_percent=>100,cascade=>TRUE);
PL/SQL procedure successfully completed.
SQL> select num_rows, distinct_keys, blevel, leaf_blocks,
2 distinct_keys/leaf_blocks
3 from user_indexes
4 where index_name = 'DEMO_IBS_RK_U1'
5 /
NUM_ROWS DISTINCT_KEYS BLEVEL LEAF_BLOCKS DISTINCT_KEYS/LEAF_BLOCKS
---------- ------------- ---------- ----------- -------------------------
52834 52834 1 128 412.765625
SQL> analyze index demo_ibs_rk_u1 validate structure;
Index analyzed.
SQL> select lf_rows, lf_blks, br_blks, del_lf_rows, pct_used
2 from index_stats;
LF_ROWS LF_BLKS BR_BLKS DEL_LF_ROWS PCT_USED
---------- ---------- ---------- ----------- ----------
52834 128 1 0 76
SQL>
Since the index is on a single NUMBER column, it is much smaller than the previous test run. The first set of 58thousand values creates only 128 leaf blocks and the next two sets of 1000 values each are able to find space within the existing leaf blocks because they are not "right-most" values occurring too frequently. So, the next two round do not create any block splits (I would get block splits if I insert more rows). This index is better packed as well at 76% after three rounds.
.
.
.
No comments:
Post a Comment