Question

SELECT COUNT (*)
  FROM rps2_workflow
 WHERE     workflow_added > TO_DATE ( 01.09.2011 ,  dd.mm.yyyy )
       AND workflow_finished < TO_DATE ( wtf ,  dd.mm.yyyy )
       AND workflow_status IN (7, 12, 17)
       AND workflow_worker = 159

由于日期无效,我预计这一询问会失败,但结果为零。

查询计划显示,第8步,无效条款is。处理:

8 TABLE ACCESS BY INDEX ROWID TABLE RPS2.RPS2_WORKFLOW Object Instance: 1  Filter Predicates: ("WORKFLOW_STATUS"=7 OR "WORKFLOW_STATUS"=12 OR "WORKFLOW_STATUS"=17) AND SYS_EXTRACT_UTC("WORKFLOW_FINISHED")<SYS_EXTRACT_UTC(TO_DATE( wtf , dd.mm.yyyy ))  Cost: 11  Bytes: 33  Cardinality: 1  CPU Cost: 8 M  IO Cost: 10  Time: 1

如果我们在<代码>AND workflow_status IN(7、12、17)的条件下发表评论,那么预期我们会收到ORA-01858:发现非数字性质,届时会发现有数字。

If we comment out AND workflow_finished < TO_DATE ( wtf , dd.mm.yyyy ) then we get amount of records that fit that conditions (> 0)

如何做到这一点?

www.un.org/Depts/DGACM/index_spanish.htm

The hint /*+no_index(rps2_workflow) */ doesn t change anything (whereas in the plan we see that fullscan is performed)

SELECT STATEMENT  ALL_ROWSCost: 254  Bytes: 31  Cardinality: 1  CPU Cost: 34 M  IO Cost: 248  Time: 4       
2 SORT AGGREGATE  Bytes: 31  Cardinality: 1     
    1 TABLE ACCESS FULL TABLE RPS2.RPS2_WORKFLOW Object Instance: 1  Filter Predicates: "WORKFLOW_WORKER"=159 AND ("WORKFLOW_STATUS"=7 OR "WORKFLOW_STATUS"=12 OR "WORKFLOW_STATUS"=17) AND SYS_EXTRACT_UTC("WORKFLOW_ADDED")>SYS_EXTRACT_UTC(TIMESTAMP  2011-09-01 00:00:00 ) AND SYS_EXTRACT_UTC("WORKFLOW_FINISHED")<SYS_EXTRACT_UTC(TO_DATE( wtf , dd.mm.yyyy ))  Cost: 254  Bytes: 31  Cardinality: 1  CPU Cost: 34 M  IO Cost: 248  Time: 4

Answer 1

可能发现,符合所有其他条件的所有记录都有<条码>NUL>><条码>的工作流程领域。

与<代码>NUL相比的任何东西都不为人所知,因此没有必要评价其他操作。

Answer 2

如果最优化者决定它不需要评价某项职能,那么它就取得了一定成绩,因此这一职能永远不会成为例外:

select 1 from dual where 1 = 1 OR to_date( asdasdasd , asdasdasdas ) > sysdate ;

         1
----------
         1

该职能只有在实际得到评估时才产生例外:

SQL> select 1 from dual where 1 = 1 AND to_date( asdasd , asdas ) > sysdate ;
select 1 from dual where 1 = 1 AND to_date( asdasd , asdas ) > sysdate
                                                    *
ERROR at line 1:
ORA-01821: date format not recognized

但是,如果教区可以决定法定<>查询/em>,查询无效――因为该职务有错误的争论类型或查询无效的类型,则教区会提出例外,然后由最优化者在其上调:

SQL> select 1 from dual where 1 = 1 or to_date( asdasdasd ,0) > sysdate ;
select 1 from dual where 1 = 1 or to_date( asdasdasd ,0) > sysdate
                                                         *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected DATE got NUMBER


SQL> select 1 from dual where 1 = 1 or to_date( asdasdasd , asdasdasdas ) > 42 ;
select 1 from dual where 1 = 1 or to_date( asdasdasd , asdasdasdas ) > 42
                                                                        *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected DATE got NUMBER

友情链接