Shulou(Shulou.com)05/31 Report--

This article will explain in detail what is the order of execution of where conditions in Oracle, and the content of the article is of high quality, so the editor will share it with you for reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

Question:

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

Why the statement "select value from t where name='db_block_size' and to_number (value) = 8192;" executes successfully, but v$parameter reports an error instead.

The process of experimental research:

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

At first, I saw that this implementation plan was very confused, and I didn't understand why it could be carried out successfully. I still couldn't understand it when I did 10046 Magi 10053 and rewrote sql plus hint.

Finally, it is guessed that in the step of filter ("NAME" = 'db_block_size' AND TO_NUMBER ("VALUE") = 8192), Oracle first filters the data by name='db_block_size' and then filters the data by to_number (' value') = 8192.

If you can change the predicate information to filter (TO_NUMBER ("VALUE") AND "NAME" = 'db_block_size'=8192) and execute the error report, then the conjecture is correct.

Try to change the and condition of the sql statement to "select value from t whereto_number (value) = 8192 and name='db_block_size';", but it is the same as before, and the step is omitted here.

Other test tables are constructed here:

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

Table created.

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

1 row created.

SYS@proc > commit

Commit complete.

SYS@proc > select * from a

ID1 ID2 ID3 ID4

--

1 1 1 0

The following four sql statements are executed:

① 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

Among them, ① and ③, ② and ④ are just conditional positions swapping after where.

View the execution result:

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

② and ④ are just in different locations, but one executes normally and the other reports incorrectly.

Take a look at the execution plans for two 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.

Here the comparison predicate information happens to be in two different positions, resulting in different execution results.

A conjecture that illustrates the above question:

Finally, it is guessed that in the step of filter ("NAME" = 'db_block_size' AND TO_NUMBER ("VALUE") = 8192), Oracle first filters the data by name='db_block_size' and then filters the data by to_number (' value') = 8192.

If you can change the predicate information to filter (TO_NUMBER ("VALUE") AND "NAME" = 'db_block_size'=8192) and execute the error report, then the conjecture is correct.

So the problem is solved.

Other:

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 TJE 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 TJE test a where to_number (a.id | | t.value) = 8192 and a.id | | name='db_block_size'

Select value from TJ 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 TJE 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.

So the execution order of the conditions after the where is actually related to the order of the execution plan predicate information, regardless of the position of the where.

Some online experiments in 10g come to the conclusion that from right to left, in 11g, follow the same steps and do not come to the same conclusion.

The problem extends:

Filter ("NAME" = 'db_block_size' AND TO_NUMBER ("VALUE") = 8192), whether all the data scanned at one time is being filtered, or whether it is obtained one line at a time.

Extended link: http://blog.itpub.net/30174570/viewspace-2149212/

So much for sharing about the order of execution of where conditions in Oracle. I hope the above content can be helpful to you and learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.