表结构及sql麻烦发一下

其中之一的表索引：CREATE INDEX "index_time_name_street_date"
ON "BA_YQYP_TEST"."ads_yqyp_shgl_keyno_index_partition"
(
"time" ASC,
"index_name" ASC,
"street" ASC,
"data_date" DESC
);

建表语句：
CREATE TABLE "BA_YQYP_TEST"."ads_yqyp_shgl_keyno_index_partition"
(
"id" VARCHAR2(200),
"spe_sub_name" VARCHAR2(200),
"one_lev_catalog" VARCHAR2(200),
"two_lev_catalog" VARCHAR2(200),
"three_lev_catalog" VARCHAR2(500),
"four_lev_catalog" VARCHAR2(500),
"five_lev_catalog" VARCHAR2(500),
"busi_type" VARCHAR2(500),
"city" VARCHAR2(200),
"area" VARCHAR2(200),
"street" VARCHAR2(200),
"commnty" VARCHAR2(200),
"grid" VARCHAR2(200),
"department" VARCHAR2(500),
"time" VARCHAR2(500),
"cus_dim" VARCHAR2(1000),
"index_code" VARCHAR2(200),
"index_name" VARCHAR2(1000),
"index_value" VARCHAR2(200),
"index_unit" VARCHAR2(200),
"index_illu" VARCHAR2(1000),
"byzd1" VARCHAR2(4000),
"byzd2" VARCHAR2(4000),
"byzd3" VARCHAR2(4000),
"byzd4" VARCHAR2(4000),
"yxsj" DATE,
"data_date" DATE,
"dt" DATE NOT NULL)
PARTITION BY RANGE("dt")
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
(
PARTITION "P_2022" VALUES LESS THAN(DATE'2022-01-01') STORAGE(ON "MAIN", CLUSTERBTR)
) STORAGE(ON "MAIN", CLUSTERBTR) ;

COMMENT ON TABLE "BA_YQYP_TEST"."ads_yqyp_shgl_keyno_index_partition" IS '社会管理_指标结果';
COMMENT ON COLUMN "BA_YQYP_TEST"."ads_yqyp_shgl_keyno_index_partition"."area" IS '区';
COMMENT ON COLUMN "BA_YQYP_TEST"."ads_yqyp_shgl_keyno_index_partition"."busi_type" IS '业务类型';
COMMENT ON COLUMN "BA_YQYP_TEST"."ads_yqyp_shgl_keyno_index_partition"."byzd1" IS '备用字段1';
COMMENT ON COLUMN "BA_YQYP_TEST"."ads_yqyp_shgl_keyno_index_partition"."byzd2" IS '备用字段2';
COMMENT ON COLUMN "BA_YQYP_TEST"."ads_yqyp_shgl_keyno_index_partition"."byzd3" IS '备用字段3';
COMMENT ON COLUMN "BA_YQYP_TEST"."ads_yqyp_shgl_keyno_index_partition"."byzd4" IS '备用字段4';
COMMENT ON COLUMN "BA_YQYP_TEST"."ads_yqyp_shgl_keyno_index_partition"."city" IS '市';
COMMENT ON COLUMN "BA_YQYP_TEST"."ads_yqyp_shgl_keyno_index_partition"."commnty" IS '社区';
COMMENT ON COLUMN "BA_YQYP_TEST"."ads_yqyp_shgl_keyno_index_partition"."cus_dim" IS '自定义维度';
COMMENT ON COLUMN "BA_YQYP_TEST"."ads_yqyp_shgl_keyno_index_partition"."data_date" IS '数据时间,用于分区字段';
COMMENT ON COLUMN "BA_YQYP_TEST"."ads_yqyp_shgl_keyno_index_partition"."department" IS '部门';
COMMENT ON COLUMN "BA_YQYP_TEST"."ads_yqyp_shgl_keyno_index_partition"."dt" IS '分区字段';
COMMENT ON COLUMN "BA_YQYP_TEST"."ads_yqyp_shgl_keyno_index_partition"."five_lev_catalog" IS '五级目录';
COMMENT ON COLUMN "BA_YQYP_TEST"."ads_yqyp_shgl_keyno_index_partition"."four_lev_catalog" IS '四级目录';
COMMENT ON COLUMN "BA_YQYP_TEST"."ads_yqyp_shgl_keyno_index_partition"."grid" IS '网格';
COMMENT ON COLUMN "BA_YQYP_TEST"."ads_yqyp_shgl_keyno_index_partition"."id" IS '主键';
COMMENT ON COLUMN "BA_YQYP_TEST"."ads_yqyp_shgl_keyno_index_partition"."index_code" IS '指标编码';
COMMENT ON COLUMN "BA_YQYP_TEST"."ads_yqyp_shgl_keyno_index_partition"."index_illu" IS '指标说明';
COMMENT ON COLUMN "BA_YQYP_TEST"."ads_yqyp_shgl_keyno_index_partition"."index_name" IS '指标名';
COMMENT ON COLUMN "BA_YQYP_TEST"."ads_yqyp_shgl_keyno_index_partition"."index_unit" IS '指标单位';
COMMENT ON COLUMN "BA_YQYP_TEST"."ads_yqyp_shgl_keyno_index_partition"."index_value" IS '指标值';
COMMENT ON COLUMN "BA_YQYP_TEST"."ads_yqyp_shgl_keyno_index_partition"."one_lev_catalog" IS '一级目录';
COMMENT ON COLUMN "BA_YQYP_TEST"."ads_yqyp_shgl_keyno_index_partition"."spe_sub_name" IS '专题';
COMMENT ON COLUMN "BA_YQYP_TEST"."ads_yqyp_shgl_keyno_index_partition"."street" IS '街道';
COMMENT ON COLUMN "BA_YQYP_TEST"."ads_yqyp_shgl_keyno_index_partition"."three_lev_catalog" IS '三级目录';
COMMENT ON COLUMN "BA_YQYP_TEST"."ads_yqyp_shgl_keyno_index_partition"."time" IS '时间';
COMMENT ON COLUMN "BA_YQYP_TEST"."ads_yqyp_shgl_keyno_index_partition"."two_lev_catalog" IS '二级目录';
COMMENT ON COLUMN "BA_YQYP_TEST"."ads_yqyp_shgl_keyno_index_partition"."yxsj" IS '运行时间';

