上篇问题链接:http://blog.itpub.net/30174570/viewspace-2148592/
问题:
为什么语句“
select
value
from
t
where
name
=
'db_block_size'
and
to_number
(
value
)
=
8192
;
”执行成功,换成v$parameter却报错。
实验研究过程:
一开始看到这个执行计划很懵逼,完全搞不懂为什么能够执行成功,做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 where to_number ( value )=8192 and name='db_block_size' ; ",不过还是和原来一样,这里省略步骤。
这里构造其他测试表:
这里执行以下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后条件位置互换而已。
查看执行结果:
②和④只是位置不同,但是一个却正常执行,一个却报错了。
这里查看两条sql的执行计划:
这里对比谓词信息刚好是两个位置不同,导致执行结果不一样。
正好说明上边的问题的猜想:
最后猜想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 ) 并且执行报错,那么猜想就是正确的。
由此问题解决。
其他:
所以where后边条件的执行顺序,实际上和执行计划谓词信息的顺序有关,和where的位置无关。
网上有些在10g做实验得出结论是从右到左,在11g里边,按照相同步骤执行并得不出相同结论。
问题延伸:
filter ( "NAME" = 'db_block_size' AND TO_NUMBER ( "VALUE" ) = 8192 ) ,这里是一次性扫描出全部数据在进行过滤,还是一行一行获取在判断的。
延伸链接: http://blog.itpub.net/30174570/viewspace-2149212/
问题:
- 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
实验研究过程:
- 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
-
- --------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- --------------------------------------------------------------------------
- | 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
最后猜想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 where to_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 ID3 ID4
- ---------- ---------- ---------- ----------
- 1 1 1 0
①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
-
- -----------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- ------------------------------------------------------------------------
- | 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
-
- ------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- ------------------------------------------------------------------------
- | 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;
-
- ID
- ----------
-
-
- 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
-
- ---------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- ---------------------------------------------------------------------------
- | 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
-
- ---------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- ---------------------------------------------------------------------------
- | 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/
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30174570/viewspace-2148607/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/30174570/viewspace-2148607/