gistfile1.txt

create table SCHEMA_test.big_table
as
select rownum id,
               OWNER, OBJECT_NAME, SUBOBJECT_NAME,
               OBJECT_ID, DATA_OBJECT_ID,
               OBJECT_TYPE, CREATED, LAST_DDL_TIME,
               TIMESTAMP, STATUS, TEMPORARY,
               GENERATED, SECONDARY
  from all_objects a
 where 1=0
/
alter table SCHEMA_test.big_table nologging;

declare
    l_cnt number;
    l_rows number := 10000000;
begin
    insert /*+ append */
    into SCHEMA_test.big_table
    select rownum,
               OWNER, OBJECT_NAME, SUBOBJECT_NAME,
                nvl(OBJECT_ID,1), DATA_OBJECT_ID,
               OBJECT_TYPE, CREATED, nvl(LAST_DDL_TIME,sysdate),
               TIMESTAMP, STATUS, TEMPORARY,
               GENERATED, SECONDARY
      from dba_objects a;

    l_cnt := sql%rowcount;

    commit;

    while (l_cnt < l_rows)
    loop
        insert /*+ APPEND */ into SCHEMA_test.big_table
        select rownum+l_cnt,
               OWNER, OBJECT_NAME, SUBOBJECT_NAME,
               nvl(OBJECT_ID,1), DATA_OBJECT_ID,
               OBJECT_TYPE, CREATED,  nvl(LAST_DDL_TIME,sysdate),
               TIMESTAMP, STATUS, TEMPORARY,
               GENERATED, SECONDARY
          from SCHEMA_test.big_table
         where rownum <= l_rows-l_cnt;
        l_cnt := l_cnt + sql%rowcount;
        commit;
    end loop;
end;
/



Create index SCHEMA_test.idx1 on SCHEMA_test.big_table(created) ;

analyze index SCHEMA_test.idx1 validate structure;

NAME	HEIGHT	BLOCKS	OPT_CMPR_COUNT	OPT_CMPR_PCTSAVE
IDX1	3	27008	1	41

alter index  SCHEMA_test.idx1 rebuild compress 1;


alter index  SCHEMA_test.idx1 rebuild compress advanced low; 


*********

alter session set current_schema=SCHEMA_test;

*************** Range scan Test*************

DECLARE
thesql VARCHAR2(4000);
hasval NUMBER(10,0);
BEGIN

For I in 1..1000
Loop
--dbms_output.put_line('loop '|| i);
thesql := 'select  count(*) FROM big_table i WHERE i.created =sysdate'||-i ;
execute immediate (thesql) into hasval;
--dbms_output.put_line('hasval '|| hasval);
END Loop;
   
END;
/

*************** table full scan Test*************

DECLARE
thesql VARCHAR2(4000);
hasval NUMBER(10,0);
BEGIN

For I in 1..1000
Loop
--dbms_output.put_line('loop '|| i);
thesql := 'select /*+full(i)*/  count(*) FROM big_table i WHERE i.created = sysdate'|| -i ;
execute immediate (thesql) into hasval;
--dbms_output.put_line('hasval '|| hasval);
END Loop;
   
END;
/

*************** index full scan Test*************

DECLARE
thesql VARCHAR2(4000);
hasval NUMBER(10,0);
BEGIN

For I in 1..1000
Loop
--dbms_output.put_line('loop '|| i);
thesql := 'select /*+index_ffs(i, idx1)*/  count(*) FROM big_table i WHERE i.created =sysdate '|| -i ;
execute immediate (thesql) into hasval;
--dbms_output.put_line('hasval '|| hasval);
END Loop;
   
END;
/

*************** Insert Test*************

declare
begin

       for i in 1..1000    loop
        insert  into SCHEMA_test.big_table
        select rownum+i,
               OWNER, OBJECT_NAME, SUBOBJECT_NAME,
               nvl(OBJECT_ID,1), DATA_OBJECT_ID,
               OBJECT_TYPE, CREATED,  nvl(LAST_DDL_TIME,sysdate),
               TIMESTAMP, STATUS, TEMPORARY,
               GENERATED, SECONDARY
          from SCHEMA_test.big_table
         where rownum <= 100;
                commit;
    end loop;
end;
/

*************** Update Test*************

declare
begin
       for i in 1..100    loop
        Update SCHEMA_test.big_table
		SET created=sysdate+i
         where rownum <= 1000;
                commit;
    end loop;
end;
/