首页 新闻 会员 周边 捐助

View 表性能优化

0
悬赏园豆:20 [待解决问题]

本人项目需要建立以下Oracle数据库的View表,但在PL/SQL Developer上执行,4077条记录,运行要49.502seconds,请问,有什么方法优化下面的View表,谢谢。

create or replace view view_project_test as
select A.CLASS_ID,A.OBJECT_ID,A.TDM_ID,A.CREATION_DATE,A.MODIFICATION_DATE,A.CN_COMMENT,A.TDM_DESCRIPTION
,A.TDM_RISK,A.UNDER_OPERATION,A.TDM_BL_START_DATE
,A.TDM_BL_FINISH_DATE,A.TDM_START_DATE,A.TDM_FINISH_DATE,A.TDM_DURATION,A.TDM_ACT_DURATION
,A.TDM_DURATION_FORMAT,A.FILE_NAME,A.DIRECTORY,A.CAD_REF_FILE_NAME,A.CAD_REF_DIRECTORY
,A.TDM_CAD_DIRTYFLAG,A.TDM_FILE_ID,A.TDM_CFO_FLAG,A.TDM_COMPONENT_NAME,A.TDM_COMPONENT_MODES
,A.TDM_FILE_VERSION,A.TDM_PRIORITY,TDM_PCT_COMPLETE,A.TDM_BUDGET,A.TDM_ACT_COST,A.TDM_PRELIMINARY_ALERT
,A.TDM_ALERT_INTERVAL,A.TDM_UPD_SPEC_FLDS_MSK,A.CN_PRODUCT_NUMBER,A.CN_PURPOSE
,A.CN_SPECIFICATION,A.CN_ORDER_ID,A.CN_MODEL_NO
,B.CN_RFQ_FROM_LOCATE,B.CN_END_CUS_LOCATE
,B.CN_END_CUS_CODE,B.CN_CUS_PART_NO,B.CN_MODIFY_MODEL,B.CN_LIFECYCLE_MONTH,B.CN_PROJECT_QTY
,B.CN_API_SHARE,B.CN_API_SHARE_QTY,B.CN_EXCHANGE_RATE,B.CN_TARGET_PRICE,B.CN_TARGET_MAT_COST
,B.CN_GROSS_MARGIN,B.CN_REVENUE,B.CN_COMPETITOR_01,B.CN_COMPETITOR_02,B.CN_COMPETITOR_03
,B.CN_INPUT_VOL_RANGE,B.CN_EFFICIENCY,B.CN_DIMENSION_LWH,B.CN_TEST_SPEC_REQ,B.CN_OUT_VOLTAGE_01
,B.CN_OUT_VOLTAGE_02,B.CN_OUT_VOLTAGE_03,B.CN_OUT_VOLTAGE_04,B.CN_OUT_VOLTAGE_05,B.CN_OUT_VOLTAGE_06
,B.CN_OUT_VOLTAGE_07,B.CN_OUT_VOLTAGE_08,B.CN_OUT_VOLTAGE_09,B.CN_OUT_VOLTAGE_10
,B.CN_OUT_CURRENT_01,B.CN_OUT_CURRENT_02,B.CN_OUT_CURRENT_03,B.CN_OUT_CURRENT_04,B.CN_OUT_CURRENT_05
,B.CN_OUT_CURRENT_06,B.CN_OUT_CURRENT_07,B.CN_OUT_CURRENT_08,B.CN_OUT_CURRENT_09,B.CN_OUT_CURRENT_10
,B.CN_SPEC_OTHER,B.CN_PROTOTYPE_DATE,B.CN_PROTOTYPE_QTY,B.CN_EVT_DATE,B.CN_EVT_QTY,B.CN_EE_COST
,B.CN_LABOR_HOUR_MIN,B.CN_ME_COST,B.CN_TOOLING_COST,B.CN_RD_TEAM,B.CN_MODIFY_MODEL_PM,B.CN_MODEL_NO_PM
,B.CN_SAFETY_COST,B.CN_LABOR_COST,B.CN_OVERHEAD_COST,B.CN_IUNIT_COST,B.CN_CANCEL_DESC,B.CN_WINNER_NAME
,B.CN_WINNER_PRICE,B.CN_AWARD_DATE,B.CN_SELL_PRICE
,C.TDM_IMAGE,C.DESCRIPTION

--BL
,case when A.CN_TEMPLATE_PROJECT =0 then 'False' else 'True' end CN_TEMPLATE_PROJECT
,case when A.TDM_RESTRICTED=0 then 'False' else 'True' end TDM_RESTRICTED
,case when A.TDM_IS_TEMPLATE=0 then 'False' else 'True' end TDM_IS_TEMPLATE

--RTC
,(select LOGIN ||'('||LAST_NAME||FIRST_NAME||')' from USERS where OBJECT_ID=A.USER_OBJECT_ID)  as USER_OBJECT_ID
,(select LOGIN ||'('||LAST_NAME||FIRST_NAME||')' from USERS where OBJECT_ID=A.USER_ID_MOD) as USER_ID_MOD

