之前一直以为收不收集直方图有两个条件:
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