我有两个视图，v_movimiento_no_duplicado和v_personas_no_duplicadas。这两个查询分别从表CLM020A和CLM010A中删除重复项。当我单独执行每个查询时，我会得到快速的返回，当我加入它们时也很快。问题是当我放置一些谓词时，例如：
SELECT *
FROM v_movimiento_no_duplicado
INNER JOIN v_personas_no_duplicadas USING (cedula_rel)
WHERE LENGTH (nombres_010) <= 30
AND LENGTH (apellidos_010) <= 30
--AND LENGTH (cedula_rel) <= 15;
没有最后一个谓词需要125毫秒（可以），但如果添加第三个谓词需要11秒。我现在还没有索引，但我尝试过三列中的每一列的索引，复合索引甚至基于函数的索引，但结果是相同或最差的。这是两种情况的计划：
有两个谓词（更快）：
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 27 | 7371 | 253 (2)| 00:00:04 |
|* 1 | HASH JOIN | | 27 | 7371 | 253 (2)| 00:00:04 |
|* 2 | HASH JOIN | | 27 | 7047 | 149 (3)| 00:00:02 |
|* 3 | HASH JOIN SEMI | | 14 | 1596 | 46 (5)| 00:00:01 |
| 4 | JOIN FILTER CREATE | :BF0000 | 14 | 1148 | 22 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | CLM010A | 14 | 1148 | 22 (0)| 00:00:01 |
| 6 | VIEW | VW_NSO_1 | 56 | 1792 | 23 (5)| 00:00:01 |
|* 7 | FILTER | | | | | |
| 8 | HASH GROUP BY | | 1 | 672 | 23 (5)| 00:00:01 |
| 9 | JOIN FILTER USE | :BF0000 | 5532 | 66384 | 22 (0)| 00:00:01 |
|* 10 | TABLE ACCESS FULL| CLM010A | 5532 | 66384 | 22 (0)| 00:00:01 |
| 11 | TABLE ACCESS FULL | CLM020A | 10734 | 1540K| 102 (0)| 00:00:02 |
| 12 | VIEW | VW_NSO_2 | 10734 | 125K| 104 (2)| 00:00:02 |
| 13 | HASH GROUP BY | | 10734 | 461K| 104 (2)| 00:00:02 |
|* 14 | TABLE ACCESS FULL | CLM020A | 10734 | 461K| 102 (0)| 00:00:02 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("M".ROWID="FILA")
2 - access("CEDULA_REL"="CEDULA_REL")
3 - access("CEDULA_REL"="CEDULA_REL")
5 - filter(LENGTH("NOMBRES_010")<=30 AND LENGTH("APELLIDOS_010")<=30)
7 - filter(COUNT("CEDULA_REL")=1)
10 - filter(SYS_OP_BLOOM_FILTER(:BF0000,"CEDULA_REL"))
14 - filter(("M"."TIPO_020"='E' OR "M"."TIPO_020"='S') AND
"M"."LUGARMOV_020" IS NOT NULL)
现在，有三个谓词（较慢）：
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 229 | 252 (2)| 00:00:04 |
|* 1 | FILTER | | | | | |
|* 2 | HASH JOIN | | 1 | 229 | 125 (1)| 00:00:02 |
|* 3 | TABLE ACCESS FULL | CLM010A | 1 | 82 | 22 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | CLM020A | 10734 | 1540K| 102 (0)| 00:00:02 |
|* 5 | FILTER | | | | | |
| 6 | HASH GROUP BY | | 1 | 12 | 23 (5)| 00:00:01 |
| 7 | TABLE ACCESS FULL| CLM010A | 5532 | 66384 | 22 (0)| 00:00:01 |
|* 8 | FILTER | | | | | |
| 9 | HASH GROUP BY | | 108 | 4752 | 104 (2)| 00:00:02 |
|* 10 | TABLE ACCESS FULL| CLM020A | 10734 | 461K| 102 (0)| 00:00:02 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (SELECT 0 FROM "LIMOV"."CLM010A" "P2" GROUP BY
"CEDULA_REL" HAVING "CEDULA_REL"=:B1 AND COUNT("CEDULA_REL")=1) AND
EXISTS (SELECT 0 FROM "LIMOV"."CLM020A" "M" WHERE ("M"."TIPO_020"='E'
OR "M"."TIPO_020"='S') AND "M"."LUGARMOV_020" IS NOT NULL GROUP BY
UPPER(TRIM("M"."LUGARMOV_020"))||UPPER("M"."TIPO_020")||TO_CHAR(INTERNAL
_FUNCTION("M"."FECHA_020"),'YYYYMMDDHH24MI')||"M"."CEDULA_REL" HAVING
MAX("M".ROWID)=:B2))
2 - access("CEDULA_REL"="CEDULA_REL")
3 - filter(LENGTH("NOMBRES_010")<=30 AND LENGTH("APELLIDOS_010")<=30
AND LENGTH("CEDULA_REL")<=15)
5 - filter("CEDULA_REL"=:B1 AND COUNT("CEDULA_REL")=1)
8 - filter(MAX("M".ROWID)=:B1)
10 - filter(("M"."TIPO_020"='E' OR "M"."TIPO_020"='S') AND
"M"."LUGARMOV_020" IS NOT NULL)
正如你所看到的，这两个计划是不同的。我没有太多的计划工作经验，任何帮助都将不胜感激。
--查看
CREATE VIEW V_MOVIMIENTO_NO_DUPLICADO SELECT "CEDULA_REL",
"TIPO_020",
"FECHA_020",
"LUGARMOV_020",
"CLM_020",
"TRASLADO_020",
"RESPALDADO",
"ESTADIA",
"DIRECCION",
"FECHA_LETRA",
"HORA_SERVER",
"USUARIO"
FROM movimiento m
WHERE m.ROWID IN ( SELECT MAX (m.ROWID) fila
FROM movimiento m
WHERE M.lugarmov_020 IS NOT NULL
AND M.tipo_020 IN ('E', 'S')
GROUP BY UPPER (TRIM (M.lugarmov_020))
|| UPPER (M.tipo_020)
|| TO_CHAR (M.fecha_020, 'YYYYMMDDHH24MI')
|| M.CEDULA_REL)
CREATE VIEW V_PERSONAS_NO_DUPLICADAS SELECT CEDULA_REL,
NOMBRES_010,
APELLIDOS_010,
FECHANAC_010,
NACIONALIDAD_010,
TVIAJERO_010,
RESPALDADO,
RESIDENCIA,
SEXO,
FREGISTRO,
PC,
USUARIO,
USERUPDATE,
DATEUPDATE
FROM persona P1
WHERE cedula_rel IN ( SELECT cedula_rel
FROM persona P2
GROUP BY cedula_rel
HAVING COUNT (cedula_rel) = 1)
--添加索引后的计划：
最快查询：（多花12秒）
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 261 | 133 (4)| 00:00:02 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 1 | 261 | 29 (7)| 00:00:01 |
| 3 | NESTED LOOPS | | 1 | 114 | 26 (8)| 00:00:01 |
| 4 | VIEW | VW_NSO_1 | 56 | 1792 | 23 (5)| 00:00:01 |
|* 5 | FILTER | | | | | |
| 6 | HASH GROUP BY | | 1 | 672 | 23 (5)| 00:00:01 |
| 7 | TABLE ACCESS FULL | CLM010A | 5532 | 66384 | 22 (0)| 00:00:01 |
|* 8 | TABLE ACCESS BY INDEX ROWID| CLM010A | 1 | 82 | 2 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | IDX_CEDULA_REL | 1 | | 1 (0)| 00:00:01 |
|* 10 | INDEX RANGE SCAN | IDX_CEDULA_REL_2 | 2 | | 1 (0)| 00:00:01 |
|* 11 | FILTER | | | | | |
| 12 | HASH GROUP BY | | 108 | 4752 | 104 (2)| 00:00:02 |
|* 13 | TABLE ACCESS FULL | CLM020A | 10734 | 461K| 102 (0)| 00:00:02 |
| 14 | TABLE ACCESS BY INDEX ROWID | CLM020A | 1 | 147 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter(COUNT("CEDULA_REL")=1)
8 - filter(LENGTH("NOMBRES_010")<=30)
9 - access("CEDULA_REL"="CEDULA_REL")
filter(LENGTH("CEDULA_REL")<=15)
10 - access("CEDULA_REL"="CEDULA_REL")
filter( EXISTS (SELECT 0 FROM "LIMOV"."CLM020A" "M" WHERE ("M"."TIPO_020"='E' OR
"M"."TIPO_020"='S') AND "M"."LUGARMOV_020" IS NOT NULL GROUP BY
UPPER(TRIM("M"."LUGARMOV_020"))||UPPER("M"."TIPO_020")||TO_CHAR(INTERNAL_FUNCTION("M"."FECH
A_020"),'YYYYMMDDHH24MI')||"M"."CEDULA_REL" HAVING MAX("M".ROWID)=:B1))
11 - filter(MAX("M".ROWID)=:B1)
13 - filter(("M"."TIPO_020"='E' OR "M"."TIPO_020"='S') AND "M"."LUGARMOV_020" IS NOT
NULL)
最慢查询：（2分钟取消）
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 241 | 116 (3)| 00:00:02 |
|* 1 | FILTER | | | | | |
| 2 | HASH GROUP BY | | 1 | 241 | 116 (3)| 00:00:02 |
| 3 | NESTED LOOPS | | | | | |
| 4 | NESTED LOOPS | | 1 | 241 | 11 (0)| 00:00:01 |
| 5 | NESTED LOOPS | | 1 | 94 | 8 (0)| 00:00:01 |
|* 6 | TABLE ACCESS BY INDEX ROWID| CLM010A | 1 | 82 | 7 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | IDX_CEDULA_LEN | 50 | | 2 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | IDX_CEDULA_REL | 1 | 12 | 1 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | IDX_CEDULA_REL_2 | 2 | | 1 (0)| 00:00:01 |
|* 10 | FILTER | | | | | |
| 11 | HASH GROUP BY | | 108 | 4752 | 104 (2)| 00:00:02 |
|* 12 | TABLE ACCESS FULL | CLM020A | 10734 | 461K| 102 (0)| 00:00:02 |
| 13 | TABLE ACCESS BY INDEX ROWID | CLM020A | 1 | 147 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(COUNT("CEDULA_REL")=1)
6 - filter(LENGTH("NOMBRES_010")<=30 AND LENGTH("APELLIDOS_010")<=30)
7 - access(LENGTH("CEDULA_REL")<=15)
8 - access("CEDULA_REL"="CEDULA_REL")
9 - access("CEDULA_REL"="CEDULA_REL")
filter( EXISTS (SELECT 0 FROM "LIMOV"."CLM020A" "M" WHERE ("M"."TIPO_020"='E' OR
"M"."TIPO_020"='S') AND "M"."LUGARMOV_020" IS NOT NULL GROUP BY
UPPER(TRIM("M"."LUGARMOV_020"))||UPPER("M"."TIPO_020")||TO_CHAR(INTERNAL_FUNCTION("M"."FECHA_
020"),'YYYYMMDDHH24MI')||"M"."CEDULA_REL" HAVING MAX("M".ROWID)=:B1))
10 - filter(MAX("M".ROWID)=:B1)
12 - filter(("M"."TIPO_020"='E' OR "M"."TIPO_020"='S') AND "M"."LUGARMOV_020" IS NOT NULL)
我解决了更改联接中视图顺序的问题，然后执行计划发生了变化，过滤器也发生了巨大变化。
最慢查询：
SELECT *
FROM v_movimiento_no_duplicado
INNER JOIN v_personas_no_duplicadas USING (cedula_rel)
WHERE LENGTH (nombres_010) <= 30
AND LENGTH (apellidos_010) <= 30
AND LENGTH (cedula_rel) <= 15;
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 229 | 252 (2)| 00:00:04 |
|* 1 | FILTER | | | | | |
|* 2 | HASH JOIN | | 1 | 229 | 125 (1)| 00:00:02 |
|* 3 | TABLE ACCESS FULL | CLM010A | 1 | 82 | 22 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | CLM020A | 10734 | 1540K| 102 (0)| 00:00:02 |
|* 5 | FILTER | | | | | |
| 6 | HASH GROUP BY | | 1 | 12 | 23 (5)| 00:00:01 |
| 7 | TABLE ACCESS FULL| CLM010A | 5532 | 66384 | 22 (0)| 00:00:01 |
|* 8 | FILTER | | | | | |
| 9 | HASH GROUP BY | | 108 | 4752 | 104 (2)| 00:00:02 |
|* 10 | TABLE ACCESS FULL| CLM020A | 10734 | 461K| 102 (0)| 00:00:02 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (SELECT 0 FROM "LIMOV"."CLM010A" "P2" GROUP BY
"CEDULA_REL" HAVING "CEDULA_REL"=:B1 AND COUNT("CEDULA_REL")=1) AND
EXISTS (SELECT 0 FROM "LIMOV"."CLM020A" "M" WHERE ("M"."TIPO_020"='E'
OR "M"."TIPO_020"='S') AND "M"."LUGARMOV_020" IS NOT NULL GROUP BY
UPPER(TRIM("M"."LUGARMOV_020"))||UPPER("M"."TIPO_020")||TO_CHAR(INTERNAL
_FUNCTION("M"."FECHA_020"),'YYYYMMDDHH24MI')||"M"."CEDULA_REL" HAVING
MAX("M".ROWID)=:B2))
2 - access("CEDULA_REL"="CEDULA_REL")
3 - filter(LENGTH("NOMBRES_010")<=30 AND LENGTH("APELLIDOS_010")<=30
AND LENGTH("CEDULA_REL")<=15)
5 - filter("CEDULA_REL"=:B1 AND COUNT("CEDULA_REL")=1)
8 - filter(MAX("M".ROWID)=:B1)
10 - filter(("M"."TIPO_020"='E' OR "M"."TIPO_020"='S') AND
"M"."LUGARMOV_020" IS NOT NULL)
检查过滤器1是否看起来有点贵。当我改变视图的顺序时，它就消失了。
SELECT *
FROM v_personas_no_duplicadas
INNER JOIN v_movimiento_no_duplicado USING (cedula_rel)
WHERE LENGTH (nombres_010) <= 30
AND LENGTH (apellidos_010) <= 30
AND LENGTH (cedula_rel) <= 15;
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 3822 | 253 (2)| 00:00:04 |
|* 1 | HASH JOIN SEMI | | 14 | 3822 | 253 (2)| 00:00:04 |
|* 2 | HASH JOIN | | 14 | 3374 | 229 (2)| 00:00:03 |
|* 3 | HASH JOIN | | 14 | 3206 | 125 (1)| 00:00:02 |
|* 4 | TABLE ACCESS FULL | CLM010A | 14 | 1148 | 22 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | CLM020A | 537 | 78939 | 102 (0)| 00:00:02 |
| 6 | VIEW | VW_NSO_1 | 10734 | 125K| 104 (2)| 00:00:02 |
| 7 | HASH GROUP BY | | 10734 | 461K| 104 (2)| 00:00:02 |
|* 8 | TABLE ACCESS FULL| CLM020A | 10734 | 461K| 102 (0)| 00:00:02 |
| 9 | VIEW | VW_NSO_2 | 56 | 1792 | 23 (5)| 00:00:01 |
|* 10 | FILTER | | | | | |
| 11 | HASH GROUP BY | | 1 | 672 | 23 (5)| 00:00:01 |
| 12 | TABLE ACCESS FULL| CLM010A | 5532 | 66384 | 22 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("CEDULA_REL"="CEDULA_REL")
2 - access("M".ROWID="FILA")
3 - access("CEDULA_REL"="CEDULA_REL")
4 - filter(LENGTH("NOMBRES_010")<=30 AND LENGTH("APELLIDOS_010")<=30)
5 - filter(LENGTH("CEDULA_REL")<=15)
8 - filter(("M"."TIPO_020"='E' OR "M"."TIPO_020"='S') AND
"M"."LUGARMOV_020" IS NOT NULL)
10 - filter(COUNT("CEDULA_REL")=1)