Test case when index size can grow than table size and effect of rebuild index
Test case preparation
I am going to create test case to simulate different index test cases
Test case 1:-First we create procedure to parallel insert in 10 sessions.The insert query will insert data based on random values generated from select query.Please note I will run gather stats after every test case.
SQL>create table t(id number,sometext varchar2(50),mydate date);
SQL>CREATE OR REPLACE procedure TEST.manyinserts as
v_m number;
begin
for i in 1..1000000 loop
select round(dbms_random.value() * 44444444444) + 1 into v_m from dual t;
insert /*+ new2 */ into t values (v_m, ‘DOES THIS’||dbms_random.value(),sysdate);
end loop;
commit;
end;
/
SQL>CREATE OR REPLACE procedure TEST.manysessions as
v_jobno number:=0;
begin
FOR i in 1..10 LOOP
dbms_job.submit(v_jobno,'manyinserts;', sysdate);
END LOOP;
commit;
end;
/
SQL> create index I1_t1 on t(ID) pctfree 0;
SQL> create index I2_ti on t(SOMETEXT);
SQL> exec dbms_stats.gather_table_stats(user,'T');
SQL> select sum(bytes)/1024/1024/1024 from dba_segments where segment_name='T';
SUM(BYTES)/1024/1024/1024
————————-
.7734375
Index statistics
Col INDEX_NAME format a10;
Select index_name,pct_increase,pct_free,blevel,leaf_blocks,clustering_factor,num_rows from user_indexes where index_name in (‘I1_T’,’I2_T’);

Size of index and table
select bytes/1024/1024/1024,segment_name from user_segments where segment_name in ('I1_T','I2_T','T');
BYTES/1024/1024/1024 SEGMENT_NA
——————– ———-
.265625 I1_T
.8671875 I2_T
.7890625 T
Test case 2:-Second we create procedure to insert serially.
SQL>create table t1(id number,sometext varchar2(50),mydate date);
SQL>CREATE OR REPLACE procedure TEST.manyinserts1 as
v_m number;
begin
for i in 1..10000000 loop
select round(dbms_random.value() * 44444444444) + 1 into v_m from dual t;
insert /*+ new2 */ into t1 values (v_m, ‘DOES THIS’||dbms_random.value(),sysdate);
end loop;
commit;
end;
/
SQL> create index I1_t1 on t1(ID) pctfree 0;
SQL> create index I2_ti on t1(SOMETEXT);
SQL> exec dbms_stats.gather_table_stats(user,’T’);
SQL> Set linesize 300;
INDEX STATISTICS
SQL> Select index_name,pct_increase,pct_free,blevel,leaf_blocks,clustering_factor,num_rows from user_indexes where index_name in (‘I1_T’,’I2_T’);
INDEX_NAME PCT_INCREASE PCT_FREE BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR NUM_ROWS
———- ———— ———- ———- ———– —————– ———-
I1_T 0 2 20296 9402633 9589796
I2_T 10 2 85153 10138784 10138858
INDEX AND TABLE SIZE
SQL> select bytes/1024/1024/1024,segment_name from user_segments where segment_name in (‘I1_T’,’I2_T’,’T’);
BYTES/1024/1024/1024 SEGMENT_NAME
——————– ——————————————————————————————————————————–
.6484375 I2_T
.7890625 T
.1640625 I1_T
SQL> select sum(bytes)/1024/1024/1024 from dba_segments where segment_name=’T’;
SUM(BYTES)/1024/1024/1024
————————-
.7734375
Test case3:-Now I will run some select statement in parallel 10 sessions.I forcefully making index on column ID.THE TABLE I SELECTED WHERE I INSERTED DATA PARALLELY.
CREATE OR REPLACE procedure TEST.manyselect as
v_m number;
begin
for i in 1..100 loop
select count(id) into v_m from t;
end loop;
end;
/
CREATE OR REPLACE procedure TEST.manysessions_select as
v_jobno number:=0;
begin
FOR i in 1..10 LOOP
dbms_job.submit(v_jobno,’manyselect;’, sysdate);
END LOOP;
commit;
end;
/
Exec TEST.manysessions_select;
Now let me check SQL statistics and buffer read from AWR


Test case 4:-Now I will run update in parallel 10 sessions
CREATE OR REPLACE procedure TEST.manyupdates as
v_m number;
begin
for i in 1..100000 loop
select round(dbms_random.value() * 44444444444) + 1 into v_m from dual t;
update t set id=v_m/15*i where rownum<10;
commit;
end loop;
end;
/
CREATE OR REPLACE procedure TEST.manysessions_update as
v_jobno number:=0;
begin
FOR i in 1..10 LOOP
dbms_job.submit(v_jobno,’manyupdates;’, sysdate);
END LOOP;
commit;
end;
/
Exec TEST.manysessions_update;
Table statistics
SQL> select bytes/1024/1024/1024,segment_name from user_segments where segment_name in (‘I1_T’,’I2_T’,’T’);
BYTES/1024/1024/1024 SEGMENT_NA
——————– ———-
.265625 I1_T
.8671875 I2_T
.7890625 T
Index statistics:-

Test case 5:-Now I will run SELECT AGAIN
Exec TEST.manysessions_select;



TEST case 6:-Let me rebuild index on column ID
SQL> alter index I1_t rebuild;
Index altered.
TABLE and INDEX size
SQL> select bytes/1024/1024/1024,segment_name from user_segments where segment_name in (‘I1_T’,’I2_T’,’T’);
BYTES/1024/1024/1024 SEGMENT_NA
——————– ———-
.1875 I1_T
.8671875 I2_T
.7890625 T
Index statistics

Test case 7:-Let me run the select again in parallel
Exec TEST.manysessions_select;



Observations:-
Please note that select query was taking more time (3.07 s) after bulk update which I think was due to temp area usage (see segments by logical read section in awr) .AFter index reuild the query response time (0.30 s)improved.
select query which traverses index I1_T |
Elapsed time/exec |
Buffer gets |
Before bulk update |
0.35 |
35809 |
After bulk update |
3.07 |
41832 |
after rebuild index |
0.30 |
23704 |
Also please note index size I2_T is more in size than table T.Index rebuild reduced the index size though cluster factor remains same.
|
TABLE_SIZE IN GB |
INDEX SIZE I1_T IN GB |
INDEX SIZE I2_T in GB |
CLUSTER FACTOR I1_T |
CLUSTER FACTOR I2_T |
PARALLEL INSERT |
.7890625 |
.265625 |
.8671875 |
9676232 |
10117165 |
SERIAL INSERT |
.7890625 |
.1640625 |
.6484375 |
9402633 |
10138858 |
PARALLEL UPDATE ON COLUMN ID |
.7890625 |
.265625 |
.8671875 |
10262755 |
9399026 |
AFTER REBUILD I1_T |
.7890625 |
.1875 |
.8671875 |
9999904 |
9399026 |
Related
About the Author
12+ years of rich experience on Database Administrations and on Infrastructure Solution Architect.
AWS Certified Solution Architect and Senior Oracle DBA