索引为什么比表大 -- 简单的Case Study

2/9/2008来源:Oracle教程人气:5673


  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.