从一个视图里查询2000条数据,每条15项内容,查询时间是2秒,
怎么去优化
怎么去优化
解决方案 »
- 求一sql 谢谢
- 求一SQL语句:子表通过一个非主键属性排除主键所在的所有行,多谢!
- 2台PC和一个SCSI硬盘,可以实现 OracleRAC环境吗?
- oracle 定时任务 存储过程 临时表
- 字段类型是long类型,怎么输入值?在线等待
- 连接和会话的概念
- 帮忙啊
- 请问在sql server中如何在select的结果中添加行号如1,2,3。。。多谢!
- 我的ORACLE连不上了,各位大虾帮帮忙,急急急!!!!!!!!!!!
- 打开Oracle Enterprise Manager工具登陆的时候,如果选择登陆到Oracle Managerment Server会出错,提示:VTK-1004:读取客户机注册表发生
- 存储过程调用函数的问题
- 问个更新主键的问题
DROP VIEW ICOM_COMPETENCE_ASSESS_VIEW;/*==============================================================*/
/* View: ICOM_COMPETENCE_ASSESS_VIEW */
/*==============================================================*/
CREATE OR REPLACE VIEW ICOM_COMPETENCE_ASSESS_VIEW AS
SELECT A.PERSON_INNER_CODE AS PERSONINNERCODE,
A.PERSON_NAME AS PERSONNAME,
(SELECT D1.ITEM_NAME FROM ICOM_SYSTEM_DICTIONARY D1 WHERE D1.DICTIONARY_INNER_CODE=A.SEX) AS SEX,
A.AGE AS AGE,
A.UN_INNER_CODE AS UNINNERCODE,
A.UN_NAME AS UNNAME,
A.STATION_INNER_CODE AS STATIONINNERCODE,
A.STATION_NAME AS STATIONNAME,
TO_CHAR(A.BIRTHDAY,'yyyy-mm-dd') AS BIRTHDAY,
/*TO_CHAR(A.BEGIN_WORK_DATE,'yyyy-mm-dd') AS BEGINWORKDATE,
TO_CHAR(A.ENTERPRISE_DATE,'yyyy-mm-dd') AS ENTERPRISEDATE,
(SELECT D1.ITEM_NAME FROM ICOM_SYSTEM_DICTIONARY D1 WHERE D1.DICTIONARY_INNER_CODE = (SELECT MIN(D.CULTURE_INNER_CODE) FROM ICOM_PERSON_EDUCATION D WHERE D.PERSON_INNER_CODE = A.PERSON_INNER_CODE)) AS CULTURE,*/
(SELECT B1.ITEM_NAME FROM ICOM_SYSTEM_DICTIONARY B1 WHERE B1.DICTIONARY_INNER_CODE = B.COMPETENCE_INNER_CODE) AS COMPETENCENAME,
TO_CHAR(B.ASSESS_DATE,'yyyy-mm-dd') AS ASSESSDATE,
B.CERTIFICATE_CODE AS ASSESSCERTINO,
(SELECT M.ITEM_NAME FROM ICOM_SYSTEM_DICTIONARY M WHERE M.DICTIONARY_INNER_CODE = (SELECT M1.CLASS_ONE+10000000000 FROM ICOM_SYSTEM_DICTIONARY M1 WHERE M1.DICTIONARY_INNER_CODE = B.COMPETENCE_INNER_CODE)) AS ASSESSCOMPETENCE,
(SELECT C1.ITEM_NAME FROM ICOM_SYSTEM_DICTIONARY C1 WHERE C1.DICTIONARY_INNER_CODE = C.DUTY_INNER_CODE) AS DUTYNAME,
TO_CHAR(C.ENGAGE_DATE,'yyyy-mm-dd') AS ENGAGEDATE,
C.CERTIFICATE_CODE AS ENGAGECERTINO,
(SELECT N.ITEM_NAME FROM ICOM_SYSTEM_DICTIONARY N WHERE N.DICTIONARY_INNER_CODE = (SELECT N1.CLASS_ONE+10000000000 FROM ICOM_SYSTEM_DICTIONARY N1 WHERE N1.DICTIONARY_INNER_CODE = C.DUTY_INNER_CODE)) AS ENGAGEDUTY
/* B.COMPETENCE_INNER_CODE AS COMPETENCEINNERCODE,
C.DUTY_INNER_CODE AS DUTYINNERCODE,
B.WORK_TYPE_INNER_CODE AS ASSESSWORKTYPEINNER,
C.WORK_TYPE_INNER_CODE AS ENGAGEWORKTYPEINNER*/
FROM ICOM_PERSON_BASE_INFO_VIEW A,
ICOM_PERSON_COMPETENCE_ASSESS B,
ICOM_PERSON_COMPETENCE_ENGAGE C
WHERE B.PERSON_INNER_CODE (+)= A.PERSON_INNER_CODE
AND C.PERSON_INNER_CODE (+)= A.PERSON_INNER_CODE
AND (B.ASSESS_DATE = (SELECT MAX(B1.ASSESS_DATE) FROM ICOM_PERSON_COMPETENCE_ASSESS B1 WHERE B1.PERSON_INNER_CODE = B.PERSON_INNER_CODE) OR B.ASSESS_DATE IS NULL)
AND (C.ENGAGE_DATE = (SELECT MAX(C1.ENGAGE_DATE) FROM ICOM_PERSON_COMPETENCE_ENGAGE C1 WHERE C1.PERSON_INNER_CODE = C.PERSON_INNER_CODE) OR C.ENGAGE_DATE IS NULL)
AND ((B.COMPETENCE_INNER_CODE BETWEEN 10031000000 AND 10032000000) OR (B.COMPETENCE_INNER_CODE IS NULL))
AND ((C.DUTY_INNER_CODE BETWEEN 10031000000 AND 10032000000) OR (C.DUTY_INNER_CODE IS NULL));