新闻详情

新闻详情

首页 / 资讯中心 / 详情

oracle sql优化随笔3

发布时间:2026/11/15 22:20:27来源:尧图网络
oracle sql优化随笔3
初始sqlSELECT * FROM XIR_MD.VECD_MAIN T WHERE T.M_TYPE IN ( XSHG,XSHE );视图展开SELECT A.I_CODE, A.A_TYPE, A.M_TYPE, B.S_NAME AS I_NAME, A.COUNTRY, A.CURRENCY, A.Q_TYPE, A.P_CLASS, A.S_DATE AS L_DATE, 1 AS PAR_VALUE FROM XIR_MD.TSTK A, (SELECT I.*, ROW_NUMBER() OVER(PARTITION BY I.I_CODE, I.A_TYPE, I.M_TYPE ORDER BY I.END_DATE) NUM FROM XIR_MD.TSTK_NAME I WHERE I.END_DATE TO_CHAR(SYSDATE, YYYY-MM-DD)) B WHERE A.I_CODE B.I_CODE AND A.A_TYPE B.A_TYPE AND A.M_TYPE B.M_TYPE AND A.CURRENCY IN (CNY,USD,HKD) AND B.NUM 1 UNION -- 债券 SELECT I_CODE ,A_TYPE ,M_TYPE ,B_NAME ,COUNTRY ,CURRENCY ,Q_TYPE ,P_CLASS ,B_LIST_DATE ,B_PAR_VALUE FROM XIR_MD.TBND UNION -- 基金 SELECT I_CODE ,A_TYPE ,M_TYPE ,F_NAME ,COUNTRY ,CURRENCY ,Q_TYPE ,P_CLASS ,F_DATE ,F_PAR_VALUE FROM XIR_MD.TFND UNION SELECT I_CODE ,A_TYPE ,M_TYPE ,W_NAME ,COUNTRY ,CURRENCY ,Q_TYPE ,P_CLASS ,ISSUE_DATE ,1 FROM XIR_MD.TWARRANT UNION SELECT I_CODE ,A_TYPE ,M_TYPE ,I_NAME ,NULL ,CURRENCY ,Q_TYPE ,P_CLASS ,I_DATE ,1 FROM XIR_MD.TIDX UNION --期货 SELECT I_CODE ,A_TYPE ,M_TYPE ,SI_NAME ,COUNTRY ,CURRENCY ,Q_TYPE ,P_CLASS ,ISSUE_DATE ,LOTSIZE FROM XIR_MD.TSTK_IDX_FUTURE UNION SELECT I_CODE ,A_TYPE ,M_TYPE ,I_NAME ,NULL ,NULL ,NULL ,P_CLASS ,L_DATE ,1 FROM XIR_MD.TCOMPOSITE_PORT UNION --商品 SELECT I_CODE ,A_TYPE ,M_TYPE ,I_NAME ,COUNTRY ,CURRENCY ,Q_TYPE ,P_CLASS ,L_DATE ,EXCHANGE_UNIT FROM XIR_MD.TCMDT UNION --回购 SELECT I_CODE ,A_TYPE ,M_TYPE ,I_NAME ,COUNTRY ,CURRENCY ,Q_TYPE ,P_CLASS ,NULL ,100 FROM XIR_MD.TIBOR union SELECT NULL AS I_CODE, NULL AS A_TYPE, NULL AS M_TYPE, NULL AS I_NAME, NULL AS COUNTRY, NULL AS CURRENCY, NULL AS Q_TYPE, NULL AS P_CLASS, NULL AS L_DATE, 1 AS PAR_VALUE FROM XIR_MD.TIBOR WHERE 12; 再整体where M_TYPE IN ( XSHG,XSHE )查看执行计划--------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | --------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 567 (100)| | 9865 |00:00:00.05 | 2538 | | 1 | VIEW | VECD_MAIN | 1 | 4424 | 1788K| 567 (3)| 00:00:07 | 9865 |00:00:00.05 | 2538 | | 2 | SORT UNIQUE | | 1 | 4424 | 305K| 567 (91)| 00:00:07 | 9865 |00:00:00.04 | 2538 | | 3 | UNION-ALL | | 1 | | | | | 9865 |00:00:00.03 | 2538 | | 4 | NESTED LOOPS | | 1 | | | | | 5525 |00:00:00.02 | 775 | | 5 | NESTED LOOPS | | 1 | 4 | 604 | 54 (4)| 00:00:01 | 5525 |00:00:00.02 | 335 | |* 6 | VIEW | | 1 | 4 | 424 | 50 (4)| 00:00:01 | 5525 |00:00:00.01 | 165 | |* 7 | WINDOW SORT PUSHED RANK | | 1 | 4 | 160 | 50 (4)| 00:00:01 | 5527 |00:00:00.01 | 165 | |* 8 | TABLE ACCESS FULL | TSTK_NAME | 1 | 4 | 160 | 49 (3)| 00:00:01 | 5527 |00:00:00.01 | 165 | |* 9 | INDEX UNIQUE SCAN | PK_TSTK | 5525 | 1 | | 0 (0)| | 5525 |00:00:00.01 | 170 | |* 10 | TABLE ACCESS BY INDEX ROWID| TSTK | 5525 | 1 | 45 | 1 (0)| 00:00:01 | 5525 |00:00:00.01 | 440 | |* 11 | TABLE ACCESS FULL | TBND | 1 | 4170 | 289K| 480 (1)| 00:00:06 | 4170 |00:00:00.01 | 1727 | |* 12 | TABLE ACCESS FULL | TFND | 1 | 81 | 5670 | 4 (0)| 00:00:01 | 81 |00:00:00.01 | 9 | |* 13 | TABLE ACCESS FULL | TWARRANT | 1 | 1 | 150 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 3 | |* 14 | TABLE ACCESS FULL | TIDX | 1 | 5 | 325 | 3 (0)| 00:00:01 | 5 |00:00:00.01 | 3 | |* 15 | TABLE ACCESS FULL | TSTK_IDX_FUTURE | 1 | 63 | 4095 | 5 (0)| 00:00:01 | 0 |00:00:00.01 | 11 | |* 16 | TABLE ACCESS FULL | TCOMPOSITE_PORT | 1 | 8 | 400 | 3 (0)| 00:00:01 | 8 |00:00:00.01 | 3 | |* 17 | TABLE ACCESS FULL | TCMDT | 1 | 5 | 290 | 3 (0)| 00:00:01 | 2 |00:00:00.01 | 3 | |* 18 | TABLE ACCESS FULL | TIBOR | 1 | 86 | 5332 | 3 (0)| 00:00:01 | 74 |00:00:00.01 | 4 | |* 19 | FILTER | | 1 | | | | | 0 |00:00:00.01 | 0 | | 20 | INDEX FULL SCAN | PK_TIBOR | 0 | 129 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | ---------------------------------------------------------------------------------------------------------------------------------------------主要问题在于union 了TBND的全表扫描SELECT I_CODE ,A_TYPE ,M_TYPE ,B_NAME ,COUNTRY ,CURRENCY ,Q_TYPE ,P_CLASS ,B_LIST_DATE ,B_PAR_VALUE FROM XIR_MD.TBND T where M_TYPE IN (XSHG,XSHE);M_TYPE字段的选择性太差导致单建M_TYPE索引根本不走hint后消耗反而变高无索引状态 -------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | -------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 480 (100)| | 4170 |00:00:00.01 | 1977 | |* 1 | TABLE ACCESS FULL| TBND | 1 | 4170 | 289K| 480 (1)| 00:00:06 | 4170 |00:00:00.01 | 1977 | -------------------------------------------------------------------------------------------------------------------- create index idx_haha_23 on XIR_MD.TBND(M_TYPE); -------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | -------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 846 (100)| | 4170 |00:00:00.01 | 2616 | | 1 | INLIST ITERATOR | | 1 | | | | | 4170 |00:00:00.01 | 2616 | | 2 | TABLE ACCESS BY INDEX ROWID| TBND | 2 | 4170 | 289K| 846 (1)| 00:00:11 | 4170 |00:00:00.01 | 2616 | |* 3 | INDEX RANGE SCAN | IDX_HAHA_23 | 2 | 4170 | | 12 (9)| 00:00:01 | 4170 |00:00:00.01 | 291 | --------------------------------------------------------------------------------------------------------------------------------------将全表扫描改成INDEX FAST FULL SCAN该部分消耗下来了。create index idx_haha_25 on XIR_MD.TBND(M_TYPE,A_TYPE,B_NAME,COUNTRY,CURRENCY,Q_TYPE,P_CLASS,B_LIST_DATE,B_PAR_VALUE); ------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 140 (100)| | 4170 |00:00:00.02 | 420 | |* 1 | VIEW | index$_join$_001 | 1 | 4170 | 289K| 140 (1)| 00:00:02 | 4170 |00:00:00.02 | 420 | |* 2 | HASH JOIN | | 1 | | | | | 4170 |00:00:00.01 | 420 | | 3 | INLIST ITERATOR | | 1 | | | | | 4170 |00:00:00.01 | 41 | |* 4 | INDEX RANGE SCAN | IDX_HAHA_25 | 2 | 4170 | 289K| 46 (5)| 00:00:01 | 4170 |00:00:00.01 | 41 | |* 5 | INDEX FAST FULL SCAN| IDX_TBND | 1 | 4170 | 289K| 119 (0)| 00:00:02 | 4170 |00:00:00.01 | 379 | -------------------------------------------------------------------------------------------------------------------------------------整体执行计划---------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | ---------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 227 (100)| | 9865 |00:00:00.04 | 952 | | 1 | VIEW | VECD_MAIN | 1 | 4424 | 1788K| 227 (6)| 00:00:03 | 9865 |00:00:00.04 | 952 | | 2 | SORT UNIQUE | | 1 | 4424 | 305K| 227 (78)| 00:00:03 | 9865 |00:00:00.04 | 952 | | 3 | UNION-ALL | | 1 | | | | | 9865 |00:00:00.03 | 952 | | 4 | NESTED LOOPS | | 1 | | | | | 5525 |00:00:00.02 | 773 | | 5 | NESTED LOOPS | | 1 | 4 | 604 | 54 (4)| 00:00:01 | 5525 |00:00:00.01 | 334 | |* 6 | VIEW | | 1 | 4 | 424 | 50 (4)| 00:00:01 | 5525 |00:00:00.01 | 164 | |* 7 | WINDOW SORT PUSHED RANK | | 1 | 4 | 160 | 50 (4)| 00:00:01 | 5527 |00:00:00.01 | 164 | |* 8 | TABLE ACCESS FULL | TSTK_NAME | 1 | 4 | 160 | 49 (3)| 00:00:01 | 5527 |00:00:00.01 | 164 | |* 9 | INDEX UNIQUE SCAN | PK_TSTK | 5525 | 1 | | 0 (0)| | 5525 |00:00:00.01 | 170 | |* 10 | TABLE ACCESS BY INDEX ROWID| TSTK | 5525 | 1 | 45 | 1 (0)| 00:00:01 | 5525 |00:00:00.01 | 439 | |* 11 | VIEW | index$_join$_005 | 1 | 4170 | 289K| 140 (1)| 00:00:02 | 4170 |00:00:00.01 | 143 | |* 12 | HASH JOIN | | 1 | | | | | 4170 |00:00:00.01 | 143 | | 13 | INLIST ITERATOR | | 1 | | | | | 4170 |00:00:00.01 | 41 | |* 14 | INDEX RANGE SCAN | IDX_HAHA_25 | 2 | 4170 | 289K| 46 (5)| 00:00:01 | 4170 |00:00:00.01 | 41 | |* 15 | INDEX FAST FULL SCAN | IDX_TBND | 1 | 4170 | 289K| 119 (0)| 00:00:02 | 4170 |00:00:00.01 | 102 | |* 16 | TABLE ACCESS FULL | TFND | 1 | 81 | 5670 | 4 (0)| 00:00:01 | 81 |00:00:00.01 | 9 | |* 17 | TABLE ACCESS FULL | TWARRANT | 1 | 1 | 150 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 3 | |* 18 | TABLE ACCESS FULL | TIDX | 1 | 5 | 325 | 3 (0)| 00:00:01 | 5 |00:00:00.01 | 3 | |* 19 | TABLE ACCESS FULL | TSTK_IDX_FUTURE | 1 | 63 | 4095 | 5 (0)| 00:00:01 | 0 |00:00:00.01 | 11 | |* 20 | TABLE ACCESS FULL | TCOMPOSITE_PORT | 1 | 8 | 400 | 3 (0)| 00:00:01 | 8 |00:00:00.01 | 3 | |* 21 | TABLE ACCESS FULL | TCMDT | 1 | 5 | 290 | 3 (0)| 00:00:01 | 2 |00:00:00.01 | 3 | |* 22 | TABLE ACCESS FULL | TIBOR | 1 | 86 | 5332 | 3 (0)| 00:00:01 | 74 |00:00:00.01 | 4 | |* 23 | FILTER | | 1 | | | | | 0 |00:00:00.01 | 0 | | 24 | INDEX FULL SCAN | PK_TIBOR | 0 | 129 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | ----------------------------------------------------------------------------------------------------------------------------------------------但是由于需要添加大量的select列索引较大也不便于表的其他增删改动作并且当前该sql优先级并非最高所以暂时保留处理方式待日后查询时间接受不了了再做优化变更。
网站建设高端定制企业官网
RELATED

