时间戳

首页 > 解决方案 > 如何加快查询速度

sql - 如何加快查询速度

问题描述

我正在尝试使用下面的 sql 创建一个表，它已经运行了好几天。

下面是sql。

create table I83094_Emnmt1 AS
    Select 
     'I83094'                                              AS Audit_Id
     ,rap01.plcy                                           AS plcy
     ,raa02.enddt_t                                        As enddt
     ,RPAD(NVU_GET_TERM_ID(RAP01.PLCY, Raa02.ENDDT_T),1)   AS Term_Id
     ,nvu_get_nxt_proc_enddt(RAP01.PLCY, Raa02.ENDDT_T)    As next_enddt
     ,exception_date                                       AS exception_date
     ,RAP07.MVRDT_t
     ,RAP07.MVRDT_s
     ,rap07a.prefdt_t
     ,RAP07.MRTLST
     ,RAP07.BRTHDT_T                                       AS BirthDate
     ,floor((tt.ja2_effdt_t - RAP07.BRTHDT_T)/365 )        AS Operator_Age
     ,rap07a.perseq
     ,tt.ja2_effdt_t                                       AS TERM_DATE
     ,rap01.j01_PT_LINE_cat_Cd                             AS j01_PT_LINE_cat_Cd
     ,rap01.j01_pt_cdb_part_id                             AS j01_pt_cdb_part_id
     ,Rap01.J01_Pt_State_Cd                                As J01_Pt_State_Cd
     ---
from RAP01
----
Join RAA02
  ON raa02.j46_pt_line_cat_cd  = rap01.j01_pt_line_cat_cd
AND raa02.j46_pt_cdb_part_id   = rap01.j01_pt_cdb_part_id
AND raa02.j46_pt_state_cd      = rap01.j01_pt_state_cd
AND raa02.plcy                 = rap01.plcy
AND raa02.sprodt_t  between '14-OCT-2013' AND '14-OCT-2018'
AND raa02.ahevnt               = '0993'
---
JOIN ewt_prama_term tt
  ON tt.ja2_pt_line_cat_cd     = rap01.j01_pt_line_cat_cd
AND tt.ja2_pt_cdb_part_id      = rap01.j01_pt_cdb_part_id
AND tt.ja2_pt_state_cd         = rap01.j01_pt_state_cd
AND tt.ja2_plcy                = rap01.plcy
and raa02.enddt_t              between tt.ja2_effdt_t and tt.ja2_expdt_t - 1
-----
JOIN rap07a
  ON rap07a.j36_pt_line_cat_cd = rap01.j01_pt_line_cat_cd
AND rap07a.j36_pt_cdb_part_id  = rap01.j01_pt_cdb_part_id 
AND rap07a.j36_pt_state_cd     = rap01.j01_pt_state_cd
AND rap07a.plcy                = rap01.plcy
--and RAP07a.perseq              = rap18.perseq
AND raa02.enddt_t  BETWEEN rap07a.prefdt_t AND (rap07a.dropdt_t  - 1)
----
JOIN RAP07
  ON  RAP07.J37_PT_LINE_CAT_CD =  rap01.j01_pt_line_cat_cd   
AND  RAP07.J37_PT_CDB_PART_ID  =  rap01.j01_pt_cdb_part_id  
AND  RAP07.J37_PT_STATE_CD     =  rap01.j01_pt_state_cd     
AND  rap07.plcy                =  RAP01.PLCY
AND  RAP07.perseq              =  rap07a.perseq
AND RAP07.MRTLST in ('MA','IC','DC','WC','EC','DP')
--AND RAP07.DRVDES_01 IN ('R','P')  
--AND RAP07.MVRDT_T < (select exception_date from I83094_exception)
AND floor((tt.ja2_effdt_t - RAP07.BRTHDT_T)/365) < 50
AND  raa02.enddt_t BETWEEN RAP07.enddt_t AND (RAP07.dropdt_t  - 1)
---
join I83094_exception exp
  ON exp.line_cd               = rap01.line3
AND exp.state_cd               = rap01.state
AND exp.company_cd             = rap01.co3
AND exp.marital_status_cd      = RAP07.MRTLST
---
WHERE Rap01.J01_Pt_Line_Cat_Cd = 'A'
AND Rap01.Line3               IN ('010','019')
AND RAP07.MVRDT_T < exp.exception_date;

字段 'J01_Pt_State_Cd' 代表不同的状态，这个表包含数十亿的数据，并且会花费很多时间来执行。我试图创建表，但必须等待 2 或 3 天。如何改进查询，以便我可以在更短的时间内执行查询。

标签： sqloracleperformancecreate-table

解决方案

适用于group by分组函数，如sum、max和。当您使用其中一些函数时，您通常希望按某些属性对分组求和，例如.mincountsum(sales) group by vendor

当您在没有分组函数的查询中使用此关键字时，它的工作方式类似于 a distinct，但您必须将所有字段包含在 group by 中。

您得到的错误就是，您没有将所有字段都包含在group by.

推荐阅读