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.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> 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');

Test case 2:-Second we create procedure to  insert serially.

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 AND TABLE SIZE

SQL> select bytes/1024/1024/1024,segment_name from user_segments where segment_name in (‘I1_T’,’I2_T’,’T’);

SQL> select sum(bytes)/1024/1024/1024 from dba_segments where segment_name=’T’;

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.

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

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.

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.

About the Author

debasis maity

12+ years of rich experience on Database Administrations and on Infrastructure Solution Architect. AWS Certified Solution Architect and Senior Oracle DBA