CREATE INDEX "index_time_name_street_date" ON "BA_YQYP_TEST"."ads_yqyp_shgl_keyno_index_partition"("time" ASC,"index_name" ASC,"street" ASC,"data_date" DESC) STORAGE(ON "MAIN", CLUSTERBTR) ;

查询SQL语句：SELECT
/+PARALLEL(4)/
IFNULL(MAX(CASE WHEN "SKI"."index_name" = '调解案件总数' AND "SKI"."four_lev_catalog" = '-' THEN "SUM"(index_value) END), 0) AS caseTotal,
IFNULL(MAX(CASE WHEN "SKI"."index_name" = '受理纠纷总数' AND "SKI"."four_lev_catalog" = '-' THEN "SUM"(index_value) END), 0) AS acceptDis,
IFNULL(MAX(CASE WHEN "SKI"."index_name" = '调解成功总数' AND "SKI"."four_lev_catalog" = '-' THEN "SUM"(index_value) END), 0) AS sucMedTotal,
IFNULL(MAX(CASE WHEN "SKI"."index_name" = '挽回经济损失' AND "SKI"."four_lev_catalog" = '-' THEN (CASE WHEN ("SUM"("SKI"."index_value") / 10000) * 1.0% 1 == 0 THEN "SUM"("SKI"."index_value") ELSE IFNULL(RTRIM(CONVERT(DECIMAL(10,2), ("SUM"("SKI"."index_value") / 10000) )), '0') END ) END), 0) AS reviveEco
FROM "ads_yqyp_shgl_keyno_index_partition"

--partition(SYS_P2689_3520)

SKI
WHERE 1=1 AND "SKI"."spe_sub_name" = '社会管理' AND "SKI"."one_lev_catalog" = 'i调解' AND "SKI"."two_lev_catalog" = '首页' AND "SKI"."three_lev_catalog" = '区调解案件' AND "SKI"."four_lev_catalog" = '-' AND "SKI"."data_date" = ( SELECT MAX("SKI"."data_date")
FROM "ads_yqyp_shgl_keyno_index" SKI
WHERE 1=1 AND TIME=(SELECT SUBSTR(TO_CHAR(to_date(MAX(data_date)) -1),1,7)
FROM "ads_yqyp_shgl_keyno_index_partition" partition(SYS_P2689_3520) ) ) AND TIME=(SELECT SUBSTR(TO_CHAR(to_date(MAX(data_date)) -1),1,7)
FROM "ads_yqyp_shgl_keyno_index_partition" partition(SYS_P2689_3520) ) AND "SKI"."cus_dim" = '本月'
GROUP BY "SKI"."index_name",
"SKI"."four_lev_catalog"