Oracle性能诊断艺术-学习笔记（索引访问方式） - Oracle-fans - 博客园

1.0

测试表

CREATE TABLE t (

  id NUMBER,

  d1 DATE,

  n1 NUMBER,

  n2 NUMBER,

  n3 NUMBER,

  n4 NUMBER,

  n5 NUMBER,

  n6 NUMBER,

  c1 VARCHAR2(20),

  c2 VARCHAR2(20),

  pad VARCHAR2(4000),

  CONSTRAINT t_pk PRIMARY KEY (id)

);

2.0

execute dbms_random.seed(0)

3.0

INSERT INTO t

SELECT rownum AS id,

       trunc(to_date('2007-01-01','yyyy-mm-dd')+rownum/27.4) AS d1,

       nullif(1+mod(rownum,19),10) AS n1,

       nullif(1+mod(rownum,113),10) AS n2,

       nullif(1+mod(rownum,61),10) AS n3,

       nullif(1+mod(rownum,19),10) AS n4,

       nullif(1+mod(rownum,113),10) AS n5,

       nullif(1+mod(rownum,61),10) AS n6,

       dbms_random.string('p',20) AS c1,

       dbms_random.string('p',20) AS c2,

       dbms_random.string('p',255) AS pad

FROM dual

CONNECT BY level <= 10000

ORDER BY dbms_random.value;

4.0

CREATE INDEX i_n1 ON t (n1);

CREATE INDEX i_n2 ON t (n2);

CREATE INDEX i_n3 ON t (n3);

CREATE INDEX i_n123 ON t (n1, n2, n3);

CREATE BITMAP INDEX i_bt_n4 ON t (n4);

CREATE BITMAP INDEX i_bt_n5 ON t (n5);

CREATE BITMAP INDEX i_bt_n6 ON t (n6);

CREATE INDEX i_c1 ON t (c1);

CREATE BITMAP INDEX i_bt_c2 ON t (c2);

5.0

BEGIN

  dbms_stats.gather_table_stats(

    ownname          => user,

    tabname          => 'T',

    estimate_percent => 100,

    method_opt       => 'for all columns size skewonly',

    cascade          => TRUE

  );

END;

/