Oracle中where条件执行顺序是什么

2025-03-09 技术教程

这篇文章将为大家详细讲解有关Oracle中where条件执行顺序是什么，文章内容质量较高，因此小编分享给大家做个参考，希望大家阅读完这篇文章后对相关知识有一定的了解。

问题：

SYS@proc> create table t as select * from v$parameter;

Table created.

SYS@proc> select value from t where name='db_block_size' and to_number(value)=8192;

VALUE

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

8192

SYS@proc> select value from v$parameter where name='db_block_size' and to_number(value)=8192;

select value from v$parameter where name='db_block_size' and to_number(value)=8192

ERROR at line 1:

ORA-01722: invalid number

为什么语句“select value from t where name='db_block_size' and to_number(value)=8192;”执行成功，换成v$parameter却报错。

实验研究过程：

SYS@proc> set autotrace on

SYS@proc> analyze table t compute statistics;

Table analyzed.

SYS@proc> select value from t where name='db_block_size' and to_number(value)=8192;

VALUE

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

8192

Execution Plan

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

Plan hash value: 1601196873

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

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

| 0 | SELECT STATEMENT | | 1 | 26 | 4 (0)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| T | 1 | 26 | 4 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

1 - filter("NAME"='db_block_size' AND TO_NUMBER("VALUE")=8192)

Statistics

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

1 recursive calls

0 db block gets

9 consistent gets

0 physical reads

0 redo size

525 bytes sent via SQL*Net to client

523 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

一开始看到这个执行计划很懵逼，完全搞不懂为什么能够执行成功，做10046,10053，改写sql加hint还是搞不懂。
最后猜想Oracle在filter("NAME"='db_block_size' AND TO_NUMBER("VALUE")=8192)这个步骤，是先对数据做name='db_block_size'的过滤，在做to_number('value')=8192的过滤。
若是能将谓词信息改变成filter(TO_NUMBER("VALUE") AND "NAME"='db_block_size'=8192)并且执行报错，那么猜想就是正确的。
尝试将sql语句的and条件调换位置"select value from t whereto_number(value)=8192 and name='db_block_size';",不过还是和原来一样，这里省略步骤。

这里构造其他测试表：

SYS@proc> create table a(id1 int,id2 int,id3 int,id4 int);

Table created.

SYS@proc> insert into a values(1,1,1,0);

1 row created.

SYS@proc> commit;

Commit complete.

SYS@proc> select * from a;

ID1 ID2 ID3ID4

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

1 1 1 0

这里执行以下4条sql语句：
①Select 'ok' From aaa where id1/id2=1 and id3/id4=2;
②Select 'ok' From aaa where id1/id2=2 and id3/id4=2;
③Select 'ok' From aaa where id3/id4=2 and id1/id2=1;
④Select 'ok' From aaa where id3/id4=2 and id1/id2=2;
其中①和③，②和④只是where后条件位置互换而已。
查看执行结果：

SYS@proc> Select 'ok' From aaa where id1/id2=1 and id3/id4=2;

Select 'ok' From aaa where id1/id2=1 and id3/id4=2

ERROR at line 1:

ORA-01476: divisor is equal to zero

SYS@proc> Select 'ok' From aaa where id1/id2=2 and id3/id4=2;

no rows selected

SYS@proc> Select 'ok' From aaa where id3/id4=2 and id1/id2=1;

Select 'ok' From aaa where id3/id4=2 and id1/id2=1

ERROR at line 1:

ORA-01476: divisor is equal to zero

SYS@proc> Select 'ok' From aaa where id3/id4=2 and id1/id2=2;

Select 'ok' From aaa where id3/id4=2 and id1/id2=2

ERROR at line 1:

ORA-01476: divisor is equal to zero

②和④只是位置不同，但是一个却正常执行，一个却报错了。
这里查看两条sql的执行计划：

SYS@proc> explain plan for Select 'ok' From aaa where id1/id2=2 and id3/id4=2;

Explained.

SYS@proc> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT

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

Plan hash value: 864433273

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

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

| 0 | SELECT STATEMENT | | 1 | 12 | 2 (0)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| AAA | 1 | 12 | 2 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

PLAN_TABLE_OUTPUT

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

