sql查询需要很多时间

发布于 2024-11-19 13:21:59 字数 2436 浏览 2 评论 0原文

我正在运行以下查询:

SELECT * 
  FROM dblappc.credit_history 
 WHERE crd_effective_date > TO_DATE('20100801','YYYYMMDD') 
   AND o_crd_score > 650 
   AND crd_expiration_date IS NULL
  • 上表没有索引
  • crd_expiration_date 是主键的一部分

如何加快查询速度?
我可以在这里使用并行提示,以便一次至少给出 500 行吗?

下面是表结构:

CREATE TABLE DFQAPP13.CREDIT_HISTORY
(
  BAN                  NUMBER(9) CONSTRAINT CRHST_BAN_NN NOT NULL,
  CRD_SEQ_NO           NUMBER(9) CONSTRAINT CRDHST_CRD_SEQ_NO_NN NOT NULL,
  SYS_CREATION_DATE    DATE                     NOT NULL,
  SYS_UPDATE_DATE      DATE,
  OPERATOR_ID          NUMBER(9),
  APPLICATION_ID       CHAR(6 BYTE),
  DL_SERVICE_CODE      CHAR(5 BYTE),
  DL_UPDATE_STAMP      NUMBER(4),
  CRD_EFFECTIVE_DATE   DATE CONSTRAINT CRDHST_CRD_EFFECTIVE_DATE_NN NOT NULL,
  CRD_EXPIRATION_DATE  DATE,
  CRD_VET_TYPE         CHAR(4 BYTE),
  O_CRD_APPLIC_NUM     NUMBER(9),
  O_CRD_DECISION       CHAR(2 BYTE),
  O_CRD_SCORE          NUMBER(7),
  O_CRD_POLICY_RULE1   VARCHAR2(40 BYTE),
  O_CRD_POLICY_RULE2   VARCHAR2(40 BYTE),
  O_CRD_POLICY_RULE3   VARCHAR2(40 BYTE),
  O_CRD_POLICY_RULE4   VARCHAR2(40 BYTE),
  O_CRD_POLICY_RULE5   VARCHAR2(40 BYTE),
  O_CRD_POLICY_RULE6   VARCHAR2(40 BYTE),
  CRD_CLASS            CHAR(1 BYTE),
  CRD_CLASS_CHG_TYPE   CHAR(1 BYTE),
  CRD_CHG_RSN_TEXT     CHAR(100 BYTE),
  I_CRD_REQ_CTN_QTY    NUMBER(7),
  CRD_APR_CTN_QTY      NUMBER(7),
  I_CRD_BANK_BRANCH    VARCHAR2(100 BYTE),
  I_CRD_TACT_BANK_CD   CHAR(1 BYTE),
  I_CRD_BANK_DATE      DATE,
  I_ESAT_CUST_IND      CHAR(1 BYTE),
  O_DUNS_RET_CODE1     CHAR(4 BYTE),
  O_DUNS_RET_CODE2     CHAR(4 BYTE),
  O_DUNS_RET_NUM       VARCHAR2(18 BYTE),
  O_DUNS_NUM           NUMBER(9),
  O_DUNS_FIN_STRENGTH  CHAR(3 BYTE),
  O_DUNS_COMP_COND     CHAR(1 BYTE),
  O_DUNS_PAYM_SCORE    NUMBER(4),
  O_DUNS_CCJ1_EIRE     NUMBER(6),
  O_DUNS_CCJ2_EIRE     NUMBER(6),
  O_DUNS_CCJ3_EIRE     NUMBER(6),
  O_DUNS_CCJ4_EIRE     NUMBER(6),
  O_DUNS_CCJ5_EIRE     NUMBER(6),
  O_DUNS_CCJ1_UK       NUMBER(4),
  O_DUNS_CCJ2_UK       NUMBER(4),
  O_DUNS_CCJ3_UK       NUMBER(4),
  O_DUNS_CCJ4_UK       NUMBER(4),
  O_DUNS_CCJ5_UK       NUMBER(4),
  I_PHONE_TYPE         CHAR(3 BYTE),
  I_PAID_ENHANCE_NUM   NUMBER(1),
  I_CHURN_CUST_IND     CHAR(1 BYTE),
  I_EX_DIRECTORY_IND   CHAR(1 BYTE),
  I_ITEMISED_BIL_IND   CHAR(1 BYTE),
  CONV_RUN_NO          NUMBER(3)
)

I am running the following query:

