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 NAMELF_ROWSLF_BLKS DEL_LF_ROWS USED_SPACE BLKS_GETS_PER_ACCESS
---------- ---------- -------------------- ---------- ---------- ----------- ---------- --------------------
2292 IDX_T100000222 015915223
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 NAMELF_ROWSLF_BLKS DEL_LF_ROWS USED_SPACE BLKS_GETS_PER_ACCESS
---------- ---------- -------------------- ---------- ---------- ----------- ---------- --------------------
2957 IDX_T26180364316180341775043.1545075
1 row selected.
SQL
SQL analyze table testforind compute statistics;
analy
Table analyzed.
SQL ze index idx_t compute statistics;
Index analyzed.
SQLcol segment_name format a40
SQL l
1* SELECT SEGMENT_NAME,BYTES,BLOCKS FROM USER_SEGMENTS WHERE SEGMENT_NAME IN ('TESTFORIND','IDX_T')
SQL /
SEGMENT_NAMEBYTES BLOCKS
---------------------------------------- ---------- ----------
TESTFORIND2392064292
IDX_T 7839744957
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 NAMELF_ROWSLF_BLKS DEL_LF_ROWS USED_SPACE BLKS_GETS_PER_ACCESS
---------- ---------- -------------------- ---------- ---------- ----------- ---------- --------------------
2256 IDX_T100000222 015915223
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 NAMELF_ROWSLF_BLKS DEL_LF_ROWS USED_SPACE BLKS_GETS_PER_ACCESS
---------- ---------- -------------------- ---------- ---------- ----------- ---------- --------------------
2512 IDX_T133333429 333332126982 3.14516176
1 row selected.
