全域性索引有缺陷啊

老熊部落格裡的例子

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