索引为什么比表大--简单的CaseStudy
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.