where检索条件性能测试记载

清早起来打开ITPUB看到sql的执行计划适合和where后的条件有关系了，以前总是听说where后的条件是从右到左检索，所以需要把能排除大部分数据的检索条件放在最右边。

参照PUB中的前辈的测试：

Select 1 from dual where 1/0=1 and 1/2=1; 正常

Select 1 from dual where 1/2=1 and 1/0=1; 报错

这里自己有个测试思路：

SQL> desc test01;
Name Type Nullable Default Comments
---- ------------ -------- ------- --------
ID NUMBER Y
NAME VARCHAR2(10) Y

SQL> select * from test01 where name=sf and 1/0=1 and id=8;

select * from test01 where name=sf and 1/0=1 and id=8

ORA-00904: "SF": 标识符无效

SQL> select * from test01 where name='sf' and 1/0=1 and id=8;

select * from test01 where name='sf' and 1/0=1 and id=8

ORA-01476: 除数为 0

可以查看在oracle 10g中也不一定是严格按照从右到左的执行顺序

SQL> select * from test01 where name=sf and id='sd';

select * from test01 where name=sf and id='sd'

ORA-00904: "SF": 标识符无效

SQL> select * from test01 where name='sf' and id='sd';

select * from test01 where name='sf' and id='sd'

ORA-01722: 无效数字

SQL> select index_name from user_indexes where table_name='TEST01';

INDEX_NAME
------------------------------
INDEX_TEST01

此时发觉where的检索条件也并不是先从索引开始检索，在cbo中where的条件先后已经没有区别了！

下面从cost消耗来看看：

Create or replace function f1(v_ab in varchar2) return varchar2

As

Begin

Dbms_output.put_line(v_ab);

Return v_ab;

End;

/.

Function f1

Create or replace function f2(v_ab in varchar2) return varchar2

As

Begin

Dbms_output.put_line(v_ab);

Return v_ab;

End;

/

Function f2

SQL> set serveroutput on;

SQL> select 1 from dual where 'a'=f1('a') and 'b'=f2('b');

1

----------

1

b

a

上述也可以看出where条件是从右到左开始检索。

T表中大概有410000数据，表结构为x number，y number

此时t表中的x=1的数据有40000，y=2的数据有10000，t表中没有索引

SQL> select x,y from t where x=1 and y=2;

已选择39行。

已用时间: 00: 00: 00.46

执行计划

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

Plan hash value: 1601196873

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

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

| 0 | SELECT STATEMENT | | 1 | 7 | 162 (10)| 00:00:02 |

|* 1 | TABLE ACCESS FULL| T | 1 | 7 | 162 (10)| 00:00:02 |

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

Predicate Information (identified by operation id):

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

1 - filter("Y"=2 AND "X"=1)

统计信息

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

257 recursive calls

0 db block gets

803 consistent gets

756 physical reads

0 redo size

930 bytes sent via SQL*Net to client

407 bytes received via SQL*Net from client

4 SQL*Net roundtrips to/from client

4 sorts (memory)

0 sorts (disk)

39 rows processed

SQL> alter system flush buffer_cache;

系统已更改。

已用时间: 00: 00: 00.06

SQL> select x,y from t where y=2 and x=1;

已选择39行。

已用时间: 00: 00: 00.31

执行计划

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

Plan hash value: 1601196873

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

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

| 0 | SELECT STATEMENT | | 1 | 7 | 162 (10)| 00:00:02 |

|* 1 | TABLE ACCESS FULL| T | 1 | 7 | 162 (10)| 00:00:02 |

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

Predicate Information (identified by operation id):

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

1 - filter("Y"=2 AND "X"=1)

统计信息

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

1 recursive calls

0 db block gets

756 consistent gets

749 physical reads

0 redo size

930 bytes sent via SQL*Net to client

407 bytes received via SQL*Net from client

4 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

39 rows processed

可以看出两者没有多大的区别，因为看执行计划都是对全表扫描然后一起来检索筛选，在oracle 10G中的cbo模式，where的检索顺序并不一定而且cost消耗也是严格oracle自己来选择认为最优执行计划。以后在学习中看来加深自己印象如何构造环境，测试的具体步骤 结论等，有些结论真的需要自己来实践。

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25362835/viewspace-1055952/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25362835/viewspace-1055952/