本文通过两个具体的SQL查询案例，对比了使用IN关键字与多个OR条件在性能上的差异。结果显示两者具有相同的执行计划和相近的执行时间，因此在实际应用中可根据具体情况选择使用。
55. Evaluate the following two queries:
SQL> SELECT cust_last_name, cust_city
FROM customers
WHERE cust_credit_limit IN (1000, 2000, 3000);
SQL> SELECT cust_last_name, cust_city
FROM customers
WHERE cust_credit_limit = 1000 OR cust_credit_limit = 2000 OR cust_credit_limit = 3000;
Which statement is true regarding the above two queries?
A. Performance would improve in query 2.
B. Performance would degrade in query 2.
C. There would be no change in performance.
D. Performance would improve in query 2 only if there are null values in the CUST_CREDIT_LIMIT
column.
Answer: C
答案解析：
sh@TESTDB> SELECT cust_last_name, cust_city
2 FROM customers
3 WHERE cust_credit_limit IN (1000, 2000, 3000);
7975 rows selected.
Elapsed: 00:00:00.07
Execution Plan
----------------------------------------------------------
Plan hash value: 2008213504
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20556 | 441K| 406 (1)| 00:00:05 |
|* 1 | TABLE ACCESS FULL| CUSTOMERS | 20556 | 441K| 406 (1)| 00:00:05 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("CUST_CREDIT_LIMIT"=1000 OR "CUST_CREDIT_LIMIT"=2000 OR
"CUST_CREDIT_LIMIT"=3000)
sh@TESTDB> SELECT cust_last_name, cust_city
2 FROM customers
3 WHERE cust_credit_limit = 1000 OR cust_credit_limit = 2000 OR cust_credit_limit = 3000;
7975 rows selected.
Elapsed: 00:00:00.08
Execution Plan
----------------------------------------------------------
Plan hash value: 2008213504
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20556 | 441K| 406 (1)| 00:00:05 |
|* 1 | TABLE ACCESS FULL| CUSTOMERS | 20556 | 441K| 406 (1)| 00:00:05 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("CUST_CREDIT_LIMIT"=1000 OR "CUST_CREDIT_LIMIT"=2000 OR
"CUST_CREDIT_LIMIT"=3000)
一样的执行计划，性能一样，故选C
扫一扫
4773
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
