ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 全局索引有缺陷啊

全局索引有缺陷啊

原创 Linux操作系统 作者:wei-xh 时间:2011-03-10 16:11:05 0 删除 编辑

老熊博客里的例子

SQL> create table t1 ( a int, b varchar2(300)) partition by range(a)
  2  (
  3   partition p01 values less than (1000),
  4   partition p02 values less than (2000),
  5   partition p03 values less than (3000),
  6   partition p04 values less than (4000),
  7   partition p05 values less than (5000),
  8   partition p06 values less than (6000),
  9   partition p07 values less than (7000),
 10   partition p08 values less than (8000),
 11   partition p09 values less than (9000),
 12   partition p10 values less than (10000),
 13   partition p11 values less than (11000),
 14   partition p12 values less than (12000),
 15   partition p13 values less than (13000),
 16   partition p14 values less than (14000),
 17   partition p15 values less than (15000),
 18   partition p16 values less than (16000),
 19   partition p17 values less than (17000),
 20   partition p18 values less than (18000),
 21   partition p19 values less than (19000),
 22   partition p20 values less than (20000)
 23  )
 24  /

表已创建。

SQL> insert into t1 select rownum,lpad('x',200,'x') from dual connect by rownum<20000;

已创建19999行。

SQL> commit;

提交完成。

SQL> insert /*+ append */ into t1 select * from t1;

已创建19999行。

SQL> commit;

提交完成。

SQL> insert /*+ append */ into t1 select * from t1;

已创建39998行。

SQL> commit;

提交完成。

SQL> insert /*+ append */ into t1 select * from t1;

已创建79996行。

SQL> commit;

提交完成。

SQL> insert /*+ append */ into t1 select * from t1;

已创建159992行。

SQL> commit;

提交完成。

SQL> insert /*+ append */ into t1 select * from t1;

已创建319984行。

SQL> commit;

提交完成。

SQL> drop index t1_idx;

索引已丢弃。

SQL> create index t1_idx on t1(a) ;

索引已创建。

SQL> exec dbms_stats.gather_table_stats(user,'T1',
    method_opt=>'for all columns size 1',cascade=>true);

PL/SQL 过程已成功完成。

SQL> set arraysize 1000
SQL> set autot traceonly
SQL> select * from t1 where a=1000;

已选择32行。

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=59 Card=32 Bytes=652
          8)

   1    0   TABLE ACCESS (FULL) OF 'T1' (Cost=59 Card=32 Bytes=6528)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        948  consistent gets
          0  physical reads
          0  redo size
        828  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         32  rows processed

SQL> select /*+ index(t1) */ * from t1 where a=1000;

已选择32行。

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=644 Card=32 Bytes=65
          28)

   1    0   TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'T1' (Cost=644 Car
          d=32 Bytes=6528)

   2    1     INDEX (RANGE SCAN) OF 'T1_IDX' (NON-UNIQUE) (Cost=4 Card
          =640)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         37  consistent gets
          0  physical reads
          0  redo size
        828  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         32  rows processed

cost =
        blevel +
        ceiling(leaf_blocks * effective index selectivity) +
        ceiling(clustering_factor * effective table selectivity)

10053看下


***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: T1  Alias: T1  Partition [1]
    #Rows: 32000  #Blks:  945  AvgRowLen:  204.00
    #Rows: 32000  #Blks:  945  AvgRowLen:  204.00
Index Stats::
  Index: T1_IDX  Col#: 1
    LVLS: 2  #LB: 1739  #DK: 20126  LB/K: 1.00  DB/K: 31.00  CLUF: 639968.00
Access path analysis for T1
***************************************
SINGLE TABLE ACCESS PATH
  Single Table Cardinality Estimation for T1[T1]
  Table: T1  Alias: T1
    Card: Original: 32000.000000  Rounded: 32  Computed: 32.00  Non Adjusted: 32.00
  Access Path: TableScan
    Cost:  258.90  Resp: 258.90  Degree: 0
      Cost_io: 258.00  Cost_cpu: 13130401
      Resp_io: 258.00  Resp_cpu: 13130401
  Access Path: index (AllEqRange)
    Index: T1_IDX
    resc_io: 644.00  resc_cpu: 3145286
    ix_sel: 0.001000  ix_sel_with_filters: 0.001000
    Cost: 644.22  Resp: 644.22  Degree: 1
  Best:: AccessPath: TableScan
         Cost: 258.90  Degree: 1  Resp: 258.90  Card: 32.00  Bytes

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22034023/viewspace-688989/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
Oracle ACE组成员,DBGeeK用户组发起人。曾在DTCC、ORACLE技术嘉年华、Gdevops等公开场合做过数据库技术专题分享,2017年应Oracle邀请在世界最大的数据库会议OOW上做技术分享。组织翻译了《拨云见日,解密Oracle ASM内核》一书。

注册时间:2009-07-04

  • 博文量
    422
  • 访问量
    2315621