Oracle可选绑定变量 [英] Oracle optional bind variables
问题描述
我有一个查询，从给定用户ID的表中选择用户.此参数是可选的.
I have a query to select users from a table, given user id. This parameter is optional.
这是查询:
SELECT * FROM USERS
WHERE (USER_ID = :USER_ID OR :USER_ID IS NULL)
ORDER BY USER_ID;
现在我执行查找一个用户的查询，所以:USER_ID取勇气1:
Now I execute the query finding one user, so :USER_ID takes the valor 1 :
SELECT * FROM USERS
WHERE (USER_ID = 1 OR 1 IS NULL)
ORDER BY USER_ID;
此查询需要5秒钟.
然后，我多次添加到上一个查询OR :USER_ID IS NULL.此示例比第一个示例花费更多的时间:
And then, I add to the previous query OR :USER_ID IS NULL many times. This example takes much more time than the first:
SELECT * FROM USERS
WHERE (USER_ID = 1 OR 1 IS NULL [OR 1 IS NULL]x100)
ORDER BY USER_ID;
此查询需要30秒.
执行计划在两个示例中相同:
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3256K| 695M| | 682K (1)| 00:00:27 | | |
| 1 | SORT ORDER BY | | 3256K| 695M| 877M| 682K (1)| 00:00:27 | | |
| 2 | PARTITION RANGE ALL | | 3256K| 695M| | 534K (1)| 00:00:21 | 1 |1048575|
|* 3 | TABLE ACCESS STORAGE FULL| USERS | 3256K| 695M| | 534K (1)| 00:00:21 | 1 |1048575|
Oracle版本: Oracle Database 12c
Version of Oracle: Oracle Database 12c
为什么oracle不接受第一个陈述，即它始终为真，而停止评估其余的?
Why oracle does not take the first statement, that it's always true, and stop evaluating the rest?
推荐答案
您的问题是由OR谓词触发的大型表上的FULL TABLE SCAN.
Your problem is the FULL TABLE SCAN on a large table triggered by the ORpredicate.
根据绑定变量的值，查询返回一行(如果绑定变量不是NULL)或整个表.
Based on the value of the bind variable the query returns either one row (if the bind variable is not NULL) or the whole table otherwise.
对于仅一个绑定变量，您可以使用NVL trick
For only one bind variable you may use the NVL trick
SELECT * FROM USERS
WHERE (USER_ID = nvl(:USER_ID, USER_ID))
ORDER BY USER_ID;
这将导致执行计划包括两个部分，涵盖两种情况:
which leads to a execution plan consisting of two parts covering both cases :
BV为NULL->全扫描
BV is NULL -> FULL SCAN
BV不为空->索引访问
BV is NOT NULL -> INDEX ACCES
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8329 | 9313K| 941 (1)| 00:00:12 |
| 1 | SORT ORDER BY | | 8329 | 9313K| 941 (1)| 00:00:12 |
| 2 | CONCATENATION | | | | | |
|* 3 | FILTER | | | | | |
|* 4 | TABLE ACCESS FULL | USERS | 8247 | 9221K| 925 (1)| 00:00:12 |
|* 5 | FILTER | | | | | |
| 6 | TABLE ACCESS BY INDEX ROWID| USERS | 82 | 93890 | 15 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | USERS_IDX | 1110 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(:USER_ID IS NULL)
4 - filter("USER_ID" IS NOT NULL)
5 - filter(:USER_ID IS NOT NULL)
7 - access("USER_ID"=:USER_ID)
因此，如果通过了BV(非NULL)，这将迅速做出响应. AND 定义了USER_ID上的索引.
这将导致整个表的FULL TABLE SCAN(5秒) AND SORT(我猜是另外25秒)，总共有30秒的响应.
So this will response quickly, if the BV is passed (not NULL) AND index on USER_IDis defined.
This will lead to a FULL TABLE SCAN (5 seconds) AND SORT of the whole table (my guess an other 25 seconds), giving total 30 seconds response.
请注意，如果您通过BV，则仅执行FULL TABLE SCAN，由于仅返回一条记录(假设USER_ID为PK)，因此SORT时间可以忽略不计-这说明了响应时间的差异.
Note that if you pass the BV, you performs only the FULL TABLE SCAN, the SORT time is neglectible as only one records is returned (assuming USER_ID is PK) - which explains the difference in the response time.
这篇关于Oracle可选绑定变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
