oracle优化原则
1：insert 插入
(1):insert into /*+ append */ NOLOGGING
2: select 查询
(1):/*+ full(v) */ 全表查询 速度慢
(2):/*+ full(v) full(fd) */ 多个表
(3):/*+ parallel(4) */ 检索查询 效率高
3:with 临时表 as
（select * from 表）
只执行一次，效率快。
select * from 临时表 a,表2 b
where a.id = b.id
4:union all(性能高)和Union
并集--追加
5：left join 左连接
交集
6：case
when ... then...else
when ... then...else
end
7:nvl(count(1),0)
代表:如果值为空则赋一个默认值0，
不为空就为count(1)相当于if...else
8:exists
where exists (select 1 from hcpms_medl_page fmp
where fmp.visit_no=b.visit_no)
9：查询有条件有多个值(集合)
<isNotEmpty prepend=" " property="kpiCodeList">
<iterate prepend=" and B.KPI_ID in" open="(" close=")"
conjunction="," property="kpiCodeList">
#kpiCodeList[]#
</iterate>
</isNotEmpty>
10：相等情况判断
<isNotEqual property="visitType" compareValue="0" prepend="AND">
IBM.ATTENDANCE_TYPE = #visitType#
</isNotEqual>
11：分页
SELECT * FROM (SELECT CEIL(ROWNUM /#rows#) PAGE,
B.AhealthcareProviderCode MEDICAL_ORG_CODE
from hcpms_provide_info)
WHERE PAGE=#page#
12：插入示例：批量插入
<![CDATA[
INSERT INTO HCPMS_KPI_DEPT_GL
(ID_KPI_DEPT,
SOCIATION_CODE,
MEDICAL_ORG_CODE,
DEPARTMENT_CODE,
PERIOD,
KPI_TIME,
VISIT_TYPE,
KPI_CODE,
KPI_VALUE,
KPI_PARM1,
KPI_PARM2,
CREATED_BY,
UPDATED_BY,
IS_VALID)
]]>
<iterate conjunction="union all">
<![CDATA[ SELECT SYS_GUID(),
#item[].sociationCode#,
#item[].medicalCode#,
#item[].departmentCode#,
#item[].period#,
#item[].kpiTime#,
#item[].visitType#,
#item[].kpiCode#,
#item[].kpiValue#,
#item[].kpiParm1#,
#item[].kpiParm2#,
#item[].useId#,
#item[].useId#,
#item[].isValid#
FROM DUAL
]]>
</iterate>
13:修改--更新
update hcpms_kpi_batch
<dynamic prepend=" set ">
<isNotNull prepend="," property="status">
status=#status#
,date_updated=sysdate
</isNotNull>
<isNotNull prepend="," property="tempBatch">
tmp_batch=''
</isNotNull>
</dynamic>
where id_kpi_batch =#kpiId#