SQL> @testcase2
SQL> CREATE SEQUENCE SEQ
2 /
Sequence created.
SQL>
SQL> DROP TABLE TESTFORIND
2 /
Table dropped.
SQL>
SQL> CREATE TABLE TESTFORIND(ID NUMBER,VALUE VARCHAR2(20));
Table created.
SQL>
SQL> BEGIN FOR X IN 1..100000 LOOP INSERT INTO TESTFORIND VALUES(X,CHR(X)'XXX'); END LOOP; END;
2 /
PL/SQL PRocedure sUCcessfully completed.
SQL>
SQL> CREATE INDEX IDX_T ON TESTFORIND(ID)
2 /
Index created.
SQL>
SQL> ANALYZE INDEX IDX_T VALIDATE STRUCTURE
2 /
Index analyzed.
SQL>
SQL> select height,blocks,name,LF_ROWS,LF_BLKS,DEL_LF_ROWS,USED_SPACE,BLKS_GETS_PER_access from index_stats
2 /
HEIGHT BLOCKS NAME LF_ROWS LF_BLKS DEL_LF_ROWS USED_SPACE BLKS_GETS_PER_ACCESS
---------- ---------- -------------------- ---------- ---------- ----------- ---------- --------------------
2 292 IDX_T 100000 222 0 1591522 3
1 row selected.
SQL>
SQL> begin for x in 1..100000 loop update testforind set id=id+seq.nextval where id=x; end loop; end;
2 /
PL/SQL procedure successfully completed.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> ANALYZE INDEX IDX_T VALIDATE STRUCTURE
2 /
Index analyzed.
SQL>
SQL> select height,blocks,name,LF_ROWS,LF_BLKS,DEL_LF_ROWS,USED_SPACE,BLKS_GETS_PER_ACCESS from index_stats
2 /
HEIGHT BLOCKS NAME LF_ROWS LF_BLKS DEL_LF_ROWS USED_SPACE BLKS_GETS_PER_ACCESS
---------- ---------- -------------------- ---------- ---------- ----------- ---------- --------------------
2 957 IDX_T 261803 643 161803 4177504 3.1545075
1 row selected.
SQL>
SQL> analyze table testforind compute statistics;
analy
Table analyzed.
SQL> ze index idx_t compute statistics;
Index analyzed.
SQL> col segment_name format a40
SQL> l
1* SELECT SEGMENT_NAME,BYTES,BLOCKS FROM USER_SEGMENTS WHERE SEGMENT_NAME IN ('TESTFORIND','IDX_T')
SQL> /
SEGMENT_NAME BYTES BLOCKS
---------------------------------------- ---------- ----------
TESTFORIND 2392064 292
IDX_T 7839744 957
2 rows selected
SQL> conn scott/tiger
Connected.
SQL> @testcase1
SQL> DROP TABLE TESTFORIND
2 /
Table dropped.
SQL>
SQL> CREATE TABLE TESTFORIND(ID NUMBER,VALUE VARCHAR2(20));
Table created.
SQL>
SQL> BEGIN FOR X IN 1..100000 LOOP INSERT INTO TESTFORIND VALUES(X,CHR(X)'XXX'); END LOOP; END;
2 /
PL/SQL procedure successfully completed.
SQL>
SQL> CREATE INDEX IDX_T ON TESTFORIND(ID)
2 /
Index created.
SQL>
SQL> ANALYZE INDEX IDX_T VALIDATE STRUCTURE
2 /
Index analyzed.
SQL>
SQL> select height,blocks,name,LF_ROWS,LF_BLKS,DEL_LF_ROWS,USED_SPACE,BLKS_GETS_PER_ACCESS from index_stats
2 /
HEIGHT BLOCKS NAME LF_ROWS LF_BLKS DEL_LF_ROWS USED_SPACE BLKS_GETS_PER_ACCESS
---------- ---------- -------------------- ---------- ---------- ----------- ---------- --------------------
2 256 IDX_T 100000 222 0 1591522 3
1 row selected.
SQL>
SQL> BEGIN FOR X IN 1..100000 LOOP IF MOD(X,3)=0 THEN UPDATE TESTFORIND SET ID=ID+100000 WHERE ID=X ;END IF; END LOOP; END;
2 /
PL/SQL procedure successfully completed.
SQL>
SQL> COMMIT;
Commit complete.
SQL>
SQL> ANALYZE INDEX IDX_T VALIDATE STRUCTURE
2 /
Index analyzed.
SQL>
SQL> select height,blocks,name,LF_ROWS,LF_BLKS,DEL_LF_ROWS,USED_SPACE,BLKS_GETS_PER_ACCESS from index_stats
2 /
HEIGHT BLOCKS NAME LF_ROWS LF_BLKS DEL_LF_ROWS USED_SPACE BLKS_GETS_PER_ACCESS
---------- ---------- -------------------- ---------- ---------- ----------- ---------- --------------------
2 512 IDX_T 133333 429 33333 2126982 3.14516176
1 row selected.