相关资讯

更多精彩内容,欢迎继续阅读

较早相关资讯

最新相关资讯

【外设】之大彩串口显示屏 2026/11/15 4:54:16

【外设】之大彩串口显示屏

大彩串口屏初步使用 1 .官网下载 STM32 屏幕 GUI 设计资料 http://www.gz-dc.com/category/typeid/4112 找到 STM32 Keil 工程,移植相关代码因项目而异进行移植,由于项目简单,本人只对用到的指令接口进行修改。 比如:注意事项&…

阅读更多 →
无法下载Windows系统iso文件 2026/11/15 2:04:12

无法下载Windows系统iso文件

当我遇到这个问题的时候,我打开了一个网站: 登录 然后我打算下载的时候: 突然那个官方的连接就可以下载了:

阅读更多 →
【清华代码熊】DeepSeek V4.1 Flash 后训练详解 2026/11/14 10:11:15

【清华代码熊】DeepSeek V4.1 Flash 后训练详解

📌 上期解析了 DeepSeek V4.1 Flash 模型架构改进,本期解析 DeepSeek V4.1 Flash 预训练/后训练技术: 🌟 预训练:45T 文本 多模态混合语料、直接训练 sparse attention(取消 DeepSeek V4 的 dense 冷启动&…

阅读更多 →
Shuffle-R1: Efficient RL framework for Multimodal Large Language Models via Data-centric Dynamic ... 2026/11/16 6:47:19

Shuffle-R1: Efficient RL framework for Multimodal Large Language Models via Data-centric Dynamic ...

文章主要内容和创新点 主要内容 本文聚焦于多模态大语言模型(MLLM)强化学习(RL)训练中的效率问题,提出了一个名为Shuffle-R1的框架。研究发现,当前RL训练存在两个关键缺陷: 优势值坍缩(Advantage Collapsing):批次中大多数优势值集中在零附近,导致有效梯度信号被淹…

阅读更多 →
PRvL: Quantifying the Capabilities and Risks of Large Language Models for PII Redaction 2026/11/14 16:35:50

PRvL: Quantifying the Capabilities and Risks of Large Language Models for PII Redaction

一、文章主要内容总结 本文聚焦于利用大型语言模型(LLMs)实现个人身份信息(PII)脱敏的研究,旨在解决传统脱敏方法(如基于规则的系统、领域特定命名实体识别(NER)模型)泛化能力差、跨格式/跨语境适应性弱的问题。 研究通过全面评估多种LLM架构(包括密集型LLM(D-LLM…

阅读更多 →
LLaVA-RE: Binary Image-Text Relevancy Evaluation with Multimodal Large Language Model 2026/11/16 17:28:18

LLaVA-RE: Binary Image-Text Relevancy Evaluation with Multimodal Large Language Model

文章主要内容和创新点 主要内容 本文聚焦于二进制图像-文本相关性评估任务(判断图像与文本“相关”或“不相关”),针对该任务中文本格式多样、相关性定义随场景变化等挑战,提出了基于多模态大语言模型(MLLM)的解决方案LLaVA-RE。 模型设计:LLaVA-RE基于LLaVA 1.5架构,…

阅读更多 →

今日资讯

本周资讯

本月资讯

看完文章仍有疑问?

联系尧图顾问,获取一对一建站咨询

立即免费咨询 📞 400-888-8888
📞 ✉