Oracle查询优化-怎样建立索引优化下面的查询语句啊
下面是转换出来的查询语句
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里面的前两个条件去掉，去掉之后
发现，哇 太快了吧，要的就是这种效果
请问，如果我不去掉这两个排序字段，如何建立索引优化查询速度
回答：
建一个聚集索引试试，让索引中字段的顺序和orderby中的一致，orderby的字段也要是非空的
回答：
我试着建了个 CREATE INDEX IDX_1 ON T_CW_FPOPENAPPLY(I_STATE ASC，I_KPSTATE ASC，ID DESC); 没有效果
回答：
而且这三个都是非空字段
回答：
@心判世界: 应该是某种原因导致索引失效了，电脑没装Oracle，你试着参考这个优化一下：
https://blog.csdn.net/lanqibaoer/article/details/51509047
以上是 Oracle查询优化-怎样建立索引优化下面的查询语句啊 的全部内容， 来源链接： utcz.com/a/120568.html
