[20120112]count的有关问题.txt

  • 时间:
  • 浏览:2
  • 来源:uu快3计划师_uu快3app苹果_全天计划

SQL>  select /*+ gather_plan_statistics */ count(*) from t3;

-------------------------------------

NLSRTL Version 11.2.0.1.0 - Production

SQL> select * from table(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST PEEKED_BINDS'));

     40000

不到 记得在itpub上与别人趋于稳定激烈争论,就让不到 的事情了.当时争论的焦点是认为count(*),不一定选取主键索引,选取非空的字段,有时候索引占用空间最小的索引.

PLAN_TABLE_OUTPUT

SQL> insert into t3 select rownum id1,ceil(rownum/4000) id2 ,'test' name from dual connect by level SQL> commit ;

select /*+ gather_plan_statistics */ count(id2) from t3

Plan hash value: 1276951659

-------------------------------------

--能能 发现不到 就与count(*)的执行计划相同!

SQL> select * from table(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST PEEKED_BINDS'));

SQL_ID  f0jazcgr65tmq, child number 0

|   0 | SELECT STATEMENT   |      |      1 |        |      1 |00:00:00.01 |      31 |

PLAN_TABLE_OUTPUT

------------------------------ -------- ---------- -----------

----------------------------------------------------------------------------------------------------

|   3 |    BITMAP INDEX FAST FULL SCAN| I_T3_ID2 |      1 |        |     10 |00:00:00.01 |       4 |

|   1 |  SORT AGGREGATE       |           |      1 |      1 |      1 |00:00:00.01 |      22 |

3.测试2:

--能能 发现I_T3_name是因为分析重复值只是,索引占用空间相对PK_T3小只是.

PL/SQL Release 11.2.0.1.0 - Production

-----------------------------------------------------------------------------------------

INDEX_NAME                     COMPRESS     BLEVEL LEAF_BLOCKS

--是因为分析修改id为非NULL

---------------------------------------------------------------------------------------------

--修改id2为NULL,建立位图索引.看看情况报告怎样呢?

SQL> exec dbms_stats.gather_table_stats(NULL,'T3',Method_Opt=>'FOR ALL COLUMNS SIZE 1');

--------------------------------------

SQL> exec dbms_stats.gather_table_stats(NULL,'T3',Method_Opt=>'FOR ALL COLUMNS SIZE 1');

SQL> create unique index pk_t3 on t3(id1);

前者第2步执行选取的是BITMAP CONVERSION TO ROWIDS,而后者执行选取的是BITMAP CONVERSION COUNT.

SQL_ID  f0jazcgr65tmq, child number 0

|   0 | SELECT STATEMENT              |          |      1 |        |      1 |00:00:00.01 |       4 |

PLAN_TABLE_OUTPUT

select /*+ gather_plan_statistics */ count(id2) from t3

-------------------------------------

-----------------------------------------------------------------------------------------

|   1 |  SORT AGGREGATE       |       |      1 |      1 |      1 |00:00:00.01 |      25 |

----------

TNS for Linux: Version 11.2.0.1.0 - Production

-------------------------------------------------------------------------------------

| Id  | Operation                     | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |

I_T3_NAME                      ENABLED           1          16

select /*+ gather_plan_statistics */ count(id2) from t3

SQL> alter table t3 modify(name  not null);

I_T3_ID2                       DISABLED          0           1

|   0 | SELECT STATEMENT      |       |      1 |        |      1 |00:00:00.01 |      25 |

     40000

----------------------------------------------------------------------------------------------------

COUNT(ID2)

|   2 |   INDEX FAST FULL SCAN| I_T3_NAME |      1 |  40000 |  40000 |00:00:00.01 |      22 |

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

----------

----------

Plan hash value: 463314188

-------------------------------------

select /*+ gather_plan_statistics */ count(id2) from t3

PLAN_TABLE_OUTPUT

1.建立测试表:

5.测试4:

SQL> exec dbms_stats.gather_table_stats(NULL,'T3',Method_Opt=>'FOR ALL COLUMNS SIZE 1');

|   1 |  SORT AGGREGATE               |          |      1 |      1 |      1 |00:00:00.01 |       4 |

Table altered.

--这时能能 发现位图索引i_t3_id2占用空间最小.

PK_T3                          DISABLED          1          18

SQL> alter table t3 add constraint pk_t3 primary key (id1) enable validate;

SQL> CREATE INDEX i_t3_name ON T3(NAME) COMPRESS 1;

|   0 | SELECT STATEMENT              |          |      1 |        |      1 |00:00:00.01 |       4 |

select /*+ gather_plan_statistics */ count(*) from t3

SQL_ID  f0jazcgr65tmq, child number 0

PK_T3                          DISABLED          1          18

-------------------------------------

--修改name为非NULL,建立name索引,选取索引压缩土妙招.

SQL_ID  f0jazcgr65tmq, child number 0

----------------------------------------------------------------------------------------------------

     40000

----------------------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT

| Id  | Operation             | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |

SQL> create table t3 (id1 number,id2 number,name varchar2(20));

----------

-----------------------------------------------------------------------------------------

I_T3_NAME                      ENABLED           1          16

-------------------------------------

|   2 |   BITMAP CONVERSION TO ROWIDS |          |      1 |  40000 |  40000 |00:00:00.01 |       4 |

--能能 发现即使count是id2,是因为分析id2是not null,oracle能能 选取PK_T3(id1的索引)的INDEX FAST FULL SCAN.

|   2 |   TABLE ACCESS FULL| T3   |      1 |  40000 |  40000 |00:00:00.01 |      31 |

--能能 发现位图索引的特殊土妙招,能能 记录NULL值,执行计划能能 使用位图索引I_T3_ID2.

     40000

Plan hash value: 1276951659

| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |

----------

|   1 |  SORT AGGREGATE               |          |      1 |      1 |      1 |00:00:00.01 |       4 |

SQL> select * from v$version;

|   2 |   BITMAP CONVERSION COUNT     |          |      1 |  40000 |     10 |00:00:00.01 |       4 |

SQL> select index_name,compression,blevel,leaf_blocks from user_indexes where table_name='T3';

|   0 | SELECT STATEMENT      |           |      1 |        |      1 |00:00:00.01 |      22 |

-------------------------------------

----------------------------------------------------------------------------------------------------

|   2 |   INDEX FAST FULL SCAN| PK_T3 |      1 |  40000 |  40000 |00:00:00.01 |      25 |

SQL_ID  fuynj2z4vdjks, child number 0

SQL> select /*+ gather_plan_statistics */ count(id2) from t3;

-------------------------------------

现在写只是例子来说明:

Table created.

|   2 |   INDEX FAST FULL SCAN| PK_T3 |      1 |  40000 |  40000 |00:00:00.01 |      25 |

2.测试1:

-------------------------------------

--------------------------------------------------------------------------------

| Id  | Operation                     | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |

SQL_ID  f0jazcgr65tmq, child number 0

-----------------------------------------------------------------------------------------

  COUNT(*)

Plan hash value: 2432646254

Plan hash value: 1645538918

---------------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT

| Id  | Operation                     | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |

SQL> alter table t3 modify(id2  not null);

SQL> select /*+ gather_plan_statistics */ count(id2) from t3;

INDEX_NAME                     COMPRESS     BLEVEL LEAF_BLOCKS

SQL> select * from table(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST PEEKED_BINDS'));

SQL> alter table t3 modify(id2 not null);

|   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.01 |      31 |

---------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT              |          |      1 |        |      1 |00:00:00.01 |       4 |

4.测试3:

SQL> select /*+ gather_plan_statistics */ count(id2) from t3;

COUNT(ID2)

----------------------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT

| Id  | Operation             | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |

--修改id2为非空:

----------------------------------------------------------------------------------------------------

--对比如下看看怎样?

|   0 | SELECT STATEMENT      |       |      1 |        |      1 |00:00:00.01 |      25 |

|   2 |   BITMAP CONVERSION COUNT     |          |      1 |  40000 |     10 |00:00:00.01 |       4 |

------------------------------ -------- ---------- -----------

--能能 发现执行选取了i_t3_name索引,是因为分析它占用的空间要比PK_T3.

SQL> alter table t3 modify(id2 null);

     40000

COUNT(ID2)

SQL> select * from table(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST PEEKED_BINDS'));

Index created.

COUNT(ID2)

SQL> select index_name,compression,blevel,leaf_blocks from user_indexes where table_name='T3';

SQL> select /*+ gather_plan_statistics */ count(id2) from t3;

SQL> select * from table(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST PEEKED_BINDS'));

--能能 发现是因为分析id2不为NULL,不到选取TABLE ACCESS FULL.

SQL_ID  fuynj2z4vdjks, child number 0

SQL> select * from table(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST PEEKED_BINDS'));

--重新分析表.

SQL> create bitmap index i_t3_id2 on t3(id2);

--咋一看,好像一样的,对比发现Plan hash value不同,仔细看,就能看出不同:

COUNT(ID2)

|   3 |    BITMAP INDEX FAST FULL SCAN| I_T3_ID2 |      1 |        |     10 |00:00:00.01 |       4 |

而顶端count(*),是计数删剪包括空值.说明细节很重要!

--能能 发现使用索引PK_T3,使用INDEX FAST FULL SCAN.是因为分析执行如下:

是因为分析前面count(id),是计数id2为非空的数量,也只是扫描位图索引时不包括NULL.

----------------------------------------------------------------------------------------------------

|   1 |  SORT AGGREGATE       |       |      1 |      1 |      1 |00:00:00.01 |      25 |

SQL> select * from table(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST PEEKED_BINDS'));

-------------------------------------

     40000

Plan hash value: 2432646254

BANNER

Plan hash value: 1323983206

-------------------------------------

| Id  | Operation             | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |

-------------------------------------

----------

-------------------------------------------------------------------------------------

select /*+ gather_plan_statistics */ count(*) from t3

|   1 |  SORT AGGREGATE               |          |      1 |      1 |      1 |00:00:00.01 |       4 |

-------------------------------------

SQL> select /*+ gather_plan_statistics */ count(*) from t3;

-----------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------

SQL> select /*+ gather_plan_statistics */ count(id2) from t3;

----------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------

CORE    11.2.0.1.0      Production

select /*+ gather_plan_statistics */ count(id2) from t3

|   3 |    BITMAP INDEX FAST FULL SCAN| I_T3_ID2 |      1 |        |     10 |00:00:00.01 |       4 |