本文从不绑定变量与绑定变量两种情况讨论柱状图的作用。

一、不绑定变量的情况：

大家可以考虑下面的数据：

 

SQL> select owner,count(1) from th group by owner;

OWNER COUNT(1)
------------------------------ ----------
SUK 1
SYS 36216
SYSTEM 1

其中，在表的OWENR上建立有一个索引。

做完普通分析后，再来执行查询。

 

SQL> analyze table th compute statistics;

Table analyzed

SQL> select * from th where owner='SYS';

已选择36216行。



Execution Plan
-------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=50 Card=12073 Bytes=
881329)
1 0 TABLE ACCESS (FULL) OF 'TH' (Cost=50 Card=12073 Bytes=8813
29)


Statistics
-------------------------------------------
0 recursive calls
0 db block gets
2894 consistent gets
0 physical reads
0 redo size
2045535 bytes sent via SQL*Net to client
27057 bytes received via SQL*Net from client
2416 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
36216 rows processed


SQL> select * from th where owner='SUK';


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=50 Card=12073 Bytes=
881329)

1 0 TABLE ACCESS (FULL) OF 'TH' (Cost=50 Card=12073 Bytes=8813
29)


Statistics
--------------------------------------
0 recursive calls
0 db block gets
513 consistent gets
0 physical reads
0 redo size
1133 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)
1 rows processed

 

只需返回一条数据，但做了全表扫描，这是不合理的执行计划。因为，它只是知道 owner列有三个不同的值，但Oracle不知道每个不同的owner分别有多少记录，Oracle默认为这些数据的分布是完全均匀的，所以，当用 owner作条件时，Oracle会认为会返回总记录的三分之一（从执行计划中的Card=12073可以看出来）

 

对表TH生成柱状图后在做同样的查询:

 

SQL> analyze table th compute statistics for 

table for all indexes for all indexed columns;

Table analyzed

SQL> select * from th where owner='SYS';

已选择36216行。


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=50 Card=36216 Bytes=
2643768)

1 0 TABLE ACCESS (FULL) OF 'TH' (Cost=50 Card=36216 Bytes=2643
768)


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2894 consistent gets
0 physical reads
0 redo size
2045535 bytes sent via SQL*Net to client
27057 bytes received via SQL*Net from client
2416 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
36216 rows processed

SQL> select * from th where owner='SUK';


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=73)

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TH' (Cost=2 Card=1 Bytes
=73)

2 1 INDEX (RANGE SCAN) OF 'IDX_TH' (NON-UNIQUE) (Cost=1 Card
=1)


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
1133 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)
1 rows processed

可见，生成了柱状图后，Oracle会根据数据的实际分布情况

选择合适的执行计划，提高性能。


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