NC Cloud适配达梦8数据库的优化调整

1、增加部分索引，并更新统计信息

索引优化语句
create index "IDX_MDORMAP_COLUMND" on "NCC_2005_DM8"."MD_ORMAP"("COLUMND");
create index "IDX_MDORMAP_CLASSID" on "NCC_2005_DM8"."MD_ORMAP"("CLASSID");
create index "IDX_MDPROPERTY_NAME" on "NCC_2005_DM8"."MD_PROPERTY"("NAME");
CREATE INDEX "INDEX_ASSTION_STARTBEANID" ON "NCC_2005_DM8"."MD_ASSOCIATION"("STARTBEANID" ASC);
CREATE INDEX "INDEX_MDRELATION_STARTTABLEID" ON "NCC_2005_DM8"."MD_DB_RELATION"("STARTTABLEID" ASC);
CREATE INDEX "INDEX_MDRELATION_TABLEID_ISFORNKEY" ON "NCC_2005_DM8"."MD_DB_RELATION"("ENDTABLEID" ASC,"ISFOREIGNKEY" ASC);
CREATE INDEX "IDX_PUB_AREA_METAID" ON "NCC_2005_DM8"."PUB_AREA"("METAID" ASC) STORAGE(ON "MAIN", CLUSTERBTR) ;
CREATE INDEX "I_PUB_AREA_METANAME" ON "NCC_2005_DM8"."PUB_AREA"("METANAME" ASC) STORAGE(ON "MAIN", CLUSTERBTR) ;

统计信息更新语句
STAT 100 ON "NCC_2005_DM8"."MD_ORMAP"("COLUMNID");
STAT 100 ON "NCC_2005_DM8"."MD_ORMAP"("CLASSID");
STAT 100 ON "NCC_2005_DM8"."MD_PROPERTY"("NAME");
STAT 100 ON "NCC_2005_DM8"."MD_ASSOCIATION"("STARTBEANID");
STAT 100 ON "NCC_2005_DM8"."MD_DB_RELATION"("STARTTABLEID");
STAT 100 ON "NCC_2005_DM8"."MD_DB_RELATION"("ENDTABLEID","ISFOREIGNKEY");
STAT 100 ON "NCC_2005_DM8"."PUB_AREA"("METAID");
STAT 100 ON "NCC_2005_DM8"."PUB_AREA"("METANAME")

2、慢sql：select pk_menuitem, menuitemcode, menuitemname, parentcode, appid, appcode, pk_menu, resid from sm_appmenuitem where sm_appmenuitem.dr = 0 and dr = 0 and ( nvl ( pk_group, '~' ) = '~' or pk_group = '0001A110000000000DTC' ) and pk_menu = ( select pk_menu from sm_appmenureg where dr = 0 and isenable = 'Y' ) and nvl ( appid, '~' ) != '~' and appid in ( select pk_appregister from sm_appregister where dr = 0 and isenable = 'Y' and apptype in ( 1, 2 ) and funtype in ( 0, 3 ) and ( nvl ( pk_group, '~' ) = '~' or pk_group = '0001A110000000000DTC' ) and appid not in ( select pk from temp_pam_pk ) and own_module in ( select funccode from sm_createcorp where pk_org = '0001A110000000000DTC' ) ) and appcode not in ( '10160501', '10160502', '10160503', '10160504', '10160511', '10160512', '10160513', '10160514', '10160515', '10160516', '10160517', '10160518', '10160519', '10164501' ) order by menuitemcode asc        症状：执行耗时超过2分钟

3、select * from ( select row_number () over ( partition by id order by versiontype desc ) rn, t1.id, t1.fromSourceBmf from md_component t1 where id in ( select componentID from md_class where fullclassname = 'nc.vo.fbm.register.PaymentStatusEnum' ) and ( industry = '0' or industry = '0' ) ) a where a.rn = 1

原：300毫秒  优化后 7毫秒

CREATE UNIQUE INDEX "IDX_ID_INDU_VERSION" ON 你的用户名."MD_COMPONENT"("ID" ASC,"INDUSTRY" ASC,"VERSIONTYPE" ASC) STORAGE(ON "nnc_index01", CLUSTERBTR) ;

4、

