直方图；索引

之前一直以为收不收集直方图有两个条件：

1.有索引

2.列值分布不均匀

第二点毋庸置疑，实验下第一点：

实验环境

CREATE TABLE TEST AS SELECT * FROM DBA_OBJECTS;

CREATE TABLE TEST2 AS SELECT * FROM DBA_OBJECTS;

UPDATE TEST2 SET OBJECT_ID = 11111 WHERE ROWNUM <=50000;

CREATE INDEX IND_TEST_OBJECT_NAME ON TEST(OBJECT_NAME);

收集统计信息，但是不收集直方图：

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'SCOTT',TABNAME=>'TEST2',ESTIMATE_PERCENT=>100,cascade=>true,method_opt => 'for all columns size 1');

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'SCOTT',TABNAME=>'TEST',ESTIMATE_PERCENT=>100,cascade=>true,method_opt => 'for all columns size 1');

SQL> select a.*,b.* from scott.test a,scott.test2 b where a.object_name=b.object_name and b.object_id =11111;

94379 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2409697593

-----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                      |     5 |   975 |   307   (1)| 00:00:04 |
|   1 |  NESTED LOOPS                |                      |       |       |            |          |
|   2 |   NESTED LOOPS               |                      |     5 |   975 |   307   (1)| 00:00:04 |
|*  3 |    TABLE ACCESS FULL         | TEST2                |     3 |   291 |   298   (1)| 00:00:04 |
|*  4 |    INDEX RANGE SCAN          | IND_TEST_OBJECT_NAME |     2 |       |     2   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| TEST                 |     2 |   196 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("B"."OBJECT_ID"=11111)
   4 - access("A"."OBJECT_NAME"="B"."OBJECT_NAME")


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
     122585  consistent gets
        726  physical reads
          0  redo size
    6004029  bytes sent via SQL*Net to client
      69724  bytes received via SQL*Net from client
       6293  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      94379  rows processed

居然走了NL，不过没办法，根据统计信息ROWS估算才3。当然就走NL。

SQL>  select round(a/b) row_numbers from (select count(*) a,count(distinct object_id) b from test2);

ROW_NUMBERS
-----------
          3

收集直方图

SQL> BEGIN
  2    DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',
  3                                  tabname => 'TEST2',
  4                                  estimate_percent => 100,
  5                                  method_opt => 'for all columns size skewonly',
  6                                  no_invalidate => FALSE,
  7                                  degree => 1,
  8                                  cascade => TRUE);
  9  END;
 10  /

PL/SQL procedure successfully completed.

SQL> select a.*,b.* from scott.test a,scott.test2 b where a.object_name=b.object_name and b.object_id =11111;

94379 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 701505764

------------------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       | 80526 |    14M|       |  1247   (1)| 00:00:15 |
|*  1 |  HASH JOIN         |       | 80526 |    14M|  5328K|  1247   (1)| 00:00:15 |
|*  2 |   TABLE ACCESS FULL| TEST2 | 49987 |  4735K|       |   299   (1)| 00:00:04 |
|   3 |   TABLE ACCESS FULL| TEST  | 74685 |  7147K|       |   299   (1)| 00:00:04 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("A"."OBJECT_NAME"="B"."OBJECT_NAME")
   2 - filter("B"."OBJECT_ID"=11111)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       8271  consistent gets
        339  physical reads
          0  redo size
    5789208  bytes sent via SQL*Net to client
      69724  bytes received via SQL*Net from client
       6293  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      94379  rows processed

从这里来看，即使说没有索引的列，收集直方图对于性能的提升也是很高的。