下面是查询视图的SQl语句：
SELECT LCNO,TRFG,CXFG,SLNM,SLAM,BYNM,EXPC,BYAM,CHNL,TRDT,TRTM,CUAC,FVDA,TODT FROM VIEW_TRANLIST_P001 WHERE STCD = 'S'
AND TRCD in('S2101','S2106','S2103','S2105','S3101') AND TRAC ='1101006120000023'
AND TRDT >='20100818' AND TRDT <='20100821'
视图的SQl:
CREATE OR REPLACE VIEW VIEW_TRANLIST_P001 AS
SELECT A."LCNO",A."TRDT",A."TRTM",A."TRCD",A."CUNO",A."CUAC",A."TRAC",A."CYEN",A."CATY",A."CXFG",A."TRTP",A."BYCY",A."BYNM",A."SLCY",A."SLNM",A."USPC",A."TRTY",A."RRDT",A."BYAM",A."EXPC",A."SLAM",A."PRAM",A."AMUT",A."FVDA",A."BUSD",A."SUSD",A."BRSY",A."USAM",A."OLNO",A."TCBY",A."TCSL",A."TBBY",A."TBSL",A."ELCB",A."ELCS",A."ELBB",A."ELBS",A."ERCB",A."ERCS",A."ERBB",A."ERBS",A."TRFG",A."STCD",A."ERCD",A."EXNA",A."EXNB",A."TRSN",A."OGCD",A."BKTL",A."BKTR",A."BKRL",A."BKRR",A."TODT",B.HDNO,B.CUDT,B.CUTM,B.TRID,B.BHID,B.CHNL,B.RQDT,B.RQTM,B.TRTL,B.TTYN,B.AUTL
FROM TRD_TRADEMSGHEAD_P001 B,TRD_TRANLIST_P001 A
WHERE B.HDNO=A.LCNO
UNION ALL
SELECT A."LCNO",A."TRDT",A."TRTM",A."TRCD",A."CUNO",A."CUAC",A."TRAC",A."CYEN",A."CATY",A."CXFG",A."TRTP",A."BYCY",A."BYNM",A."SLCY",A."SLNM",A."USPC",A."TRTY",A."RRDT",A."BYAM",A."EXPC",A."SLAM",A."PRAM",A."AMUT",A."FVDA",A."BUSD",A."SUSD",A."BRSY",A."USAM",A."OLNO",A."TCBY",A."TCSL",A."TBBY",A."TBSL",A."ELCB",A."ELCS",A."ELBB",A."ELBS",A."ERCB",A."ERCS",A."ERBB",A."ERBS",A."TRFG",A."STCD",A."ERCD",A."EXNA",A."EXNB",A."TRSN",A."OGCD",A."BKTL",A."BKTR",A."BKRL",A."BKRR",A."TODT",B.HDNO,B.CUDT,B.CUTM,B.TRID,B.BHID,B.CHNL,B.RQDT,B.RQTM,B.TRTL,B.TTYN,B.AUTL
FROM TRD_TRADEMSGHEAD_HIS_P001 B,TRD_TRANLIST_HIS_P001 A
WHERE B.HDNO=A.LCNO执行那个SQl语句，始终效率很低。请问如何优化。目前TRD_TRADEMSGHEAD_P001和TRD_TRADEMSGHEAD_HIS_P001在HDNO上创建了索引，但似乎TRD_TRADEMSGHEAD_P001表的索引没用到。
SELECT LCNO,TRFG,CXFG,SLNM,SLAM,BYNM,EXPC,BYAM,CHNL,TRDT,TRTM,CUAC,FVDA,TODT FROM VIEW_TRANLIST_P001 WHERE STCD = 'S'
AND TRCD in('S2101','S2106','S2103','S2105','S3101') AND TRAC ='1101006120000023'
AND TRDT >='20100818' AND TRDT <='20100821'
视图的SQl:
CREATE OR REPLACE VIEW VIEW_TRANLIST_P001 AS
SELECT A."LCNO",A."TRDT",A."TRTM",A."TRCD",A."CUNO",A."CUAC",A."TRAC",A."CYEN",A."CATY",A."CXFG",A."TRTP",A."BYCY",A."BYNM",A."SLCY",A."SLNM",A."USPC",A."TRTY",A."RRDT",A."BYAM",A."EXPC",A."SLAM",A."PRAM",A."AMUT",A."FVDA",A."BUSD",A."SUSD",A."BRSY",A."USAM",A."OLNO",A."TCBY",A."TCSL",A."TBBY",A."TBSL",A."ELCB",A."ELCS",A."ELBB",A."ELBS",A."ERCB",A."ERCS",A."ERBB",A."ERBS",A."TRFG",A."STCD",A."ERCD",A."EXNA",A."EXNB",A."TRSN",A."OGCD",A."BKTL",A."BKTR",A."BKRL",A."BKRR",A."TODT",B.HDNO,B.CUDT,B.CUTM,B.TRID,B.BHID,B.CHNL,B.RQDT,B.RQTM,B.TRTL,B.TTYN,B.AUTL
FROM TRD_TRADEMSGHEAD_P001 B,TRD_TRANLIST_P001 A
WHERE B.HDNO=A.LCNO
UNION ALL
SELECT A."LCNO",A."TRDT",A."TRTM",A."TRCD",A."CUNO",A."CUAC",A."TRAC",A."CYEN",A."CATY",A."CXFG",A."TRTP",A."BYCY",A."BYNM",A."SLCY",A."SLNM",A."USPC",A."TRTY",A."RRDT",A."BYAM",A."EXPC",A."SLAM",A."PRAM",A."AMUT",A."FVDA",A."BUSD",A."SUSD",A."BRSY",A."USAM",A."OLNO",A."TCBY",A."TCSL",A."TBBY",A."TBSL",A."ELCB",A."ELCS",A."ELBB",A."ELBS",A."ERCB",A."ERCS",A."ERBB",A."ERBS",A."TRFG",A."STCD",A."ERCD",A."EXNA",A."EXNB",A."TRSN",A."OGCD",A."BKTL",A."BKTR",A."BKRL",A."BKRR",A."TODT",B.HDNO,B.CUDT,B.CUTM,B.TRID,B.BHID,B.CHNL,B.RQDT,B.RQTM,B.TRTL,B.TTYN,B.AUTL
FROM TRD_TRADEMSGHEAD_HIS_P001 B,TRD_TRANLIST_HIS_P001 A
WHERE B.HDNO=A.LCNO执行那个SQl语句，始终效率很低。请问如何优化。目前TRD_TRADEMSGHEAD_P001和TRD_TRADEMSGHEAD_HIS_P001在HDNO上创建了索引，但似乎TRD_TRADEMSGHEAD_P001表的索引没用到。
FROM TRD_TRADEMSGHEAD_P001 B,TRD_TRANLIST_P001 AWHERE B.HDNO=A.LCNO
UNION ALL
...
FROM TRD_TRADEMSGHEAD_HIS_P001 B,TRD_TRANLIST_HIS_P001 A
把上面的重新写一下，现在的视图SQL中，你的索引是失效的。
有没有计算这个查询联合了多少次？为什么不用 join