select g.pk_app_group, a.pk_menuitem cardid, app.pk_appregister, app.code, app.name, app.own_module, app.mountid, app.width, app.height, app.target_path, app.apptype, app.image_src, app.uselicense_load, app.orgtypecode, app.funtype, app.parent_id, app.isenable, app.pk_group, app.app_desc, app.help_name, app.iscauserusable, app.mdid, app.source_app_code, app.fun_property, app.iscopypage, app.creator, app.creationtime, app.modifier, app.modifiedtime, app.dr, app.ts, a.menuitemcode, a.menuitemname name, a.menudes, a.appcode, a.parentcode, a.pk_menu, a.resid, a.appid, ga.gridx, ga.gridy from sm_app_group g, sm_group_app ga, sm_appmenuitem a, sm_appregister app where ga.dr = 0 and g.dr = 0 and a.dr = 0 and g.pk_app_group = ga.groupid and ga.menuitemid = a.pk_menuitem and app.pk_appregister = a.appid and app.ISENABLE = 'Y' and g.pk_app_group in ( '0001A11000000000738Z', '0001A110000000007399', '0001A11000000000739K', '0001A110000000007391', '0001A11000000000739P', '0001A110000000007398', '0001A11000000000738X', '0001A110000000007392', '0001A11000000000738R', '0001A11000000000739N', '0001A110000000007395',

优化前：3-4秒 

select g.pk_app_group, a.pk_menuitem cardid, app.pk_appregister, app.code, app.name, app.own_module, app.mountid, app.width, app.height, app.target_path, app.apptype, app.image_src, app.uselicense_load, app.orgtypecode, app.funtype, app.parent_id, app.isenable, app.pk_group, app.app_desc, app.help_name, app.iscauserusable, app.mdid, app.source_app_code, app.fun_property, app.iscopypage, app.creator, app.creationtime, app.modifier, app.modifiedtime, app.dr, app.ts, a.menuitemcode, a.menuitemname name, a.menudes, a.appcode, a.parentcode, a.pk_menu, a.resid, a.appid, ga.gridx, ga.gridy from sm_app_group g, sm_group_app ga, sm_appmenuitem a, sm_appregister app where ga.dr = 0 and g.dr = 0 and a.dr = 0 and g.pk_app_group = ga.groupid and ga.menuitemid = a.pk_menuitem and app.pk_appregister = a.appid and app.ISENABLE = 'Y' and g.pk_app_group in ( '0001A110000000007394', '0001A11000000000739S', '0001A11000000000739N', '0001A11000000000739K', '0001A110000000007392', '0001A11000000000739F', '0001A110000000007396', '0001A11000000000738X', '0001A11000000000739R', '0001A11000000000739L', '0001A11000000000739I', '0001A11000000000739C', '0001A11000000000738Q', '0001A11000000000739G', '0001A11000000000739Q', '0001A11000000000739P', '0001A110000000007399', '0001A11000000000739W', '0001A11000000000739U', '0001A11000000000739E', '0001A110000000007397', '0001A110000000007395', '0001A11000000000738R', '0001A11000000000739J', '0001A11000000000738S', '0001A11000000000738V', '0001A11000000000738W', '0001A11000000000738U', '0001A11000000000739M', '0001A110000000007391', '0001A11000000000739B', '0001A11000000000739V', '0001A11000000000738T', '0001A110000000007390', '0001A11000000000738Y', '0001A11000000000739Y', '0001A11000000000739A', '0001A11000000000739X', '0001A11000000000738Z', '0001A11000000000739D', '0001A110000000007398', '0001A1100000000073AJ', '0001A1100000000073AI', '0001A1100000000073AH', '0001A1100000000073AF', '0001A1100000000073AE', '0001A1100000000073AD', '0001A1100000000073AB', '0001A1100000000073AA', '0001A1100000000073A9', '0001A1100000000073A8', '0001A1100000000073A7', '0001A1100000000073A6', '0001A1100000000073AC', '0001A1100000000073A5', '0001A1100000000073A4', '0001A1100000000073A3', '0001A1100000000073AG', '0001A1100000000073A2', '0001A1100000000073A1', '0001A11000000000739Z', '0001A1100000000073AK', '0001A1100000000073A0', '0001A1100000000073AO', '0001A1100000000073AM', '0001A1100000000073AL', '0001A1100000000073B8', '0001A1100000000073B7', '0001A1100000000073B6', '0001A1100000000073B5', '0001A1100000000073B4', '0001A1100000000073B3', '0001A1100000000073B2', '0001A1100000000073B1', '0001A1100000000073B0', '0001A1100000000073AZ', '0001A1100000000073AY', '0001A1100000000073AX', '0001A1100000000073AW', '0001A1100000000073AV', '0001A1100000000073AU', '0001A1100000000073AT', '0001A1100000000073AS', '0001A1100000000073AR', '0001A1100000000073AQ', '0001A1100000000073B9', '0001A1100000000073AP', '0001A1100000000073BK', '0001A1100000000073BJ', '0001A1100000000073BI', '0001A1100000000073BH', '0001A1100000000073BG', '0001A1100000000073BF', '0001A1100000000073BE', '0001A1100000000073BD', '0001A1100000000073BC', '0001A1100000000073BB', '0001A1100000000073BA', '0001A1100000000073BP', '0001A1100000000073BO', '0001A1100000000073BN', '0001A1100000000073BM', '0001A1100000000073BL', '0001A1100000000073BQ', '0001A1100000000073BR' )

----用户职责已分配应用
select pk_menuitem, menuitemcode, menuitemname, parentcode, appid, appcode, pk_menu, resid from sm_appmenuitem where sm_appmenuitem.dr = 0 and dr = 0 and ( nvl ( pk_group, '~' ) = '~' or pk_group = '0001A1100000000033W7' ) and pk_menu = ( select pk_menu from sm_appmenureg where dr = 0 and isenable = 'Y' ) and nvl ( appid, '~' ) != '~' and appid in ( select pk_appregister from sm_appregister where dr = 0 and isenable = 'Y' and apptype in ( 1, 2 ) and funtype in ( 0, 3 ) and ( nvl ( pk_group, '~' ) = '~' or pk_group = '0001A1100000000033W7' ) and appid not in ( select pk from temp_pam_pk1 ) and own_module in ( select funccode from sm_createcorp where pk_org = '0001A1100000000033W7' ) ) and appcode not in ( '10160501', '10160502', '10160503', '10160504', '10160511', '10160512', '10160513', '10160514', '10160515', '10160516', '10160517', '10160518', '10160519', '10164501', '10164503' ) order by menuitemcode asc