一般来说,Oracle根据您的
CHECK
无论何时这样做,约束都将为优化器提供更多信息,以生成一个好的计划。当这些是多余的时,识别它们并不总是足够聪明的。以下是Oracle 12c中使用表名的一个简短示例:
-- Create the CUSTS table
CREATE TABLE custs ( custid number not null );
CREATE INDEX custs_u1 on custs (custid);
-- Create the LISTS table
CREATE TABLE lists
( listid number not null,
sort_type number not null,
custid number,
constraint lists_c1 check ( sort_type between 1 and 16 and
trunc(sort_type) = sort_Type )
);
-- Explain a join
EXPLAIN PLAN FOR
SELECT /*+ USE_HASH(u) */
u.custid AS "custid",
l.listid AS "listid"
FROM custs u
INNER JOIN lists l ON u.custid = l.custid;
-- Show the plan
select * from table(dbms_xplan.display);
Plan hash value: 2443150416
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 3 (0)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 39 | 3 (0)| 00:00:01 |
| 2 | INDEX FULL SCAN | CUSTS_U1 | 1 | 13 | 1 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| LISTS | 1 | 26 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("U"."CUSTID"="L"."CUSTID")
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
到目前为止,没有什么奇怪的。没有添加可疑谓词。
现在,让我们告诉Oracle优化器
TRUNC(sort_type)
declare
x varchar2(30);
begin
x := dbms_stats.create_extended_stats ( user, 'LISTS', '(TRUNC(SORT_TYPE))');
dbms_output.put_line('Extension name = ' || x);
end;
... 现在,让我们再次解释同样的问题。。。
-- Re-explain the same join as before
EXPLAIN PLAN FOR
SELECT /*+ USE_HASH(u) */
u.custid AS "custid",
l.listid AS "listid"
FROM custs u
INNER JOIN lists l ON u.custid = l.custid;
-- Show the new plan
select * from table(dbms_xplan.display);
Plan hash value: 2443150416
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 52 | 3 (0)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 52 | 3 (0)| 00:00:01 |
| 2 | INDEX FULL SCAN | CUSTS_U1 | 1 | 13 | 1 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| LISTS | 1 | 39 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("U"."CUSTID"="L"."CUSTID")
3 - filter(TRUNC("SORT_TYPE")>=1 AND TRUNC("SORT_TYPE")<=16)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
(*)在以前的版本中有很多bug,这些bug破坏了CBO估计的选择性,从而损害了事情。
扩展统计数据的存在只是Oracle认为它可以从这个谓词中获益的一个例子。要确定这是否是您的情况中的原因,您可以在数据库中查找扩展统计信息,如下所示:
SELECT * FROM dba_stat_extensions where table_name = 'LISTS';
请记住,Oracle CBO可以自行创建stat扩展。所以可能会有一些你没有意识到的扩展数据。
