oracle SELECT 子句使用 IN ... 很慢？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7028465/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SELECT clause using IN ... very slow?
提问by Sudhakar
Could you guys please review the following query to an Oracle DB and point out what's wrong:
你们能否请查看以下对 Oracle DB 的查询并指出问题所在：
SELECT t1.name FROM t1, t2 WHERE t1.id = t2.id AND t2.empno IN (1, 2, 3, …, 200)
Query statistics:
查询统计：
- Time taken: 10.53 seconds.
- 耗时：10.53 秒。
Indices:
指数：
t2.empnois indexed.t1.idis indexed.t2.idis indexed.
t2.empno被索引。t1.id被索引。t2.id被索引。
Update
更新
The above query was just a sample replica of the query i use. Here below in a more true form
上面的查询只是我使用的查询的示例副本。下面以更真实的形式
Explain Plan
解释计划
Query:
询问：
SELECT
PRODUCT_REPRESENTATION_SK
FROM
Product_Representation pr
, Design_Object do
, Files files
,EPS_STATUS epsStatus
,EPS_ERROR_CODES epsError
,VIEW_TYPE viewTable
WHERE
pr.DESIGN_OBJECT_SK = do.DESIGN_OBJECT_SK
AND pr.LAYER_NAME !='Layer 0'
AND epsStatus.EPS_STATUS_SK = pr.EPS_STATUS
AND epsError.EPS_ERROR_CODE = pr.EPS_ERROR_CODE
AND viewTable.VIEW_TYPE_ID = pr.VIEW_TYPE_ID
AND files.pim_id = do.PIM_ID
AND do.DESIGN_OBJECT_ID IN
(
147086,149924,140458,135068,145197,134774,141837,138568,141731,138772,143769,141739,149113,148809,141072,141732,143974,147076,143972,141078,141925,134643,139701,141729,147078,139120,137097,147072,138261,149700,149701,139127,147070,149702,136766,146829,135762,140155,148459,138061,138762............................................. 200 such numbers
)
Indexed Colums:
索引列：
pr.DESIGN_OBJECT_SK
do.DESIGN_OBJECT_SK
do.DESIGN_OBJECT_ID
files.pim_id
Table
桌子
TABLE "PIM"."DESIGN_OBJECT"
(
"DESIGN_OBJECT_SK" NUMBER(*,0) NOT NULL ENABLE,
"PIM_ID" NUMBER(*,0) NOT NULL ENABLE,
"DESIGN_OBJECT_TYPE_SK" NUMBER(*,0) NOT NULL ENABLE,
"DESIGN_OBJECT_ID" VARCHAR2(40 BYTE) NOT NULL ENABLE,
"DIVISION_CD" NUMBER(*,0),
"STAT_IND" NUMBER(*,0) NOT NULL ENABLE,
"STAT_CHNG_TMST" TIMESTAMP (6),
"CRTD_BY" VARCHAR2(45 BYTE),
"CRT_TMST" TIMESTAMP (6),
"MDFD_BY" VARCHAR2(45 BYTE),
"CHNG_TMST" TIMESTAMP (6),
"UPDATE_CNT" NUMBER(*,0),
"GENDER" VARCHAR2(1 BYTE),
PRIMARY KEY ("DESIGN_OBJECT_SK")
)
TABLESPACE "PIM" ENABLE,
FOREIGN KEY ("DESIGN_OBJECT_TYPE_SK")
REFERENCES "PIM"."DESIGN_OBJECT_TYPE" ("DESIGN_OBJECT_TYPE_SK")
ON DELETE CASCADE ENABLE,
FOREIGN KEY ("PIM_ID")
REFERENCES "PIM"."FILES" ("PIM_ID")
ON DELETE CASCADE ENABLE
)
Table 2
表 2
CREATE TABLE "PIM"."PRODUCT_REPRESENTATION"
(
"PRODUCT_REPRESENTATION_SK" NUMBER(*,0) NOT NULL ENABLE,
"DESIGN_OBJECT_SK" NUMBER(*,0) NOT NULL ENABLE,
"VIEW_TYPE_ID" NUMBER(*,0) NOT NULL ENABLE,
"LAYER_NAME" VARCHAR2(255 BYTE),
"STAT_IND" NUMBER(*,0) NOT NULL ENABLE,
"STAT_CHNG_TMST" TIMESTAMP (6),
"CRTD_BY" VARCHAR2(45 BYTE),
"CRT_TMST" TIMESTAMP (6),
"MDFD_BY" VARCHAR2(45 BYTE),
"CHNG_TMST" TIMESTAMP (6),
"UPDATE_CNT" NUMBER(*,0),
"EPS_STATUS" VARCHAR2(30 BYTE) NOT NULL ENABLE,
"EPS_GENERATED_TIME" TIMESTAMP (6),
"EPS_ERROR_CODE" NUMBER,
"EPS_ERROR_DETAILS" VARCHAR2(500 BYTE),
"DEEPSERVER_ASSET_LAYER_ID" VARCHAR2(255 BYTE),
"PRODUCT_REPRESENTATION_LOC" VARCHAR2(255 BYTE),
PRIMARY KEY ("PRODUCT_REPRESENTATION_SK")
)
TABLESPACE "PIM" ENABLE,
FOREIGN KEY ("DESIGN_OBJECT_SK")
REFERENCES "PIM"."DESIGN_OBJECT" ("DESIGN_OBJECT_SK")
ON DELETE CASCADE ENABLE,
FOREIGN KEY ("VIEW_TYPE_ID")
REFERENCES "PIM"."VIEW_TYPE" ("VIEW_TYPE_ID")
ON DELETE CASCADE ENABLE,
CONSTRAINT "EPS_ERROR_CODE_FK"
FOREIGN KEY ("EPS_ERROR_CODE")
REFERENCES "PIM"."EPS_ERROR_CODES" ("EPS_ERROR_CODE")
ON DELETE CASCADE ENABLE,
CONSTRAINT "EPS_STATUS_FK"
FOREIGN KEY ("EPS_STATUS")
REFERENCES "PIM"."EPS_STATUS" ("EPS_STATUS_SK")
ON DELETE CASCADE ENABLE
)
回答by HLGEM
The first thing that is wrong is using implict join syntax. That is a SQL antipattern.
错误的第一件事是使用隐式连接语法。这是一个 SQL 反模式。
If you have a large list in the IN clause, have you tried putting them in a table instead and using a join?
如果 IN 子句中有一个很大的列表，您是否尝试过将它们放在一个表中并使用连接？
What database? Have you looked at your explain plan or execution plan to see where the slowdown is?
什么数据库？您是否查看了解释计划或执行计划以了解放缓的地方？
回答by ypercube??
Lets forget for a moment the empno BETWEEN 1 and 200suggestion and assume that you have you have t2.empno IN (3,7,...,5209)(200 entries).
让我们暂时忘记这个empno BETWEEN 1 and 200建议并假设您拥有t2.empno IN (3,7,...,5209)（200 个条目）。
You could also write your query (which is a hidden JOIN query) to the non-equivalentEXISTS query which would show same results (but possibly fewer rows) and should be faster than the JOIN:
您还可以将您的查询（这是一个隐藏的 JOIN 查询）写入非等效EXISTS 查询，该查询将显示相同的结果（但可能行数更少）并且应该比 JOIN 更快：
SELECT
t1.name
FROM
t1
WHERE EXISTS
( SELECT *
FROM t2
WHERE t2.id = t1.id
AND t2.empno IN (3,7,...,5209)
)
(Wild speculation)
（胡乱猜测）
If on the other hand, it's not even t2.empno IN (3,7,...,5209)but t2.empno IN (SELECT tx.empno FROM tx WHERE someConditions)andyou are using MySQL, then this is the root of your problem (MySQL is known to not handle field IN (SELECT f FROM x)in the best possible way). So, you could change the query into:
另一方面，如果它不是偶数t2.empno IN (3,7,...,5209)，t2.empno IN (SELECT tx.empno FROM tx WHERE someConditions)而您正在使用 MySQL，那么这就是问题的根源（众所周知，MySQL 无法field IN (SELECT f FROM x)以最佳方式处理）。因此，您可以将查询更改为：
SELECT
t1.name
FROM
t1
JOIN t2
ON t2.id = t2.id
JOIN tx
ON tx.empno = t2.empno
WHERE
someConditions
or even to:
甚至：
SELECT
t1.name
FROM
t1
WHERE EXISTS
( SELECT *
FROM t2
JOIN tx
ON tx.empno = t2.empno
WHERE t2.id = t1.id
AND someConditions
)
回答by Matthew
Don't use the cross-join.
不要使用交叉连接。
try this
尝试这个
SELECT
t1.name
FROM
t1
JOIN t2
ON t2.id = t1.id
WHERE
t2.empno IN (1,...,200)
EDIT: After you edit, seeing your multiple tables in the cartesian products, it is probably very important that you use proper JOINsyntax.
编辑：编辑后，在笛卡尔积中看到多个表，使用正确的JOIN语法可能非常重要。
