首页 新闻 搜索 专区 学院

Oracle查询优化-怎样建立索引优化下面的查询语句啊

0
悬赏园豆:50 [已解决问题] 解决于 2021-02-24 15:16

下面是转换出来的查询语句
SELECT *
FROM (SELECT "Project1"."C1" AS "C1",
"Project1"."ID" AS "ID",
"Project1"."NVC_ORDERBY" AS "NVC_ORDERBY",
"Project1"."I_STATE" AS "I_STATE",
"Project1"."I_KPSTATE" AS "I_KPSTATE",
"Project1"."VC_FPNO" AS "VC_FPNO",
"Project1"."NVC_TAX" AS "NVC_TAX",
"Project1"."N_TAXRATE" AS "N_TAXRATE",
"Project1"."NVC_CUSTOMER" AS "NVC_CUSTOMER",
"Project1"."N_OPENMONEY" AS "N_OPENMONEY",
"Project1"."NVC_NAME" AS "NVC_NAME",
"Project1"."I_ID_APPLY" AS "I_ID_APPLY",
"Project1"."DT_APPLY" AS "DT_APPLY",
"Project1"."NVC_REMARK" AS "NVC_REMARK",
"Project1"."I_ID_UNIT" AS "I_ID_UNIT",
"Project1"."I_ID_BUNIT" AS "I_ID_BUNIT",
"Project1"."I_ID_USER" AS "I_ID_USER",
"Project1"."DT_WRITE" AS "DT_WRITE",
"Project1"."I_ID_TAX" AS "I_ID_TAX",
"Project1"."I_ID_MONEYTYPE" AS "I_ID_MONEYTYPE"
FROM (SELECT "Project1"."ID" AS "ID",
"Project1"."I_STATE" AS "I_STATE",
"Project1"."I_ID_TAX" AS "I_ID_TAX",
"Project1"."I_ID_MONEYTYPE" AS "I_ID_MONEYTYPE",
"Project1"."N_OPENMONEY" AS "N_OPENMONEY",
"Project1"."NVC_REMARK" AS "NVC_REMARK",
"Project1"."I_ID_APPLY" AS "I_ID_APPLY",
"Project1"."DT_APPLY" AS "DT_APPLY",
"Project1"."I_ID_BUNIT" AS "I_ID_BUNIT",
"Project1"."I_ID_UNIT" AS "I_ID_UNIT",
"Project1"."I_ID_USER" AS "I_ID_USER",
"Project1"."DT_WRITE" AS "DT_WRITE",
"Project1"."N_TAXRATE" AS "N_TAXRATE",
"Project1"."NVC_CUSTOMER" AS "NVC_CUSTOMER",
"Project1"."I_KPSTATE" AS "I_KPSTATE",
"Project1"."VC_FPNO" AS "VC_FPNO",
"Project1"."NVC_ORDERBY" AS "NVC_ORDERBY",
"Project1"."NVC_TAX" AS "NVC_TAX",
"Project1"."C1" AS "C1",
"Project1"."NVC_NAME" AS "NVC_NAME",
ROW_NUMBER() OVER(ORDER BY "Project1"."I_STATE" ASC, "Project1"."I_KPSTATE" ASC, "Project1"."ID" DESC) AS "row_number"
FROM (SELECT "Filter1"."ID1" AS "ID",
"Filter1"."I_STATE1" AS "I_STATE",
"Filter1"."I_ID_TAX" AS "I_ID_TAX",
"Filter1"."I_ID_MONEYTYPE" AS "I_ID_MONEYTYPE",
"Filter1"."N_OPENMONEY" AS "N_OPENMONEY",
"Filter1"."NVC_REMARK" AS "NVC_REMARK",
"Filter1"."I_ID_APPLY" AS "I_ID_APPLY",
"Filter1"."DT_APPLY" AS "DT_APPLY",
"Filter1"."I_ID_BUNIT1" AS "I_ID_BUNIT",
"Filter1"."I_ID_UNIT1" AS "I_ID_UNIT",
"Filter1"."I_ID_USER1" AS "I_ID_USER",
"Filter1"."DT_WRITE1" AS "DT_WRITE",
"Filter1"."N_TAXRATE" AS "N_TAXRATE",
"Filter1"."NVC_CUSTOMER" AS "NVC_CUSTOMER",
"Filter1"."I_KPSTATE" AS "I_KPSTATE",
"Filter1"."VC_FPNO" AS "VC_FPNO",
"Filter1"."NVC_ORDERBY" AS "NVC_ORDERBY",
"Filter1"."NVC_TAX" AS "NVC_TAX",
1 AS "C1",
"Extent3"."NVC_NAME" AS "NVC_NAME"
FROM (SELECT "Extent1"."ID" AS "ID1",
"Extent1"."I_STATE" AS "I_STATE1",
"Extent1"."I_ID_TAX" AS "I_ID_TAX",
"Extent1"."I_ID_MONEYTYPE" AS "I_ID_MONEYTYPE",
"Extent1"."N_OPENMONEY" AS "N_OPENMONEY",
"Extent1"."NVC_REMARK" AS "NVC_REMARK",
"Extent1"."I_ID_APPLY" AS "I_ID_APPLY",
"Extent1"."DT_APPLY" AS "DT_APPLY",
"Extent1"."I_ID_BUNIT" AS "I_ID_BUNIT1",
"Extent1"."I_ID_UNIT" AS "I_ID_UNIT1",
"Extent1"."I_ID_USER" AS "I_ID_USER1",
"Extent1"."DT_WRITE" AS "DT_WRITE1",
"Extent1"."I_FILECOUNT" AS "I_FILECOUNT",
"Extent1"."N_TAXRATE" AS "N_TAXRATE",
"Extent1"."NVC_CUSTOMER" AS "NVC_CUSTOMER",
"Extent1"."I_KPSTATE" AS "I_KPSTATE",
"Extent1"."VC_FPNO" AS "VC_FPNO",
"Extent1"."NVC_ORDERBY" AS "NVC_ORDERBY",
"Extent2"."ID" AS "ID2",
"Extent2"."VC_NO" AS "VC_NO",
"Extent2"."I_TYPE" AS "I_TYPE",
"Extent2"."NVC_TAX" AS "NVC_TAX",
"Extent2"."I_ID_BUNIT" AS "I_ID_BUNIT2",
"Extent2"."I_STATE" AS "I_STATE2",
"Extent2"."I_ID_UNIT" AS "I_ID_UNIT2",
"Extent2"."I_ID_USER" AS "I_ID_USER2",
"Extent2"."DT_WRITE" AS "DT_WRITE2"
FROM "XYZLS"."T_CW_FPOPENAPPLY" "Extent1"
INNER JOIN "XYZLS"."T_CW_FPTAX" "Extent2"
ON "Extent1"."I_ID_TAX" = "Extent2"."ID"
WHERE ((1 = "Extent1"."I_ID_UNIT") OR
(6182 = "Extent1"."I_ID_UNIT") OR
(6422 = "Extent1"."I_ID_UNIT") OR
(6502 = "Extent1"."I_ID_UNIT") OR
(6541 = "Extent1"."I_ID_UNIT") OR
(6183 = "Extent1"."I_ID_UNIT") OR
(6185 = "Extent1"."I_ID_UNIT") OR
(6201 = "Extent1"."I_ID_UNIT") OR
(6801 = "Extent1"."I_ID_UNIT") OR
(7101 = "Extent1"."I_ID_UNIT") OR
(6221 = "Extent1"."I_ID_UNIT") OR
(6241 = "Extent1"."I_ID_UNIT") OR
(6361 = "Extent1"."I_ID_UNIT") OR
(6261 = "Extent1"."I_ID_UNIT") OR
(6281 = "Extent1"."I_ID_UNIT") OR
(6721 = "Extent1"."I_ID_UNIT") OR
(6401 = "Extent1"."I_ID_UNIT") OR
(6641 = "Extent1"."I_ID_UNIT") OR
(6681 = "Extent1"."I_ID_UNIT") OR
(6481 = "Extent1"."I_ID_UNIT") OR
(6902 = "Extent1"."I_ID_UNIT") OR
(6482 = "Extent1"."I_ID_UNIT") OR
(6901 = "Extent1"."I_ID_UNIT") OR
(7041 = "Extent1"."I_ID_UNIT") OR
(6561 = "Extent1"."I_ID_UNIT") OR
(6581 = "Extent1"."I_ID_UNIT") OR
(6582 = "Extent1"."I_ID_UNIT") OR
(6583 = "Extent1"."I_ID_UNIT") OR
(6584 = "Extent1"."I_ID_UNIT") OR
(6585 = "Extent1"."I_ID_UNIT") OR
(6881 = "Extent1"."I_ID_UNIT") OR
(7001 = "Extent1"."I_ID_UNIT") OR
(6661 = "Extent1"."I_ID_UNIT") OR
(7061 = "Extent1"."I_ID_UNIT") OR
(6162 = "Extent1"."I_ID_UNIT") OR
(6821 = "Extent1"."I_ID_UNIT") OR
(6921 = "Extent1"."I_ID_UNIT") OR
(6961 = "Extent1"."I_ID_UNIT") OR
(7062 = "Extent1"."I_ID_UNIT") OR
(7081 = "Extent1"."I_ID_UNIT") OR
(7082 = "Extent1"."I_ID_UNIT"))) "Filter1"
LEFT OUTER JOIN "XYZLS"."T_BASE_MONEYTYPE" "Extent3"
ON "Filter1"."I_ID_MONEYTYPE" = "Extent3"."ID"
WHERE (("Filter1"."DT_APPLY" >= TO_DATE('2019-01-01', 'yyyy-mm-dd')) AND
("Filter1"."DT_APPLY" <= TO_DATE('2019-12-30', 'yyyy-mm-dd')))) "Project1") "Project1"
WHERE ("Project1"."row_number" > 0)
ORDER BY "Project1"."I_STATE" ASC,
"Project1"."I_KPSTATE" ASC,
"Project1"."ID" DESC)
WHERE (ROWNUM <= (35))

