这几天开始了一个新的项目,和oracle数据打交道比较频繁,借此记录下使用的一些常见的问题和方法。
1、在使用union时,where条件跟在union结果后的速度比wher条件跟在每个SQL语句中要快很多。[1的用法比2的用法快将近一半]
eg:1> where条件跟在结果集后面:
select * from (
select m.ARCH_ID,m.JGDM,m.JGMC, m.CENTER_ID, 'SM_ARCHIVE' tablename from SM_ARCHIVE M
union
select p.ARCH_ID,p.JGDM,p.JGMC, p.CENTER_ID ,'DB_PROARCHIVE' tablename from DB_PROARCHIVE P
) newtable where CENTER_ID='100000' order by tablename
2>where 跟在单独的SQL语句中:
select * from (
select m.ARCH_ID,m.JGDM,m.JGMC, m.CENTER_ID, 'SM_ARCHIVE' tablename from SM_ARCHIVE M where CENTER_ID='100000'
union
select p.ARCH_ID,p.JGDM,p.JGMC, p.CENTER_ID ,'DB_PROARCHIVE' tablename from DB_PROARCHIVE P where CENTER_ID='100000'
) newtable order by tablename