,(select TDM_DESCRIPTION from TDM_MECHANISM_TYPE where OBJECT_ID=A.TDM_SUPPORTED_CLB) as TDM_SUPPORTED_CLB
,(select TDM_DESCRIPTION from TDM_VAULTS where OBJECT_ID=A.VAULT_OBJECT_ID) as VAULT_OBJECT_ID
,(select TDM_DESCRIPTION from TDM_INTEGRATIONS where OBJECT_ID=A.TDM_INTEGRATION_MANAGED) as TDM_INTEGRATION_MANAGED
,(select TDM_DESCRIPTION from TN_PROJECT_SERIES where OBJECT_ID=B.CN_PROJECT_SERIES ) as CN_PROJECT_SERIES
,(select TDM_DESCRIPTION from TN_PRODUCTS where OBJECT_ID=B.CN_APPLICATION ) as CN_APPLICATION
,(select TDM_DESCRIPTION from TN_PROJ where OBJECT_ID=B.CN_SRC_TEMPLATE_PROJECT) as CN_SRC_TEMPLATE_PROJECT
,(select TDM_DESCRIPTION from TN_PRODUCTS where OBJECT_ID=B.CN_PRODUCT_TYPE ) as CN_PRODUCT_TYPE
,(select TDM_DESCRIPTION from TN_RESOURCES where OBJECT_ID=B.CN_RFQ_FROM ) as CN_RFQ_FROM
,(select TDM_DESCRIPTION from TN_RESOURCES where OBJECT_ID=B.CN_END_CUSTOMER ) as CN_END_CUSTOMER
,(select TDM_DESCRIPTION from TN_RESOURCES where OBJECT_ID=A.CN_REF_CUSTOMER) as CN_REF_CUSTOMER
,(select TDM_DESCRIPTION from TN_RESOURCES where OBJECT_ID=A.CN_REF_CLIENT) as CN_REF_CLIENT

--LOOKUP
,(select DESCRIPTION from TDM_PT_CALENDAR_TYPE where OBJECT_ID=A.TDM_CALENDAR_TYPE) as TDM_CALENDAR_TYPE
,(select DESCRIPTION from FILE_TYPE where OBJECT_ID=A.FILE_TYPE) as FILE_TYPE
,(select DESCRIPTION from TDM_CURRENCY_TYPE where OBJECT_ID=A.TDM_CURRENCY) as TDM_CURRENCY
,(select DESCRIPTION from TN_CLIENT_TYPE where OBJECT_ID=A.CN_REF_CLIENT_TYPE) as CN_REF_CLIENT_TYPE
,(select DESCRIPTION from TN_PROJECT_STATUS where OBJECT_ID=A.TDM_PROJECT_STATUS) as TDM_PROJECT_STATUS
,(select DESCRIPTION from TN_AWARD_STATUS where OBJECT_ID=B.TDM_PRJ_STATUS) as TDM_PRJ_STATUS
,(select DESCRIPTION from TN_BUSINESS_UNIT where OBJECT_ID=B.CN_BUSSINESS_UNIT ) as CN_BUSSINESS_UNIT
,(select DESCRIPTION from TN_CURRENCY_CODE where OBJECT_ID=B.CN_CURRENCY_CODE ) as CN_CURRENCY_CODE
,(select DESCRIPTION from TN_FAC_CODE where OBJECT_ID=B.CN_FAC_CODE ) as CN_FAC_CODE
,(select DESCRIPTION from TN_GREEN_PROJECT where OBJECT_ID=B.CN_GREEN_PROJECT ) as CN_GREEN_PROJECT
,(select DESCRIPTION from TN_MODIFICATION where OBJECT_ID=B.CN_MODIFY_CODE ) as CN_MODIFY_CODE
,(select DESCRIPTION from TDM_PT_NOTIF_LEVEL where OBJECT_ID=A.TDM_NOTIFICATION_LEVEL) as TDM_NOTIFICATION_LEVEL
,(select DESCRIPTION from TN_PFC where OBJECT_ID=B.CN_FPC ) as CN_FPC
,(select DESCRIPTION from TN_REASON_CODE where OBJECT_ID=B.CN_CANCEL_TYPE) as CN_CANCEL_TYPE
,(select DESCRIPTION from TN_RELIABILITY_TEST where OBJECT_ID=B.CN_RELIABILITY_TEST ) as CN_RELIABILITY_TEST
,(select DESCRIPTION from TDM_PT_SCHEDULE_FROM where OBJECT_ID=A.TDM_SCHEDULE_FROM) as TDM_SCHEDULE_FROM
,(select DESCRIPTION from TN_PROJECT_CLIENT_AREA where OBJECT_ID=B.CN_REF_CLIENT_AREA) as CN_REF_CLIENT_AREA


from TN_PROJECT A
left join TN_PROJ B on A.OBJECT_ID=B.OBJECT_ID
left join TN_PROJECT_STATUS C on  A.TDM_PROJECT_STATUS=C.OBJECT_ID

where A.CLASS_ID=459 and A.TDM_IS_TEMPLATE <> -1;

个人抽屉的主页 个人抽屉 | 初学一级 | 园豆:143
提问于:2010-10-27 10:55
< >
分享
所有回答(2)
0

好吓人……

Jaryleely | 园豆:367 (菜鸟二级) | 2010-10-27 11:21
0

贴出执行计划。

killkill | 园豆:1192 (小虾三级) | 2010-10-27 15:33
在那里看执行计划??没有注意过执行计划。
支持(0) 反对(0) 个人抽屉 | 园豆:143 (初学一级) | 2010-10-28 10:50
知道在哪看来,按F5就可以,不过执行计划太长,博客园不能排格式,贴上来就乱了,能知道您的邮件,我发给您帮忙看看。
支持(0) 反对(0) 个人抽屉 | 园豆:143 (初学一级) | 2010-10-28 11:11
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册