其中最多的T_CW_FPOPENAPPLY表有50万数据 其他表数据都不多
ID为主键


耗时一秒多,我觉得太慢了
后来我把orderby里面的前两个条件去掉,去掉之后

发现,哇 太快了吧,要的就是这种效果

请问,如果我不去掉这两个排序字段,如何建立索引优化查询速度

心判世界的主页 心判世界 | 初学一级 | 园豆:83
提问于:2019-06-20 18:22
< >
分享
最佳答案
0

建一个聚集索引试试,让索引中字段的顺序和orderby中的一致,orderby的字段也要是非空的

收获园豆:50
小光 | 小虾三级 |园豆:1802 | 2019-06-21 11:14

我试着建了个 CREATE INDEX IDX_1 ON T_CW_FPOPENAPPLY(I_STATE ASC,I_KPSTATE ASC,ID DESC); 没有效果

心判世界 | 园豆:83 (初学一级) | 2019-06-21 15:10

而且这三个都是非空字段

心判世界 | 园豆:83 (初学一级) | 2019-06-21 15:12

@心判世界: 应该是某种原因导致索引失效了,电脑没装Oracle,你试着参考这个优化一下:
https://blog.csdn.net/lanqibaoer/article/details/51509047

小光 | 园豆:1802 (小虾三级) | 2019-06-24 09:36
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册