[20201203]为什么不使用索引.txt

来源：这里教程网时间：2026-03-03 16:17:12 作者：

[20201203]为什么不使用索引.txt --//生产系统一条sql语句出现问题： 1.环境： > @ ver1 PORT_STRING VERSION BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production 2.问题探究： > @ dpc 485xs929brpxa '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID 485xs929brpxa, child number 1 ------------------------------------- SELECT "MS_BRDA"."BRID" , "MS_BRDA"."MZHM" , "MS_BRDA"."BRXM" , "MS_BRDA"."FYZH" , "MS_BRDA"."SFZH" , "MS_BRDA"."BRXZ" , "MS_BRDA"."BRXB" , "MS_BRDA"."CSNY" , "MS_BRDA"."HYZK" , "MS_BRDA"."ZYDM" , "MS_BRDA"."MZDM" , "MS_BRDA"."XXDM" , "MS_BRDA"."GMYW" , "MS_BRDA"."DWXH" , "MS_BRDA"."DWMC" , "MS_BRDA"."DWDH" , "MS_BRDA"."DWYB" , "MS_BRDA"."HKDZ" , "MS_BRDA"."JTDH" , "MS_BRDA"."HKYB" , "MS_BRDA"."JZCS" , "MS_BRDA"."JZRQ" , "MS_BRDA"."CZRQ" , "MS_BRDA"."JZKH" , "MS_BRDA"."SFDM" , "MS_BRDA"."JGDM" , "MS_BRDA"."GJDM" , "MS_BRDA"."LXRM" , "MS_BRDA"."LXGX" , "MS_BRDA"."LXDZ" , "MS_BRDA"."LXDH" , "MS_BRDA"."DBRM" , "MS_BRDA"."DBGX" , "MS_BRDA"."SBHM" , Plan hash value: 1849663881 ------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | OMem | 1Mem | Used-Mem | ------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | | | | 33524 (100)| | | | | | 1 | SORT ORDER BY | | 62430 | 9693K| 19M| 33524 (1)| 00:06:43 | 48128 | 48128 |43008 (0)| |* 2 | TABLE ACCESS FULL| MS_BRDA | 62430 | 9693K| | 31339 (1)| 00:06:17 | | | | ------------------------------------------------------------------------------------------------------------------ Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 2 - SEL$1 / MS_BRDA@SEL$1 Peeked Binds (identified by position): -------------------------------------- 1 - (DATE): 08/18/2020 00:00:00 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("MS_BRDA"."JDSJ">=:LDT_JDSJ) --//首先对应索引是存在的，为什么不使用呢? --//注意下划线，一开始我以为是查询时间的问题，注意它的过滤条件仅仅一个"MS_BRDA"."JDSJ">=:LDT_JDSJ,带入参数是08/18/2020 00:00:00. > @ tpt/sqlid 485xs929brpxa % Show SQL text, child cursors and execution stats for SQLID 485xs929brpxa child nvl('%','%') HASH_VALUE PLAN_HASH_VALUE CH# SQL_TEXT ---------- --------------- ---- ------------------------------------------------------------------------------------------------------------------------------------------------------ 2461783978 1849663881 1 SELECT "MS_BRDA"."BRID" , "MS_BRDA"."MZHM" , "MS_BRDA"."BRXM" , "MS_BRDA"."FYZH" , "MS_BRDA"."SFZH" , "MS_BRDA"."BRXZ" , "MS_BRDA"."BRXB" , "MS_BRDA"."CSNY" , "MS_BRDA"."HYZK" , "MS_BRDA"."ZYDM" , "MS_BRDA"."MZDM" , "MS_BRDA"."XXDM" , "MS_BRDA"."GMYW" , "MS_BRDA"."DWXH" , "MS_BRDA"."DWMC" , "MS_BRDA"."DWDH" , "MS_BRDA"."DWYB" , "MS_BRDA"."HKDZ" , "MS_BRDA"."JTDH" , "MS_BRDA"."HKYB" , "MS_BRDA"."JZCS" , "MS_BRDA"."JZRQ" , "MS_BRDA"."CZRQ" , "MS_BRDA"."JZKH" , "MS_BRDA"."SFDM" , "MS_BRDA"."JGDM" , "MS_BRDA"."GJDM" , "MS_BRDA"."LXRM" , "MS_BRDA"."LXGX" , "MS_BRDA"."LXDZ" , "MS_BRDA"."LXDH" , "MS_BRDA"."DBRM" , "MS_BRDA"."DBGX" , "MS_BRDA"."SBHM" , CH# PARENT_HANDLE OBJECT_HANDLE PLAN_HASH PARSES H_PARSES EXECUTIONS FETCHES ROWS_PROCESSED ROWS_PER_FETCH CPU_SEC CPU_SEC_EXEC ELA_SEC ELA_SEC_EXEC LIOS LIOS_EXEC PIOS SORTS USERS_EXECUTING ---- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- -------------- -------------- ---------- ------------ ---------- ------------ ---------- ---------- ---------- ---------- --------------- 1 00000005FA105698 00000005A7F792D0 1849663881 91379 1 91380 182760 8590622 47.0049354 107272.287 1.17391428 107565.243 1.17712019 1095809048 11991.7821 7 91380 0 > @ bind_cap 485xs929brpxa '' C200 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SELECT "MS_BRDA"."BRID" , "MS_BRDA"."MZHM" , "MS_BRDA"."BRXM" , "MS_BRDA"."FYZH" , "MS_BRDA"."SFZH" , "MS_BRDA"."BRXZ" , "MS_BRDA"."BRXB" , "MS_BRDA"."CSNY" , "MS_BRDA"."HYZK" , "MS_BRDA"."ZYDM" , "MS_BRDA"."MZDM" , "MS_BRDA"."XXDM" , "MS_BRDA"."GMYW" , "MS_BRDA"."DW XH" , "MS_BRDA"."DWMC" , "MS_BRDA"."DWDH" , "MS_BRDA"."DWYB" , "MS_BRDA"."HKDZ" , "MS_BRDA"."JTDH" , "MS_BRDA"."HKYB" , "MS_BRDA". "JZCS" , "MS_BRDA"."JZRQ" , "MS_BRDA"."CZRQ" , "MS_BRDA"."JZKH" , "MS_BRDA"."SFDM" , "MS_BRDA"."JGDM" , "MS_BRDA"."GJDM" , "MS_BRD A"."LXRM" , "MS_BRDA"."LXGX" , "MS_BRDA"."LXDZ" , "MS_BRDA"."LXDH" , "MS_BRDA"."DBRM" , "MS_BRDA"."DBGX" , "MS_BRDA"."SBHM" , "MS_ BRDA"."YBKH" , "MS_BRDA"."ZZTX" , "MS_BRDA"."JDSJ" , "MS_BRDA"."JDR" , "MS_BRDA"."ZXBZ" , "MS_BRDA"."ZXR" , "MS_BRDA"."ZXSJ" , "MS _BRDA"."CSD_SQS" , "MS_BRDA"."CSD_S" , "MS_BRDA"."CSD_X" , "MS_BRDA"."JGDM_SQS" , "MS_BRDA"."JGDM_S" , "MS_BRDA"."XZZ_SQS" , "MS_BRDA"."XZZ_ S" , "MS_BRDA"."XZZ_X" , "MS_BRDA"."XZZ_YB" , "MS_BRDA"."XZZ_DH" , "MS_BRDA"."HKDZ_SQS" , "MS_BRDA"."HKDZ_S" , "MS_BRDA"."HKDZ_X" , "MS_BRDA"."XZZ_QTDZ" , "MS_BRDA"."HKDZ_QTDZ" , "MS_BRDA"."BRSF" , "MS_BRDA"."YYRQ" , "MS_BRDA"."YYSD" , "MS_BRDA"."BRLY" , "MS_BRDA"."PYDM " , "MS_BRDA"."FYLX" , "MS_BRDA"."XSETZ" , "MS_BRDA"."PBRY" , "MS_BRDA"."PBRDZ" , "MS_BRDA"."ZJLX" , "MS_BRDA"."ZYCS" , "MS_BRDA". "BASID" , "MS_BRDA"."BASZYH" , "MS_BRDA"."VIP" , "MS_BRDA"."XGR" , "MS_BRDA"."XGRQ" , "MS_BRDA"."SSXDZ" , "MS_BRDA"."YXZJHM" , "MS _BRDA"."YXZJLX" FROM "MS_BRDA" WHERE ( "MS_BRDA"."JDSJ" >= :ldt_jdsj ) ORDER BY "MS_BRDA"."JDSJ" ASC SQL_ID CHILD_NUMBER WAS NAME POSITION MAX_LENGTH LAST_CAPTURED DATATYPE_STRING VALUE_STRING C30 ------------- ------------ --- ---------------------------------------- ---------- ---------- ------------------- --------------- -------------------------------------------------- ------------------------------ 485xs929brpxa 1 YES :LDT_JDSJ 1 7 2020-12-03 11:21:59 DATE 2020/12/02 00:00:00 --//实际上查询时间范围不大，数据返回量应该不大。 > @ tab_lh XXXXXX_YYY MS_BRDA JDSJ DISPLAY TABLE_NAME OF COLUMN_NAME INFORMATION. INPUT OWNER TABLE_NAME COLUMN SAMPLE : @ TAB_LH TABLE_NAME [COLUMN_NAME] IF NOT INPUT COLUMN_NAME ,USE "" . Column Null Distinct Sample Number Number Name DATA_TYPE DATA_LENGTH able Values Density Size TRANS_LOW TRANS_HIGH Nulls Buckets LAST_ANALYZED HISTOGRAM DATA_DEFAULT ------------------------ ---------- ----------- ---- ------------ ----------- -------------- -------------------------------- -------------------------------- ---------- ------- ------------------- --------------- -------------------- JDSJ DATE 7 N 3,315,200 0.00000030 4,571,135 0100-11-26 00:00:00 2047-03-18 12:55:29 0 1 2017-02-14 11:34:04 NONE --//注意看TRANS_HIGH=2047-03-18 12:55:29。表里面有一些垃圾数据的干扰，导致优化器认为该日期范围很大，选择了全表扫描。不过 --//我感到奇怪的是最小值也是有问题的0100-11-26 00:00:00。安装道理应该纠正回来啊。 --//一个简单例子就可以验证问题在哪里： > explain plan for select * from MS_BRDA where JDSJ>=trunc(sysdate)+10; > @ dp PLAN_TABLE_OUTPUT --------------------------- Plan hash value: 3614505696 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 61678 | 9576K| 31425 (1)| 00:06:18 | |* 1 | TABLE ACCESS FULL| MS_BRDA | 61678 | 9576K| 31425 (1)| 00:06:18 | ----------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 / MS_BRDA@SEL$1 Outline Data ------------- /*+ BEGIN_OUTLINE_DATA FULL(@"SEL$1" "MS_BRDA"@"SEL$1") OUTLINE_LEAF(@"SEL$1") ALL_ROWS OPT_PARAM('_bloom_filter_enabled' 'false') DB_VERSION('11.2.0.4') OPTIMIZER_FEATURES_ENABLE('11.2.0.4') IGNORE_OPTIM_EMBEDDED_HINTS END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("JDSJ">=TRUNC(SYSDATE@!)+10) --//可以发现我查询条件TDSJ>=trunc(sysdate)+10;竟然估计返回61678。而实际的情况仅仅返回一条。 --//简单推测看看 --//(2047-2020)/(2047-100)*4571135 = 63390.16,与执行计划看到差不多。457113数值来源与统计 > @ sosiz XXXXXX_YYY MS_BRDA ********************************** Table Level 参数 schema tablename ********************************** Table Number Empty Average Chain Average Global User Sample Name of Rows Blocks Blocks Space Count Row Len Stats Stats Size LAST_ANALYZED --------------- -------------- ------------ ------------ -------- -------- -------- ------ ------ -------------- ------------------- MS_BRDA 4,571,135 115,200 0 0 0 159 YES NO 4,571,135 2017-02-14 11:34:04 > select jdsj from MS_BRDA where JDSJ>=trunc(sysdate)+10; JDSJ ------------------- 2047-03-18 12:55:29 --//正是由于操作人员录入了错误的数据，如果某次分析表后，统计信息出现异常，而导致执行计划发生畸变，选择全表扫描，当然这里不是这样的情况。 3.解决方法： --//敦促相关人员纠正录入数据库表中的错误。 --//重新分析表，取样小一些看看是否能规避取样问题。 BEGIN SYS.DBMS_STATS.GATHER_TABLE_STATS ( OwnName => 'XXXXXX_YYY' ,TabName => 'MS_BRDA' ,Estimate_Percent => 1 ,Method_Opt => 'FOR ALL COLUMNS SIZE REPEAT ' ,Degree => 4 ,Cascade => TRUE ,FORCE => true ,No_Invalidate => FALSE); END; / > @ tab_lh XXXXXX_YYY MS_BRDA JDSJ DISPLAY TABLE_NAME OF COLUMN_NAME INFORMATION. INPUT OWNER TABLE_NAME COLUMN SAMPLE : @ TAB_LH TABLE_NAME [COLUMN_NAME] IF NOT INPUT COLUMN_NAME ,USE "" . Column Null Distinct Sample Number Number Name DATA_TYPE DATA_LENGTH able Values Density Size TRANS_LOW TRANS_HIGH Nulls Buckets LAST_ANALYZED HISTOGRAM DATA_DEFAULT ------------------------ -------------------- ----------- ---- ------------ ----------- -------------- -------------------------------- -------------------------------- ---------- ------- ------------------- --------------- -------------------- JDSJ DATE 7 N 519,595 0.00000192 57,843 0100-11-26 00:00:00 2020-12-03 11:35:34 0 1 2020-12-03 11:48:40 NONE --//TRANS_HIGH=2020-12-03 11:35:34. > explain plan for select * from MS_BRDA where JDSJ>=trunc(sysdate)+10; > @ dp PLAN_TABLE_OUTPUT --------------------------- Plan hash value: 1302555158 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 11 | 1826 | 11 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| MS_BRDA | 11 | 1826 | 11 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | I_MS_BRDA_JDSJ | 11 | | 3 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 / MS_BRDA@SEL$1 2 - SEL$1 / MS_BRDA@SEL$1 Outline Data ------------- /*+ BEGIN_OUTLINE_DATA INDEX_RS_ASC(@"SEL$1" "MS_BRDA"@"SEL$1" ("MS_BRDA"."JDSJ")) OUTLINE_LEAF(@"SEL$1") ALL_ROWS OPT_PARAM('_bloom_filter_enabled' 'false') DB_VERSION('11.2.0.4') OPTIMIZER_FEATURES_ENABLE('11.2.0.4') IGNORE_OPTIM_EMBEDDED_HINTS END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("JDSJ">=TRUNC(SYSDATE@!)+10) --//暂时这样解决问题。

编辑推荐：

返回列表

[20201203]为什么不使用索引.txt

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推