测试脚本:
create table audience as
select
trunc(dbms_random.value(1,13)) month_no
from
all_objects
where
rownum <= 1200
;
begin
dbms_stats.gather_table_stats(
user,
'audience',
cascade => true,
estimate_percent => null,
method_opt => 'for all columns size 1'
);
end;
/
set autotrace traceonly explain
select count(*) from audience
where month_no = 25
;
select count(*) from audience
where month_no in (4, 4)
;
select count(*) from audience
where month_no in (3, 25)
;
select count(*) from audience
where month_no in (3, 25, 26)
;
select count(*) from audience
where month_no in (3, 25, 25, 26)
;
select count(*) from audience
where month_no in (3, 25, null)
;
select count(*) from audience
where month_no in (:b1, :b2, :b3)
;
set autotrace off
现象:
10g
Execution Plan
----------------------------------------------------------
Plan hash value: 3337892515
---------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 2 |
| 1 | SORT AGGREGATE | | 1 | 3 | |
|* 2 | TABLE ACCESS FULL| AUDIENCE | 1 | 3 | 2 |
---------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("MONTH_NO"=25)
Note
-----
- cpu costing is off (consider enabling it)
Execution Plan
----------------------------------------------------------
Plan hash value: 3337892515
---------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 2 |
| 1 | SORT AGGREGATE | | 1 | 3 | |
|* 2 | TABLE ACCESS FULL| AUDIENCE | 100 | 300 | 2 |
---------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("MONTH_NO"=4)
Note
-----
- cpu costing is off (consider enabling it)
Execution Plan
----------------------------------------------------------
Plan hash value: 3337892515
---------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 2 |
| 1 | SORT AGGREGATE | | 1 | 3 | |
|* 2 | TABLE ACCESS FULL| AUDIENCE | 100 | 300 | 2 |
---------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("MONTH_NO"=3 OR "MONTH_NO"=25)
Note
-----
- cpu costing is off (consider enabling it)
Execution Plan
----------------------------------------------------------
Plan hash value: 3337892515
---------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 2 |
| 1 | SORT AGGREGATE | | 1 | 3 | |
|* 2 | TABLE ACCESS FULL| AUDIENCE | 101 | 303 | 2 |
---------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("MONTH_NO"=3 OR "MONTH_NO"=25 OR "MONTH_NO"=26)
Note
-----
- cpu costing is off (consider enabling it)
Execution Plan
----------------------------------------------------------
Plan hash value: 3337892515
---------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 2 |
| 1 | SORT AGGREGATE | | 1 | 3 | |
|* 2 | TABLE ACCESS FULL| AUDIENCE | 101 | 303 | 2 |
---------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("MONTH_NO"=3 OR "MONTH_NO"=25 OR "MONTH_NO"=26)
Note
-----
- cpu costing is off (consider enabling it)
Execution Plan
----------------------------------------------------------
Plan hash value: 3337892515
---------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 2 |
| 1 | SORT AGGREGATE | | 1 | 3 | |
|* 2 | TABLE ACCESS FULL| AUDIENCE | 200 | 600 | 2 |
---------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("MONTH_NO"=3 OR "MONTH_NO"=25 OR
"MONTH_NO"=TO_NUMBER(NULL))
Note
-----
- cpu costing is off (consider enabling it)
Execution Plan
----------------------------------------------------------
Plan hash value: 3337892515
---------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 2 |
| 1 | SORT AGGREGATE | | 1 | 3 | |
|* 2 | TABLE ACCESS FULL| AUDIENCE | 300 | 900 | 2 |
---------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("MONTH_NO"=TO_NUMBER(:B1) OR "MONTH_NO"=TO_NUMBER(:B2) OR
"MONTH_NO"=TO_NUMBER(:B3))
11g:
Execution Plan
----------------------------------------------------------
Plan hash value: 3337892515
---------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 2 |
| 1 | SORT AGGREGATE | | 1 | 3 | |
|* 2 | TABLE ACCESS FULL| AUDIENCE | 1 | 3 | 2 |
---------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("MONTH_NO"=25)
Note
-----
- cpu costing is off (consider enabling it)
Execution Plan
----------------------------------------------------------
Plan hash value: 3337892515
---------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 2 |
| 1 | SORT AGGREGATE | | 1 | 3 | |
|* 2 | TABLE ACCESS FULL| AUDIENCE | 100 | 300 | 2 |
---------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("MONTH_NO"=4)
Note
-----
- cpu costing is off (consider enabling it)
Execution Plan
----------------------------------------------------------
Plan hash value: 3337892515
---------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 2 |
| 1 | SORT AGGREGATE | | 1 | 3 | |
|* 2 | TABLE ACCESS FULL| AUDIENCE | 100 | 300 | 2 |
---------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("MONTH_NO"=3 OR "MONTH_NO"=25)
Note
-----
- cpu costing is off (consider enabling it)
Execution Plan
----------------------------------------------------------
Plan hash value: 3337892515
---------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 2 |
| 1 | SORT AGGREGATE | | 1 | 3 | |
|* 2 | TABLE ACCESS FULL| AUDIENCE | 101 | 303 | 2 |
---------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("MONTH_NO"=3 OR "MONTH_NO"=25 OR "MONTH_NO"=26)
Note
-----
- cpu costing is off (consider enabling it)
Execution Plan
----------------------------------------------------------
Plan hash value: 3337892515
---------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 2 |
| 1 | SORT AGGREGATE | | 1 | 3 | |
|* 2 | TABLE ACCESS FULL| AUDIENCE | 101 | 303 | 2 |
---------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("MONTH_NO"=3 OR "MONTH_NO"=25 OR "MONTH_NO"=26)
Note
-----
- cpu costing is off (consider enabling it)
Execution Plan
----------------------------------------------------------
Plan hash value: 3337892515
---------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 2 |
| 1 | SORT AGGREGATE | | 1 | 3 | |
|* 2 | TABLE ACCESS FULL| AUDIENCE | 100 | 300 | 2 |
---------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("MONTH_NO"=3 OR "MONTH_NO"=25)
Note
-----
- cpu costing is off (consider enabling it)
Execution Plan
----------------------------------------------------------
Plan hash value: 3337892515
---------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 2 |
| 1 | SORT AGGREGATE | | 1 | 3 | |
|* 2 | TABLE ACCESS FULL| AUDIENCE | 300 | 900 | 2 |
---------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("MONTH_NO"=TO_NUMBER(:B1) OR "MONTH_NO"=TO_NUMBER(:B2) OR
"MONTH_NO"=TO_NUMBER(:B3))
观测红色字体部分的内容。我们会发现11g中能更精确的计算基数card的值。在刘易斯《基于成本的oracle优化法则中》,他所测试的9i/10g还不能正确计算超出最大最小值界限,只可以很好的处理重复值
我所测试的10g版本已经可以很好的处理最大最小值的问题。11.2.0.3.6中进一步完善了算法,可以更好的处理异常值,但帮定变量还不行
create table audience as
select
trunc(dbms_random.value(1,13)) month_no
from
all_objects
where
rownum <= 1200
;
begin
dbms_stats.gather_table_stats(
user,
'audience',
cascade => true,
estimate_percent => null,
method_opt => 'for all columns size 1'
);
end;
/
set autotrace traceonly explain
select count(*) from audience
where month_no = 25
;
select count(*) from audience
where month_no in (4, 4)
;
select count(*) from audience
where month_no in (3, 25)
;
select count(*) from audience
where month_no in (3, 25, 26)
;
select count(*) from audience
where month_no in (3, 25, 25, 26)
;
select count(*) from audience
where month_no in (3, 25, null)
;
select count(*) from audience
where month_no in (:b1, :b2, :b3)
;
set autotrace off
现象:
10g
Execution Plan
----------------------------------------------------------
Plan hash value: 3337892515
---------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 2 |
| 1 | SORT AGGREGATE | | 1 | 3 | |
|* 2 | TABLE ACCESS FULL| AUDIENCE | 1 | 3 | 2 |
---------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("MONTH_NO"=25)
Note
-----
- cpu costing is off (consider enabling it)
Execution Plan
----------------------------------------------------------
Plan hash value: 3337892515
---------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 2 |
| 1 | SORT AGGREGATE | | 1 | 3 | |
|* 2 | TABLE ACCESS FULL| AUDIENCE | 100 | 300 | 2 |
---------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("MONTH_NO"=4)
Note
-----
- cpu costing is off (consider enabling it)
Execution Plan
----------------------------------------------------------
Plan hash value: 3337892515
---------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 2 |
| 1 | SORT AGGREGATE | | 1 | 3 | |
|* 2 | TABLE ACCESS FULL| AUDIENCE | 100 | 300 | 2 |
---------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("MONTH_NO"=3 OR "MONTH_NO"=25)
Note
-----
- cpu costing is off (consider enabling it)
Execution Plan
----------------------------------------------------------
Plan hash value: 3337892515
---------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 2 |
| 1 | SORT AGGREGATE | | 1 | 3 | |
|* 2 | TABLE ACCESS FULL| AUDIENCE | 101 | 303 | 2 |
---------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("MONTH_NO"=3 OR "MONTH_NO"=25 OR "MONTH_NO"=26)
Note
-----
- cpu costing is off (consider enabling it)
Execution Plan
----------------------------------------------------------
Plan hash value: 3337892515
---------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 2 |
| 1 | SORT AGGREGATE | | 1 | 3 | |
|* 2 | TABLE ACCESS FULL| AUDIENCE | 101 | 303 | 2 |
---------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("MONTH_NO"=3 OR "MONTH_NO"=25 OR "MONTH_NO"=26)
Note
-----
- cpu costing is off (consider enabling it)
Execution Plan
----------------------------------------------------------
Plan hash value: 3337892515
---------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 2 |
| 1 | SORT AGGREGATE | | 1 | 3 | |
|* 2 | TABLE ACCESS FULL| AUDIENCE | 200 | 600 | 2 |
---------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("MONTH_NO"=3 OR "MONTH_NO"=25 OR
"MONTH_NO"=TO_NUMBER(NULL))
Note
-----
- cpu costing is off (consider enabling it)
Execution Plan
----------------------------------------------------------
Plan hash value: 3337892515
---------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 2 |
| 1 | SORT AGGREGATE | | 1 | 3 | |
|* 2 | TABLE ACCESS FULL| AUDIENCE | 300 | 900 | 2 |
---------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("MONTH_NO"=TO_NUMBER(:B1) OR "MONTH_NO"=TO_NUMBER(:B2) OR
"MONTH_NO"=TO_NUMBER(:B3))
11g:
Execution Plan
----------------------------------------------------------
Plan hash value: 3337892515
---------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 2 |
| 1 | SORT AGGREGATE | | 1 | 3 | |
|* 2 | TABLE ACCESS FULL| AUDIENCE | 1 | 3 | 2 |
---------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("MONTH_NO"=25)
Note
-----
- cpu costing is off (consider enabling it)
Execution Plan
----------------------------------------------------------
Plan hash value: 3337892515
---------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 2 |
| 1 | SORT AGGREGATE | | 1 | 3 | |
|* 2 | TABLE ACCESS FULL| AUDIENCE | 100 | 300 | 2 |
---------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("MONTH_NO"=4)
Note
-----
- cpu costing is off (consider enabling it)
Execution Plan
----------------------------------------------------------
Plan hash value: 3337892515
---------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 2 |
| 1 | SORT AGGREGATE | | 1 | 3 | |
|* 2 | TABLE ACCESS FULL| AUDIENCE | 100 | 300 | 2 |
---------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("MONTH_NO"=3 OR "MONTH_NO"=25)
Note
-----
- cpu costing is off (consider enabling it)
Execution Plan
----------------------------------------------------------
Plan hash value: 3337892515
---------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 2 |
| 1 | SORT AGGREGATE | | 1 | 3 | |
|* 2 | TABLE ACCESS FULL| AUDIENCE | 101 | 303 | 2 |
---------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("MONTH_NO"=3 OR "MONTH_NO"=25 OR "MONTH_NO"=26)
Note
-----
- cpu costing is off (consider enabling it)
Execution Plan
----------------------------------------------------------
Plan hash value: 3337892515
---------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 2 |
| 1 | SORT AGGREGATE | | 1 | 3 | |
|* 2 | TABLE ACCESS FULL| AUDIENCE | 101 | 303 | 2 |
---------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("MONTH_NO"=3 OR "MONTH_NO"=25 OR "MONTH_NO"=26)
Note
-----
- cpu costing is off (consider enabling it)
Execution Plan
----------------------------------------------------------
Plan hash value: 3337892515
---------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 2 |
| 1 | SORT AGGREGATE | | 1 | 3 | |
|* 2 | TABLE ACCESS FULL| AUDIENCE | 100 | 300 | 2 |
---------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("MONTH_NO"=3 OR "MONTH_NO"=25)
Note
-----
- cpu costing is off (consider enabling it)
Execution Plan
----------------------------------------------------------
Plan hash value: 3337892515
---------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 2 |
| 1 | SORT AGGREGATE | | 1 | 3 | |
|* 2 | TABLE ACCESS FULL| AUDIENCE | 300 | 900 | 2 |
---------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("MONTH_NO"=TO_NUMBER(:B1) OR "MONTH_NO"=TO_NUMBER(:B2) OR
"MONTH_NO"=TO_NUMBER(:B3))
观测红色字体部分的内容。我们会发现11g中能更精确的计算基数card的值。在刘易斯《基于成本的oracle优化法则中》,他所测试的9i/10g还不能正确计算超出最大最小值界限,只可以很好的处理重复值
我所测试的10g版本已经可以很好的处理最大最小值的问题。11.2.0.3.6中进一步完善了算法,可以更好的处理异常值,但帮定变量还不行
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29033984/viewspace-767028/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29033984/viewspace-767028/