1 - filter("ID1"/"ID2"=2 AND "ID3"/"ID4"=2)

13 rows selected.

SYS@proc> explain plan for Select 'ok' From aaa where id3/id4=2 and id1/id2=2;

Explained.

SYS@proc> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT

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

Plan hash value: 864433273

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

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

| 0 | SELECT STATEMENT | | 1 | 12 | 2 (0)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| AAA | 1 | 12 | 2 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

PLAN_TABLE_OUTPUT

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

1 - filter("ID3"/"ID4"=2 AND "ID1"/"ID2"=2)

13 rows selected.

这里对比谓词信息刚好是两个位置不同，导致执行结果不一样。
正好说明上边的问题的猜想：
最后猜想Oracle在filter("NAME"='db_block_size' AND TO_NUMBER("VALUE")=8192)这个步骤，是先对数据做name='db_block_size'的过滤，在做to_number('value')=8192的过滤。
若是能将谓词信息改变成filter(TO_NUMBER("VALUE") AND "NAME"='db_block_size'=8192)并且执行报错，那么猜想就是正确的。

由此问题解决。

其他：

SYS@proc> create table test (id int);

Table created.

SYS@proc> insert into test values(null);

1 row created.

SYS@proc> commit;

Commit complete.

SYS@proc> select * from test;

----------

SYS@proc> set autotrace on

SYS@proc> select value from t,test a where a.id||name='db_block_size' and to_number(a.id||t.value)=8192;

VALUE

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

8192

Execution Plan

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

Plan hash value: 423998170

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

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

| 0 | SELECT STATEMENT | |1 | 39 |6 (0)| 00:00:01 |

| 1 | NESTED LOOPS | |1 | 39 |6 (0)| 00:00:01 |

| 2 | TABLE ACCESS FULL| TEST |1 | 13 |2 (0)| 00:00:01 |

|* 3 | TABLE ACCESS FULL| T |1 | 26 |4 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

3 - filter(TO_CHAR("A"."ID")||"NAME"='db_block_size' AND

TO_NUMBER(TO_CHAR("A"."ID")||"T"."VALUE")=8192)

Note

-----

- dynamic sampling used for this statement (level=2)

Statistics

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

32 recursive calls

0 db block gets

28 consistent gets

0 physical reads

0 redo size

525 bytes sent via SQL*Net to client

523 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

4 sorts (memory)

0 sorts (disk)

1 rows processed

SYS@proc> set autotrace off

SYS@proc> select value from t,test a where to_number(a.id||t.value)=8192 and a.id||name='db_block_size';

select value from t,test a where to_number(a.id||t.value)=8192 and a.id||name='db_block_size'

ERROR at line 1:

ORA-01722: invalid number

SYS@proc> explain plan for select value from t,test a where to_number(a.id||t.value)=8192 and a.id||name='db_block_size';

Explained.

SYS@proc> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT

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

Plan hash value: 423998170

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

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

| 0 | SELECT STATEMENT | |1 | 39 |6 (0)| 00:00:01 |

| 1 | NESTED LOOPS | |1 | 39 |6 (0)| 00:00:01 |

| 2 | TABLE ACCESS FULL| TEST |1 | 13 |2 (0)| 00:00:01 |

|* 3 | TABLE ACCESS FULL| T |1 | 26 |4 (0)| 00:00:01 |

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

PLAN_TABLE_OUTPUT

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

Predicate Information (identified by operation id):

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

3 - filter(TO_NUMBER(TO_CHAR("A"."ID")||"T"."VALUE")=8192 AND

TO_CHAR("A"."ID")||"NAME"='db_block_size')

16 rows selected.

所以where后边条件的执行顺序，实际上和执行计划谓词信息的顺序有关，和where的位置无关。
网上有些在10g做实验得出结论是从右到左，在11g里边，按照相同步骤执行并得不出相同结论。

问题延伸：
filter("NAME"='db_block_size' AND TO_NUMBER("VALUE")=8192)，这里是一次性扫描出全部数据在进行过滤，还是一行一行获取在判断的。
延伸链接：http://blog.itpub.net/30174570/viewspace-2149212/

关于Oracle中where条件执行顺序是什么就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。