以下两句SQL哪个更好些,效率高些??SELECT
conf_param_def_file_path
FROM t_re_conf_param_def_mtrx
WHERE((blck_cd = 1
AND svc_cl_cd = 1) OR(blck_cd = 1
AND svc_cl_cd = 2) OR(blck_cd = 2
AND svc_cl_cd = 1) OR(blck_cd = 2
AND svc_cl_cd = 2) OR(blck_cd = 3
AND svc_cl_cd = 1) OR(blck_cd = 3
AND svc_cl_cd = 2) OR(blck_cd = 4
AND svc_cl_cd = 1) OR(blck_cd = 2
AND svc_cl_cd = 2) OR(blck_cd = 5
AND svc_cl_cd = 1) OR(blck_cd = 5
AND svc_cl_cd = 2) OR(blck_cd = 6
AND svc_cl_cd = 1) OR(blck_cd = 6
AND svc_cl_cd = 2) OR(blck_cd = 7
AND svc_cl_cd = 1) OR(blck_cd = 7
AND svc_cl_cd = 2) OR(blck_cd = 8
AND svc_cl_cd = 1) OR(blck_cd = 8
AND svc_cl_cd = 2) OR(blck_cd = 9
AND svc_cl_cd = 1) OR(blck_cd = 9
AND svc_cl_cd = 2) OR(blck_cd = 10
AND svc_cl_cd = 1) OR(blck_cd = 10 AND dev_typ_cd = '905'
AND dev_mdl_cd = '1208'SELECT blck_cd,
svc_cl_cd,
dev_typ_cd,
dev_mdl_cd,conf_param_def_file_path
FROM t_re_conf_param_def_mtrx
WHERE(blck_cd = 1
AND svc_cl_cd = 1)
AND dev_typ_cd = '905'
AND dev_mdl_cd = '1208'
UNION
SELECT blck_cd,
svc_cl_cd,
dev_typ_cd,
dev_mdl_cd,conf_param_def_file_path
FROM t_re_conf_param_def_mtrx
WHERE(blck_cd = 1
AND svc_cl_cd = 2)
AND dev_typ_cd = '905'
AND dev_mdl_cd = '1208'
UNION
SELECT blck_cd,
svc_cl_cd,
dev_typ_cd,
dev_mdl_cd,conf_param_def_file_path
FROM t_re_conf_param_def_mtrx
WHERE(blck_cd = 2
AND svc_cl_cd = 1)
AND dev_typ_cd = '905'
AND dev_mdl_cd = '1208'
UNION
SELECT blck_cd,
svc_cl_cd,
dev_typ_cd,
dev_mdl_cd,conf_param_def_file_path
FROM t_re_conf_param_def_mtrx
WHERE(blck_cd = 2
AND svc_cl_cd = 2)
AND dev_typ_cd = '905'
AND dev_mdl_cd = '1208'
UNION
SELECT blck_cd,
svc_cl_cd,
dev_typ_cd,
dev_mdl_cd,conf_param_def_file_path
FROM t_re_conf_param_def_mtrx
WHERE(blck_cd = 3
AND svc_cl_cd = 1)
AND dev_typ_cd = '905'
AND dev_mdl_cd = '1208'
UNION
SELECT blck_cd,
svc_cl_cd,
dev_typ_cd,
dev_mdl_cd,conf_param_def_file_path
FROM t_re_conf_param_def_mtrx
WHERE(blck_cd = 3
AND svc_cl_cd = 2)
AND dev_typ_cd = '905'
AND dev_mdl_cd = '1208'
UNION
SELECT blck_cd,
svc_cl_cd,
dev_typ_cd,
dev_mdl_cd,conf_param_def_file_path
FROM t_re_conf_param_def_mtrx
WHERE(blck_cd = 4
AND svc_cl_cd = 1)
AND dev_typ_cd = '905'
AND dev_mdl_cd = '1208'
UNION
SELECT blck_cd,
svc_cl_cd,
dev_typ_cd,
dev_mdl_cd,conf_param_def_file_path
FROM t_re_conf_param_def_mtrx
WHERE(blck_cd = 4
AND svc_cl_cd = 2)
AND dev_typ_cd = '905'
AND dev_mdl_cd = '1208'
UNION
SELECT blck_cd,
svc_cl_cd,
dev_typ_cd,
dev_mdl_cd,conf_param_def_file_path
FROM t_re_conf_param_def_mtrx
WHERE(blck_cd = 4
AND svc_cl_cd = 1)
AND dev_typ_cd = '905'
AND dev_mdl_cd = '1208'
UNION
SELECT blck_cd,
svc_cl_cd,
dev_typ_cd,
dev_mdl_cd,conf_param_def_file_path
FROM t_re_conf_param_def_mtrx
WHERE(blck_cd = 4
AND svc_cl_cd = 2)
AND dev_typ_cd = '905'
AND dev_mdl_cd = '1208'
UNION
SELECT blck_cd,
svc_cl_cd,
dev_typ_cd,
dev_mdl_cd,conf_param_def_file_path
FROM t_re_conf_param_def_mtrx
WHERE(blck_cd = 5
AND svc_cl_cd = 1)
AND dev_typ_cd = '905'
AND dev_mdl_cd = '1208'
UNION
SELECT blck_cd,
svc_cl_cd,
dev_typ_cd,
dev_mdl_cd,conf_param_def_file_path
FROM t_re_conf_param_def_mtrx
WHERE(blck_cd = 5
AND svc_cl_cd = 2)
AND dev_typ_cd = '905'
AND dev_mdl_cd = '1208'
UNION
SELECT blck_cd,
svc_cl_cd,
dev_typ_cd,
dev_mdl_cd,conf_param_def_file_path
FROM t_re_conf_param_def_mtrx
WHERE(blck_cd = 6
AND svc_cl_cd = 1)
AND dev_typ_cd = '905'
AND dev_mdl_cd = '1208'
UNION
SELECT blck_cd,
svc_cl_cd,
dev_typ_cd,
dev_mdl_cd,conf_param_def_file_path
FROM t_re_conf_param_def_mtrx
WHERE(blck_cd = 6
AND svc_cl_cd = 2)
AND dev_typ_cd = '905'
AND dev_mdl_cd = '1208'
UNION
SELECT blck_cd,
svc_cl_cd,
dev_typ_cd,
dev_mdl_cd,conf_param_def_file_path
FROM t_re_conf_param_def_mtrx
WHERE(blck_cd = 7
AND svc_cl_cd = 1)
AND dev_typ_cd = '905'
AND dev_mdl_cd = '1208'
UNION
SELECT blck_cd,
svc_cl_cd,
dev_typ_cd,
dev_mdl_cd,conf_param_def_file_path
FROM t_re_conf_param_def_mtrx
WHERE(blck_cd = 7
AND svc_cl_cd = 2)
AND dev_typ_cd = '905'
AND dev_mdl_cd = '1208'
UNION
SELECT blck_cd,
svc_cl_cd,
dev_typ_cd,
dev_mdl_cd,conf_param_def_file_path
FROM t_re_conf_param_def_mtrx
WHERE(blck_cd = 8
AND svc_cl_cd = 1)
AND dev_typ_cd = '905'
AND dev_mdl_cd = '1208'
UNION
SELECT blck_cd,
svc_cl_cd,
dev_typ_cd,
dev_mdl_cd,conf_param_def_file_path
FROM t_re_conf_param_def_mtrx
WHERE(blck_cd = 8
AND svc_cl_cd = 2)
AND dev_typ_cd = '905'
AND dev_mdl_cd = '1208'
UNION
SELECT blck_cd,
svc_cl_cd,
dev_typ_cd,
dev_mdl_cd,conf_param_def_file_path
FROM t_re_conf_param_def_mtrx
WHERE(blck_cd = 9
AND svc_cl_cd = 1)
AND dev_typ_cd = '905'
AND dev_mdl_cd = '1208'
UNION
SELECT blck_cd,
svc_cl_cd,
dev_typ_cd,
dev_mdl_cd,conf_param_def_file_path
FROM t_re_conf_param_def_mtrx
WHERE(blck_cd = 9
AND svc_cl_cd = 2)
AND dev_typ_cd = '905'
AND dev_mdl_cd = '1208'
UNION
SELECT blck_cd,
svc_cl_cd,
dev_typ_cd,
dev_mdl_cd,conf_param_def_file_path
FROM t_re_conf_param_def_mtrx
WHERE(blck_cd = 10
AND svc_cl_cd = 1)
AND dev_typ_cd = '905'
AND dev_mdl_cd = '1208'
UNION
SELECT blck_cd,
svc_cl_cd,
dev_typ_cd,
dev_mdl_cd,conf_param_def_file_path
FROM t_re_conf_param_def_mtrx
WHERE(blck_cd = 10
AND svc_cl_cd = 2)
AND dev_typ_cd = '905'
AND dev_mdl_cd = '1208'
conf_param_def_file_path
FROM t_re_conf_param_def_mtrx
WHERE((blck_cd = 1
AND svc_cl_cd = 1) OR(blck_cd = 1
AND svc_cl_cd = 2) OR(blck_cd = 2
AND svc_cl_cd = 1) OR(blck_cd = 2
AND svc_cl_cd = 2) OR(blck_cd = 3
AND svc_cl_cd = 1) OR(blck_cd = 3
AND svc_cl_cd = 2) OR(blck_cd = 4
AND svc_cl_cd = 1) OR(blck_cd = 2
AND svc_cl_cd = 2) OR(blck_cd = 5
AND svc_cl_cd = 1) OR(blck_cd = 5
AND svc_cl_cd = 2) OR(blck_cd = 6
AND svc_cl_cd = 1) OR(blck_cd = 6
AND svc_cl_cd = 2) OR(blck_cd = 7
AND svc_cl_cd = 1) OR(blck_cd = 7
AND svc_cl_cd = 2) OR(blck_cd = 8
AND svc_cl_cd = 1) OR(blck_cd = 8
AND svc_cl_cd = 2) OR(blck_cd = 9
AND svc_cl_cd = 1) OR(blck_cd = 9
AND svc_cl_cd = 2) OR(blck_cd = 10
AND svc_cl_cd = 1) OR(blck_cd = 10 AND dev_typ_cd = '905'
AND dev_mdl_cd = '1208'SELECT blck_cd,
svc_cl_cd,
dev_typ_cd,
dev_mdl_cd,conf_param_def_file_path
FROM t_re_conf_param_def_mtrx
WHERE(blck_cd = 1
AND svc_cl_cd = 1)
AND dev_typ_cd = '905'
AND dev_mdl_cd = '1208'
UNION
SELECT blck_cd,
svc_cl_cd,
dev_typ_cd,
dev_mdl_cd,conf_param_def_file_path
FROM t_re_conf_param_def_mtrx
WHERE(blck_cd = 1
AND svc_cl_cd = 2)
AND dev_typ_cd = '905'
AND dev_mdl_cd = '1208'
UNION
SELECT blck_cd,
svc_cl_cd,
dev_typ_cd,
dev_mdl_cd,conf_param_def_file_path
FROM t_re_conf_param_def_mtrx
WHERE(blck_cd = 2
AND svc_cl_cd = 1)
AND dev_typ_cd = '905'
AND dev_mdl_cd = '1208'
UNION
SELECT blck_cd,
svc_cl_cd,
dev_typ_cd,
dev_mdl_cd,conf_param_def_file_path
FROM t_re_conf_param_def_mtrx
WHERE(blck_cd = 2
AND svc_cl_cd = 2)
AND dev_typ_cd = '905'
AND dev_mdl_cd = '1208'
UNION
SELECT blck_cd,
svc_cl_cd,
dev_typ_cd,
dev_mdl_cd,conf_param_def_file_path
FROM t_re_conf_param_def_mtrx
WHERE(blck_cd = 3
AND svc_cl_cd = 1)
AND dev_typ_cd = '905'
AND dev_mdl_cd = '1208'
UNION
SELECT blck_cd,
svc_cl_cd,
dev_typ_cd,
dev_mdl_cd,conf_param_def_file_path
FROM t_re_conf_param_def_mtrx
WHERE(blck_cd = 3
AND svc_cl_cd = 2)
AND dev_typ_cd = '905'
AND dev_mdl_cd = '1208'
UNION
SELECT blck_cd,
svc_cl_cd,
dev_typ_cd,
dev_mdl_cd,conf_param_def_file_path
FROM t_re_conf_param_def_mtrx
WHERE(blck_cd = 4
AND svc_cl_cd = 1)
AND dev_typ_cd = '905'
AND dev_mdl_cd = '1208'
UNION
SELECT blck_cd,
svc_cl_cd,
dev_typ_cd,
dev_mdl_cd,conf_param_def_file_path
FROM t_re_conf_param_def_mtrx
WHERE(blck_cd = 4
AND svc_cl_cd = 2)
AND dev_typ_cd = '905'
AND dev_mdl_cd = '1208'
UNION
SELECT blck_cd,
svc_cl_cd,
dev_typ_cd,
dev_mdl_cd,conf_param_def_file_path
FROM t_re_conf_param_def_mtrx
WHERE(blck_cd = 4
AND svc_cl_cd = 1)
AND dev_typ_cd = '905'
AND dev_mdl_cd = '1208'
UNION
SELECT blck_cd,
svc_cl_cd,
dev_typ_cd,
dev_mdl_cd,conf_param_def_file_path
FROM t_re_conf_param_def_mtrx
WHERE(blck_cd = 4
AND svc_cl_cd = 2)
AND dev_typ_cd = '905'
AND dev_mdl_cd = '1208'
UNION
SELECT blck_cd,
svc_cl_cd,
dev_typ_cd,
dev_mdl_cd,conf_param_def_file_path
FROM t_re_conf_param_def_mtrx
WHERE(blck_cd = 5
AND svc_cl_cd = 1)
AND dev_typ_cd = '905'
AND dev_mdl_cd = '1208'
UNION
SELECT blck_cd,
svc_cl_cd,
dev_typ_cd,
dev_mdl_cd,conf_param_def_file_path
FROM t_re_conf_param_def_mtrx
WHERE(blck_cd = 5
AND svc_cl_cd = 2)
AND dev_typ_cd = '905'
AND dev_mdl_cd = '1208'
UNION
SELECT blck_cd,
svc_cl_cd,
dev_typ_cd,
dev_mdl_cd,conf_param_def_file_path
FROM t_re_conf_param_def_mtrx
WHERE(blck_cd = 6
AND svc_cl_cd = 1)
AND dev_typ_cd = '905'
AND dev_mdl_cd = '1208'
UNION
SELECT blck_cd,
svc_cl_cd,
dev_typ_cd,
dev_mdl_cd,conf_param_def_file_path
FROM t_re_conf_param_def_mtrx
WHERE(blck_cd = 6
AND svc_cl_cd = 2)
AND dev_typ_cd = '905'
AND dev_mdl_cd = '1208'
UNION
SELECT blck_cd,
svc_cl_cd,
dev_typ_cd,
dev_mdl_cd,conf_param_def_file_path
FROM t_re_conf_param_def_mtrx
WHERE(blck_cd = 7
AND svc_cl_cd = 1)
AND dev_typ_cd = '905'
AND dev_mdl_cd = '1208'
UNION
SELECT blck_cd,
svc_cl_cd,
dev_typ_cd,
dev_mdl_cd,conf_param_def_file_path
FROM t_re_conf_param_def_mtrx
WHERE(blck_cd = 7
AND svc_cl_cd = 2)
AND dev_typ_cd = '905'
AND dev_mdl_cd = '1208'
UNION
SELECT blck_cd,
svc_cl_cd,
dev_typ_cd,
dev_mdl_cd,conf_param_def_file_path
FROM t_re_conf_param_def_mtrx
WHERE(blck_cd = 8
AND svc_cl_cd = 1)
AND dev_typ_cd = '905'
AND dev_mdl_cd = '1208'
UNION
SELECT blck_cd,
svc_cl_cd,
dev_typ_cd,
dev_mdl_cd,conf_param_def_file_path
FROM t_re_conf_param_def_mtrx
WHERE(blck_cd = 8
AND svc_cl_cd = 2)
AND dev_typ_cd = '905'
AND dev_mdl_cd = '1208'
UNION
SELECT blck_cd,
svc_cl_cd,
dev_typ_cd,
dev_mdl_cd,conf_param_def_file_path
FROM t_re_conf_param_def_mtrx
WHERE(blck_cd = 9
AND svc_cl_cd = 1)
AND dev_typ_cd = '905'
AND dev_mdl_cd = '1208'
UNION
SELECT blck_cd,
svc_cl_cd,
dev_typ_cd,
dev_mdl_cd,conf_param_def_file_path
FROM t_re_conf_param_def_mtrx
WHERE(blck_cd = 9
AND svc_cl_cd = 2)
AND dev_typ_cd = '905'
AND dev_mdl_cd = '1208'
UNION
SELECT blck_cd,
svc_cl_cd,
dev_typ_cd,
dev_mdl_cd,conf_param_def_file_path
FROM t_re_conf_param_def_mtrx
WHERE(blck_cd = 10
AND svc_cl_cd = 1)
AND dev_typ_cd = '905'
AND dev_mdl_cd = '1208'
UNION
SELECT blck_cd,
svc_cl_cd,
dev_typ_cd,
dev_mdl_cd,conf_param_def_file_path
FROM t_re_conf_param_def_mtrx
WHERE(blck_cd = 10
AND svc_cl_cd = 2)
AND dev_typ_cd = '905'
AND dev_mdl_cd = '1208'
"Optimizer" "Cost" "Cardinality" "Bytes" "Partition Start" "Partition Stop" "Partition Id" "ACCESS PREDICATES" "FILTER PREDICATES"
"SELECT STATEMENT" "ALL_ROWS" "8" "5" "165" "" "" "" "" ""
"TABLE ACCESS(FULL) NGN.T_RE_CONF_PARAM_DEF_MTRX" "ANALYZED" "8" "5" "165" "" "" "" "" ""DEV_MDL_CD"=1208 AND "DEV_TYP_CD"=905 AND ("BLCK_CD"=1 AND "SVC_CL_CD"=1 OR "BLCK_CD"=1 AND "SVC_CL_CD"=2 OR "BLCK_CD"=2 AND "SVC_CL_CD"=1 OR "BLCK_CD"=2 AND "SVC_CL_CD"=2 OR "BLCK_CD"=3 AND "SVC_CL_CD"=1 OR "BLCK_CD"=3 AND "SVC_CL_CD"=2 OR "BLCK_CD"=4 AND "SVC_CL_CD"=1 OR "BLCK_CD"=5 AND "SVC_CL_CD"=1 OR "BLCK_CD"=5 AND "SVC_CL_CD"=2 OR "BLCK_CD"=6 AND "SVC_CL_CD"=1 OR "BLCK_CD"=6 AND "SVC_CL_CD"=2 OR "BLCK_CD"=7 AND "SVC_CL_CD"=1 OR "BLCK_CD"=7 AND "SVC_CL_CD"=2 OR "BLCK_CD"=8 AND "SVC_CL_CD"=1 OR "BLCK_CD"=8 AND "SVC_CL_CD"=2 OR "BLCK_CD"=9 AND "SVC_CL_CD"=1 OR "BLCK_CD"=9 AND "SVC_CL_CD"=2 OR "BLCK_CD"=10 AND "SVC_CL_CD"=1 OR "BLCK_CD"=10 AND "SVC_CL_CD"=2 OR "BLCK_CD"=11 AND "SVC_CL_CD"=1 OR "BLCK_CD"=11 AND "SVC_CL_CD"=2 OR "BLCK_CD"=12 AND "SVC_CL_CD"=1 OR "BLCK_CD"=12 AND "SVC_CL_CD"=2 OR "BLCK_CD"=13 AND "SVC_CL_CD"=1 OR "BLCK_CD"=13 AND "SVC_CL_CD"=2 OR "BLCK_CD"=14 AND "SVC_CL_CD"=1 OR "BLCK_CD"=14 AND "SVC_CL_CD"=2 OR "BLCK_CD"=15 AND "SVC_CL_CD"=1 OR "BLCK_CD"=15 AND "SVC_CL_CD"=2 OR "BLCK_CD"=16 AND "SVC_CL_CD"=1 OR "BLCK_CD"=16 AND "SVC_CL_CD"=2 OR "BLCK_CD"=17 AND "SVC_CL_CD"=1 OR "BLCK_CD"=17 AND "SVC_CL_CD"=2 OR "BLCK_CD"=18 AND "SVC_CL_CD"=1 OR "BLCK_CD"=18 AND "SVC_CL_CD"=2 OR "BLCK_CD"=19 AND "SVC_CL_CD"=1 OR "BLCK_CD"=19 AND "SVC_CL_CD"=2 OR "BLCK_CD"=20 AND "SVC_CL_CD"=1 OR "BLCK_CD"=20 AND "SVC_CL_CD"=2)"第二条的优化结果
"Optimizer" "Cost" "Cardinality" "Bytes" "Partition Start" "Partition Stop" "Partition Id" "ACCESS PREDICATES" "FILTER PREDICATES"
"SELECT STATEMENT" "ALL_ROWS" "132" "44" "1452" "" "" "" "" ""
"SORT(UNIQUE)" "" "132" "44" "1452" "" "" "" "" ""
"UNION-ALL" "" "" "" "" "" "" "" "" ""
"TABLE ACCESS(BY INDEX ROWID) NGN.T_RE_CONF_PARAM_DEF_MTRX" "ANALYZED" "2" "1" "33" "" "" "" "" ""
"INDEX(UNIQUE SCAN) NGN.PK_T_RE_CONF_PARAM_DEF_MTRX" "ANALYZED" "1" "1" "" "" "" "" ""BLCK_CD"=1 AND "SVC_CL_CD"=1 AND "DEV_TYP_CD"=905 AND "DEV_MDL_CD"=1208" ""
"TABLE ACCESS(BY INDEX ROWID) NGN.T_RE_CONF_PARAM_DEF_MTRX" "ANALYZED" "2" "1" "33" "" "" "" "" ""
"INDEX(UNIQUE SCAN) NGN.PK_T_RE_CONF_PARAM_DEF_MTRX" "ANALYZED" "1" "1" "" "" "" "" ""BLCK_CD"=1 AND "SVC_CL_CD"=2 AND "DEV_TYP_CD"=905 AND "DEV_MDL_CD"=1208" ""
"TABLE ACCESS(BY INDEX ROWID) NGN.T_RE_CONF_PARAM_DEF_MTRX" "ANALYZED" "2" "1" "33" "" "" "" "" ""
"INDEX(UNIQUE SCAN) NGN.PK_T_RE_CONF_PARAM_DEF_MTRX" "ANALYZED" "1" "1" "" "" "" "" ""BLCK_CD"=2 AND "SVC_CL_CD"=1 AND "DEV_TYP_CD"=905 AND "DEV_MDL_CD"=1208" ""
"TABLE ACCESS(BY INDEX ROWID) NGN.T_RE_CONF_PARAM_DEF_MTRX" "ANALYZED" "2" "1" "33" "" "" "" "" ""
"INDEX(UNIQUE SCAN) NGN.PK_T_RE_CONF_PARAM_DEF_MTRX" "ANALYZED" "1" "1" "" "" "" "" ""BLCK_CD"=2 AND "SVC_CL_CD"=2 AND "DEV_TYP_CD"=905 AND "DEV_MDL_CD"=1208" ""
"TABLE ACCESS(BY INDEX ROWID) NGN.T_RE_CONF_PARAM_DEF_MTRX" "ANALYZED" "2" "1" "33" "" "" "" "" ""
"INDEX(UNIQUE SCAN) NGN.PK_T_RE_CONF_PARAM_DEF_MTRX" "ANALYZED" "1" "1" "" "" "" "" ""BLCK_CD"=3 AND "SVC_CL_CD"=1 AND "DEV_TYP_CD"=905 AND "DEV_MDL_CD"=1208" ""
"TABLE ACCESS(BY INDEX ROWID) NGN.T_RE_CONF_PARAM_DEF_MTRX" "ANALYZED" "2" "1" "33" "" "" "" "" ""
"INDEX(UNIQUE SCAN) NGN.PK_T_RE_CONF_PARAM_DEF_MTRX" "ANALYZED" "1" "1" "" "" "" "" ""BLCK_CD"=3 AND "SVC_CL_CD"=2 AND "DEV_TYP_CD"=905 AND "DEV_MDL_CD"=1208" ""
"TABLE ACCESS(BY INDEX ROWID) NGN.T_RE_CONF_PARAM_DEF_MTRX" "ANALYZED" "2" "1" "33" "" "" "" "" ""
"INDEX(UNIQUE SCAN) NGN.PK_T_RE_CONF_PARAM_DEF_MTRX" "ANALYZED" "1" "1" "" "" "" "" ""BLCK_CD"=4 AND "SVC_CL_CD"=1 AND "DEV_TYP_CD"=905 AND "DEV_MDL_CD"=1208" ""
"TABLE ACCESS(BY INDEX ROWID) NGN.T_RE_CONF_PARAM_DEF_MTRX" "ANALYZED" "2" "1" "33" "" "" "" "" ""
"INDEX(UNIQUE SCAN) NGN.PK_T_RE_CONF_PARAM_DEF_MTRX" "ANALYZED" "1" "1" "" "" "" "" ""BLCK_CD"=4 AND "SVC_CL_CD"=2 AND "DEV_TYP_CD"=905 AND "DEV_MDL_CD"=1208" ""
"TABLE ACCESS(BY INDEX ROWID) NGN.T_RE_CONF_PARAM_DEF_MTRX" "ANALYZED" "2" "1" "33" "" "" "" "" ""
"INDEX(UNIQUE SCAN) NGN.PK_T_RE_CONF_PARAM_DEF_MTRX" "ANALYZED" "1" "1" "" "" "" "" ""BLCK_CD"=4 AND "SVC_CL_CD"=1 AND "DEV_TYP_CD"=905 AND "DEV_MDL_CD"=1208" ""
"TABLE ACCESS(BY INDEX ROWID) NGN.T_RE_CONF_PARAM_DEF_MTRX" "ANALYZED" "2" "1" "33" "" "" "" "" ""
"INDEX(UNIQUE SCAN) NGN.PK_T_RE_CONF_PARAM_DEF_MTRX" "ANALYZED" "1" "1" "" "" "" "" ""BLCK_CD"=4 AND "SVC_CL_CD"=2 AND "DEV_TYP_CD"=905 AND "DEV_MDL_CD"=1208" ""
"TABLE ACCESS(BY INDEX ROWID) NGN.T_RE_CONF_PARAM_DEF_MTRX" "ANALYZED" "2" "1" "33" "" "" "" "" ""
"INDEX(UNIQUE SCAN) NGN.PK_T_RE_CONF_PARAM_DEF_MTRX" "ANALYZED" "1" "1" "" "" "" "" ""BLCK_CD"=5 AND "SVC_CL_CD"=1 AND "DEV_TYP_CD"=905 AND "DEV_MDL_CD"=1208" ""
"TABLE ACCESS(BY INDEX ROWID) NGN.T_RE_CONF_PARAM_DEF_MTRX" "ANALYZED" "2" "1" "33" "" "" "" "" ""
"INDEX(UNIQUE SCAN) NGN.PK_T_RE_CONF_PARAM_DEF_MTRX" "ANALYZED" "1" "1" "" "" "" "" ""BLCK_CD"=5 AND "SVC_CL_CD"=2 AND "DEV_TYP_CD"=905 AND "DEV_MDL_CD"=1208" ""
"TABLE ACCESS(BY INDEX ROWID) NGN.T_RE_CONF_PARAM_DEF_MTRX" "ANALYZED" "2" "1" "33" "" "" "" "" ""
"INDEX(UNIQUE SCAN) NGN.PK_T_RE_CONF_PARAM_DEF_MTRX" "ANALYZED" "1" "1" "" "" "" "" ""BLCK_CD"=6 AND "SVC_CL_CD"=1 AND "DEV_TYP_CD"=905 AND "DEV_MDL_CD"=1208" ""
"TABLE ACCESS(BY INDEX ROWID) NGN.T_RE_CONF_PARAM_DEF_MTRX" "ANALYZED" "2" "1" "33" "" "" "" "" ""
"INDEX(UNIQUE SCAN) NGN.PK_T_RE_CONF_PARAM_DEF_MTRX" "ANALYZED" "1" "1" "" "" "" "" ""BLCK_CD"=6 AND "SVC_CL_CD"=2 AND "DEV_TYP_CD"=905 AND "DEV_MDL_CD"=1208" ""
or--编译时MS_SQL编译了union
--oracle不太清楚
都差不多.
如果table中數據量大,而且沒有索引或者索引不得儅的話,union 那是相當的慢的
(我在MSSQL中測過(這兩天剛好修改了個這方面的程序),90W筆數據,如果沒有添加索引的話,我的SQL有9個union,執行下來差不多8分鐘,用in大概40秒左右;當然如果索引的黨的話,兩者基本上不相上下,1秒左右就完了)
(
code int,
name int
)insert into tablea values (1,2);select a.code,a.name from tablea a where a.code =1 or a.name =2;返回一条记录逻辑上等价于select a.code,a.name from tablea a where a.code =1
union
select a.code,a.name from tablea a where a.name =2;
返回一条记录。而不等价于select a.code,a.name from tablea a where a.code =1
union all
select a.code,a.name from tablea a where a.name =2;
返回两条记录。
我所说的or与union all等价是指相同列等于不同值的or连接。对于不同列的or条件通常情况下是无法转换为union all的,也无法转换为union的。
你所举的例子是假设结果集中没有重复数据,所以是可以转换为union,但如果没有执行查询前并不能确定记录集中是否有重复数据,那么转换为union后就过滤掉了那些重复数据。
1.UNION来求出结果集,并做重复的记录过滤
2.OR 某条件满足,即可以忽略之后的条件
假如在相关字段上建立索引,我相信应该更加快。UNION会默认执行SORT操作。UNION执行的是ROWID的全检索,假如这次相关字段上有索引,肯定快过UNION的动作。
UNION还需要重新安排一些内存来保存临时的纪录。而OR不需要。所以,花掉的COST远低于UNION。
说明它只有编译一句;而第二个用多个union 连接select ....from tb...
要把多个select语句连接起来一起编译,要的时间多oracle编译语句是最花费时间的,
编译好一次后,以后再执行这样的语句就不需要再编译
SELECT
conf_param_def_file_path
FROM t_re_conf_param_def_mtrx
WHERE(blck_cd = 1 or blck_cd = 2)
and (svc_cl_cd = 1 or svc_cl_cd = 2
or svc_cl_cd = 3 or svc_cl_cd = 4
or svc_cl_cd = 5 or svc_cl_cd = 6
or svc_cl_cd = 7 or svc_cl_cd = 8
or svc_cl_cd = 9 or svc_cl_cd = 10)
and dev_typ_cd = '905' and dev_mdl_cd = '1208';
SELECT
conf_param_def_file_path
FROM t_re_conf_param_def_mtrx
WHERE blck_cd BETWEEN 1 AND 10 AND svc_cl_cd IN (1, 2)
AND dev_typ_cd = '905' AND dev_mdl_cd = '1208'
是等价的
建议SQL:SELECT conf_param_def_file_path
FROM t_re_conf_param_def_mtrx
WHERE blck_cd IN (1,2,3,4,5,6,7,8,9,10) AND svc_cl_cd IN (1,2)
AND dev_typ_cd = '905'
AND dev_mdl_cd = '1208'
若非要比较二者的性能,建议把“UNION”改为“UNION ALL”。