sql查询需要很多时间
我正在运行以下查询:
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 技术交流群。

绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
在不知道表的大小、现有索引、执行计划等细节的情况下,很难给出任何建议。
由于查询是在两个范围内搜索,因此仅使用索引获取所有结果并不容易。
但我会首先尝试以下两个选项:
对
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)
orSimple 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
有一个具体问题和一些一般性改进。
首先,
null
值未建立索引,因此 Oracle 最有可能选择对查询进行完整索引扫描或索引跳过扫描变体,并在主键之上构建索引。如果您确实需要将
crd_expiration_date
包含到主键中,请仅在o_crd_score
和crd_ effective_date
字段上为此查询创建另一个索引。如果之后 Oracle 不会在查询文本中强制使用新索引:
一般问题很常见,并且在之前的答案中提到过:
更新
哦哦...
我之前在阅读问题时错过了“上面的表没有索引”这句话。
因此只有一个建议:只创建一个索引。
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 ono_crd_score
andcrd_effective_date
fields only.If after that Oracle won't use new index force it in query text:
General issues are common and mentioned in previous answers:
Update
Oooops ...
I missed phrase "Above table has no indexes" while reading question before.
Therefore only one recommendation: just create an index.
来加快速度
o_crd_score >650
,crd_expiration_date 为 null
位居第二,最后的
crd_ effective_date>to_date('20100801','YYYYMMDD')
因此查询将如下所示。
我在这里所做的是将记录短路到最小,以便将日期检查应用于过滤掉的记录。
您还可以通过在表上应用 INDEX 来加快速度。
You can make it faster by
o_crd_score >650
in first place,crd_expiration_date is null
in second place andcrd_effective_date>to_date('20100801','YYYYMMDD')
in the lastSo the query will be like.
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.