SELECT * 
  FROM dblappc.credit_history 
 WHERE crd_effective_date > TO_DATE('20100801','YYYYMMDD') 
   AND o_crd_score > 650 
   AND crd_expiration_date IS NULL
  • Above table has no indexes
  • crd_expiration_date is part of the primary key

How can I speed up the query?
Could I use parallel hint here so that to give 500 rows at a time at least?

Below is the table structure:

CREATE TABLE DFQAPP13.CREDIT_HISTORY
(
  BAN                  NUMBER(9) CONSTRAINT CRHST_BAN_NN NOT NULL,
  CRD_SEQ_NO           NUMBER(9) CONSTRAINT CRDHST_CRD_SEQ_NO_NN NOT NULL,
  SYS_CREATION_DATE    DATE                     NOT NULL,
  SYS_UPDATE_DATE      DATE,
  OPERATOR_ID          NUMBER(9),
  APPLICATION_ID       CHAR(6 BYTE),
  DL_SERVICE_CODE      CHAR(5 BYTE),
  DL_UPDATE_STAMP      NUMBER(4),
  CRD_EFFECTIVE_DATE   DATE CONSTRAINT CRDHST_CRD_EFFECTIVE_DATE_NN NOT NULL,
  CRD_EXPIRATION_DATE  DATE,
  CRD_VET_TYPE         CHAR(4 BYTE),
  O_CRD_APPLIC_NUM     NUMBER(9),
  O_CRD_DECISION       CHAR(2 BYTE),
  O_CRD_SCORE          NUMBER(7),
  O_CRD_POLICY_RULE1   VARCHAR2(40 BYTE),
  O_CRD_POLICY_RULE2   VARCHAR2(40 BYTE),
  O_CRD_POLICY_RULE3   VARCHAR2(40 BYTE),
  O_CRD_POLICY_RULE4   VARCHAR2(40 BYTE),
  O_CRD_POLICY_RULE5   VARCHAR2(40 BYTE),
  O_CRD_POLICY_RULE6   VARCHAR2(40 BYTE),
  CRD_CLASS            CHAR(1 BYTE),
  CRD_CLASS_CHG_TYPE   CHAR(1 BYTE),
  CRD_CHG_RSN_TEXT     CHAR(100 BYTE),
  I_CRD_REQ_CTN_QTY    NUMBER(7),
  CRD_APR_CTN_QTY      NUMBER(7),
  I_CRD_BANK_BRANCH    VARCHAR2(100 BYTE),
  I_CRD_TACT_BANK_CD   CHAR(1 BYTE),
  I_CRD_BANK_DATE      DATE,
  I_ESAT_CUST_IND      CHAR(1 BYTE),
  O_DUNS_RET_CODE1     CHAR(4 BYTE),
  O_DUNS_RET_CODE2     CHAR(4 BYTE),
  O_DUNS_RET_NUM       VARCHAR2(18 BYTE),
  O_DUNS_NUM           NUMBER(9),
  O_DUNS_FIN_STRENGTH  CHAR(3 BYTE),
  O_DUNS_COMP_COND     CHAR(1 BYTE),
  O_DUNS_PAYM_SCORE    NUMBER(4),
  O_DUNS_CCJ1_EIRE     NUMBER(6),
  O_DUNS_CCJ2_EIRE     NUMBER(6),
  O_DUNS_CCJ3_EIRE     NUMBER(6),
  O_DUNS_CCJ4_EIRE     NUMBER(6),
  O_DUNS_CCJ5_EIRE     NUMBER(6),
  O_DUNS_CCJ1_UK       NUMBER(4),
  O_DUNS_CCJ2_UK       NUMBER(4),
  O_DUNS_CCJ3_UK       NUMBER(4),
  O_DUNS_CCJ4_UK       NUMBER(4),
  O_DUNS_CCJ5_UK       NUMBER(4),
  I_PHONE_TYPE         CHAR(3 BYTE),
  I_PAID_ENHANCE_NUM   NUMBER(1),
  I_CHURN_CUST_IND     CHAR(1 BYTE),
  I_EX_DIRECTORY_IND   CHAR(1 BYTE),
  I_ITEMISED_BIL_IND   CHAR(1 BYTE),
  CONV_RUN_NO          NUMBER(3)
)

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(3

没有伤那来痛 2024-11-26 13:22:00

在不知道表的大小、现有索引、执行计划等细节的情况下,很难给出任何建议。

由于查询是在两个范围内搜索,因此仅使用索引获取所有结果并不容易。

但我会首先尝试以下两个选项:

  • crd_efficient_date 进行简单索引,对 (crd_expiration_date, o_crd_score) 进行复合 或

  • 简单索引o_crd_score 和复合 (crd_expiration_date, crd_ effective_date)

您可以使用以下命令检查查询的执行计划(现在没有索引以及添加索引后):解释计划

Without knowing the size of the table, the existing indexes, the execution plan and other details, it's hard to give any advice.

Since the query is searching in two ranges, it's not easy to get all results using only indexes.

But I would try first these 2 options:

  • Simple index on crd_effective_date and compound on (crd_expiration_date, o_crd_score) or

  • Simple index on o_crd_score and compound on (crd_expiration_date, crd_effective_date)

You can check the execution plan of a query (now, without indexes and after adding them), using: EXPLAIN PLAN

写下不归期 2024-11-26 13:22:00

有一个具体问题和一些一般性改进。

首先,null 值未建立索引,因此 Oracle 最有可能选择对查询进行完整索引扫描或索引跳过扫描变体,并在主键之上构建索引。
如果您确实需要将 crd_expiration_date 包含到主键中,请仅在 o_crd_scorecrd_ effective_date 字段上为此查询创建另一个索引。

create index X_CREDIT_HISTORY_DATE_SCORE 
  on CREDIT_HISTORY (o_crd_score, crd_effective_date)

如果之后 Oracle 不会在查询文本中强制使用新索引:

select /*+ index(hist_data X_CREDIT_HISTORY_DATE_SCORE) */ 
  * 
from 
  dblappc.credit_history hist_data
where 
  crd_effective_date>to_date('20100801','YYYYMMDD') 
  and 
  o_crd_score >650 
  and 
  crd_expiration_date is null

一般问题很常见,并且在之前的答案中提到过:

  1. 仅选择您真正需要的特定字段;
  2. 避免在主键中使用空值;
  3. 首选代理主键。

更新

哦哦...
我之前在阅读问题时错过了“上面的表没有索引”这句话。

因此只有一个建议:只创建一个索引。

There are one specific issue and some general improvements.

Firstly, null values not indexed, therefore Oracle most probably choose to full index scan or index skip scan variants for your query with index build above primary key.
If you really need to include crd_expiration_date into primary key, create another index for this query on o_crd_score and crd_effective_date fields only.

create index X_CREDIT_HISTORY_DATE_SCORE 
  on CREDIT_HISTORY (o_crd_score, crd_effective_date)

If after that Oracle won't use new index force it in query text:

select /*+ index(hist_data X_CREDIT_HISTORY_DATE_SCORE) */ 
  * 
from 
  dblappc.credit_history hist_data
where 
  crd_effective_date>to_date('20100801','YYYYMMDD') 
  and 
  o_crd_score >650 
  and 
  crd_expiration_date is null

General issues are common and mentioned in previous answers:

  1. Select only specific fields, which you really need;
  2. Avoid using nulls in primary keys;
  3. Surrogate primary keys are preferred.

Update

Oooops ...
I missed phrase "Above table has no indexes" while reading question before.

Therefore only one recommendation: just create an index.

愁以何悠 2024-11-26 13:22:00

来加快速度

  • 您可以通过选择要减少结果权重的特定项目
  • 。在 WHERE 子句中,首先使用 o_crd_score >650
    crd_expiration_date 为 null 位居第二,
    最后的 crd_ effective_date>to_date('20100801','YYYYMMDD')

因此查询将如下所示。

SELECT a,b,d... 
FROM dblappc.credit_history
WHERE 
  o_crd_score >650 and 
  crd_expiration_date is null
  crd_effective_date>to_date('20100801','YYYYMMDD') and 

我在这里所做的是将记录短路到最小,以便将日期检查应用于过滤掉的记录。

您还可以通过在表上应用 INDEX 来加快速度。

You can make it faster by

  • SELECT the specific item you want to reduce the result weight.
  • In WHERE clause use the o_crd_score >650 in first place,
    crd_expiration_date is null in second place and
    crd_effective_date>to_date('20100801','YYYYMMDD') in the last

So the query will be like.

SELECT a,b,d... 
FROM dblappc.credit_history
WHERE 
  o_crd_score >650 and 
  crd_expiration_date is null
  crd_effective_date>to_date('20100801','YYYYMMDD') and 

What I am doing here is that short circuiting the the records to the minimum so that the date check will be applied to the filtered out record.

Also you can make it fast by applying the INDEX on the table.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文