sql 字符串 数值型不走索引
来源：互联网 发布：南宁php招聘 编辑：程序博客网 时间：2024/05/21 15:01
SQL_ID 99tfs2tpapwqk, child number 0-------------------------------------select count(*) num from ( select a.*, b.client_name client_name2, a.err_msg err_msg2 from tbtranscfm a inner join tbclient b on a.in_client_no = b.in_client_no inner join tbtainfo c on c.ta_code = a.ta_code where a.bank_acc= :1 and (a.prd_code in ( select prd_code from tbproduct where dep_id <> dep_id and ta_code='LF' and model_flag <> '1' union select temp_b.prd_code from tbdataaccess_dep temp_a inner join tbproduct temp_b on temp_a.prd_code=temp_b.prd_code where temp_a.dep_id='' and temp_a.reserve1 like '1%' and ta_code='LF' and model_flag <> '1' union select temp_a.prd_code from tbproduct temp_a inner join tbbranch temp_b on temp_a.branch_no=temp_b.branch_no where (temp_b.internal_branch like '11%' or temp_b.internal_branch in ('11' )) and (length(rtrim(temp_a.dep_id))=0 or rtrim(temp_a.dep_id) is null) and ta_code='LF' and model_flag <> '1' union select temp_c.prd_code from tbproduct temp_c inner join tbdataaccess_bran temp_d on temp_c.prd_code=temp_d.prd_code inner Plan hash value: 2671705297 --------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | | | 86838 (100)| || 1 | SORT AGGREGATE | | 1 | 67 | | ||* 2 | FILTER | | | | | || 3 | NESTED LOOPS | | 50 | 3350 | 86838 (1)| 00:17:23 || 4 | NESTED LOOPS | | 50 | 2500 | 86788 (1)| 00:17:22 ||* 5 | INDEX UNIQUE SCAN | PK_TBTAINFO | 1 | 3 | 0 (0)| ||* 6 | TABLE ACCESS FULL | TBTRANSCFM | 50 | 2350 | 86788 (1)| 00:17:22 ||* 7 | INDEX UNIQUE SCAN | PK_TBCLIENT | 1 | 17 | 1 (0)| 00:00:01 || 8 | SORT UNIQUE | | 5 | 428 | 16 (88)| 00:00:01 || 9 | UNION-ALL | | | | | ||* 10 | TABLE ACCESS BY INDEX ROWID | TBPRODUCT | 1 | 15 | 2 (0)| 00:00:01 ||* 11 | INDEX UNIQUE SCAN | PK_TBPRODUCT | 1 | | 1 (0)| 00:00:01 ||* 12 | FILTER | | | | | || 13 | NESTED LOOPS | | 1 | 162 | 3 (0)| 00:00:01 ||* 14 | TABLE ACCESS BY INDEX ROWID | TBPRODUCT | 1 | 13 | 2 (0)| 00:00:01 ||* 15 | INDEX UNIQUE SCAN | PK_TBPRODUCT | 1 | | 1 (0)| 00:00:01 ||* 16 | TABLE ACCESS BY INDEX ROWID | TBDATAACCESS_DEP | 1 | 149 | 1 (0)| 00:00:01 ||* 17 | INDEX SKIP SCAN | PK_DATAACCESSDEP | 1 | | 1 (0)| 00:00:01 || 18 | NESTED LOOPS | | 1 | 40 | 3 (0)| 00:00:01 ||* 19 | TABLE ACCESS BY INDEX ROWID | TBPRODUCT | 1 | 22 | 2 (0)| 00:00:01 ||* 20 | INDEX UNIQUE SCAN | PK_TBPRODUCT | 1 | | 1 (0)| 00:00:01 ||* 21 | TABLE ACCESS BY INDEX ROWID | TSYS_BRANCH | 1760 | 31680 | 1 (0)| 00:00:01 ||* 22 | INDEX UNIQUE SCAN | PK_SYSBRANCH | 1 | | 0 (0)| || 23 | NESTED LOOPS | | | | | || 24 | NESTED LOOPS | | 1 | 181 | 3 (0)| 00:00:01 || 25 | NESTED LOOPS | | 1 | 163 | 3 (0)| 00:00:01 ||* 26 | TABLE ACCESS BY INDEX ROWID| TBPRODUCT | 1 | 13 | 2 (0)| 00:00:01 ||* 27 | INDEX UNIQUE SCAN | PK_TBPRODUCT | 1 | | 1 (0)| 00:00:01 ||* 28 | TABLE ACCESS BY INDEX ROWID| TBDATAACCESS_BRAN | 1 | 150 | 1 (0)| 00:00:01 ||* 29 | INDEX SKIP SCAN | PK_DATAACCBRANCH | 1 | | 1 (0)| 00:00:01 ||* 30 | INDEX UNIQUE SCAN | PK_SYSBRANCH | 1 | | 0 (0)| ||* 31 | TABLE ACCESS BY INDEX ROWID | TSYS_BRANCH | 1 | 18 | 0 (0)| || 32 | NESTED LOOPS | | 1 | 30 | 3 (0)| 00:00:01 ||* 33 | TABLE ACCESS BY INDEX ROWID | TBPRODUCT | 1 | 13 | 2 (0)| 00:00:01 ||* 34 | INDEX UNIQUE SCAN | PK_TBPRODUCT | 1 | | 1 (0)| 00:00:01 ||* 35 | TABLE ACCESS BY INDEX ROWID | TBDATAACCESS_USER | 1 | 17 | 1 (0)| 00:00:01 ||* 36 | INDEX UNIQUE SCAN | PK_DATAACCUSER | 1 | | 0 (0)| |-------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id):--------------------------------------------------- 2 - filter(("A"."PRD_CODE"=' ' OR IS NOT NULL)) 5 - access("C"."TA_CODE"=:2) 6 - filter(("A"."BANK_ACC"=:1 AND "A"."PRD_CODE"=:3 AND "A"."TA_CODE"=:2)) 7 - access("A"."IN_CLIENT_NO"="B"."IN_CLIENT_NO") 10 - filter(("DEP_ID"<>"DEP_ID" AND "TA_CODE"='LF' AND "MODEL_FLAG"<>'1')) 11 - access("PRD_CODE"=:B1) 12 - filter(NULL IS NOT NULL) 14 - filter(("TEMP_B"."TA_CODE"='LF' AND "TEMP_B"."MODEL_FLAG"<>'1')) 15 - access("TEMP_B"."PRD_CODE"=:B1) 16 - filter("TEMP_A"."RESERVE1" LIKE '1%') 17 - access("TEMP_A"."PRD_CODE"=:B1) filter("TEMP_A"."PRD_CODE"=:B1) 19 - filter(("TEMP_A"."TA_CODE"='LF' AND (RTRIM("TEMP_A"."DEP_ID") IS NULL OR LENGTH(RTRIM("TEMP_A"."DEP_ID"))=0) AND "TEMP_A"."MODEL_FLAG"<>'1')) 20 - access("TEMP_A"."PRD_CODE"=:B1) 21 - filter(("BRANCH_PATH" LIKE '11%' OR "BRANCH_PATH"='11')) 22 - access("TEMP_A"."BRANCH_NO"="BRANCH_CODE") 26 - filter(("TEMP_C"."TA_CODE"='LF' AND "TEMP_C"."MODEL_FLAG"<>'1')) 27 - access("TEMP_C"."PRD_CODE"=:B1) 28 - filter("TEMP_D"."RESERVE1" LIKE '1%') 29 - access("TEMP_D"."PRD_CODE"=:B1) filter("TEMP_D"."PRD_CODE"=:B1) 30 - access("TEMP_D"."BRANCH_NO"="BRANCH_CODE") 31 - filter(("BRANCH_PATH" LIKE '11%' OR "BRANCH_PATH"='11')) 33 - filter(("TEMP_B"."TA_CODE"='LF' AND "TEMP_B"."MODEL_FLAG"<>'1')) 34 - access("TEMP_B"."PRD_CODE"=:B1) 35 - filter("TEMP_A"."RESERVE1" LIKE '1%') 36 - access("TEMP_A"."USER_ID"='007649' AND "TEMP_A"."PRD_CODE"=:B1) OWNERSEGMENT_NAMEMBBLOCK_COUNT1IFM30TSYS_BRANCH002IFM30TBPRODUCT003IFM30TBDATAACCESS_USER004IFM30TBTAINFO005IFM30PK_DATAACCUSER006IFM30TBCLIENT2327IFM30TBTRANSCFM26213278IFM30PK_TBPRODUCT009IFM30PK_TBTAINFO0010IFM30PK_TBCLIENT7011IFM30PK_SYSBRANCH00 create index TBTRANSCFM_IDX1 on TBTRANSCFM(BANK_ACC) tablespace SALEDATAPlan hash value: 3289177790 --------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 67 | 84 (6)| 00:00:02 || 1 | SORT AGGREGATE | | 1 | 67 | | ||* 2 | FILTER | | | | | || 3 | NESTED LOOPS | | 1 | 67 | 68 (0)| 00:00:01 || 4 | NESTED LOOPS | | 1 | 50 | 67 (0)| 00:00:01 ||* 5 | INDEX UNIQUE SCAN | PK_TBTAINFO | 1 | 3 | 0 (0)| 00:00:01 ||* 6 | TABLE ACCESS BY INDEX ROWID | TBTRANSCFM | 1 | 47 | 67 (0)| 00:00:01 ||* 7 | INDEX RANGE SCAN | TBTRANSCFM_IDX1 | 62 | | 2 (0)| 00:00:01 ||* 8 | INDEX UNIQUE SCAN | PK_TBCLIENT | 1 | 17 | 1 (0)| 00:00:01 || 9 | SORT UNIQUE | | 5 | 428 | 16 (88)| 00:00:01 || 10 | UNION-ALL | | | | | ||* 11 | TABLE ACCESS BY INDEX ROWID | TBPRODUCT | 1 | 15 | 2 (0)| 00:00:01 ||* 12 | INDEX UNIQUE SCAN | PK_TBPRODUCT | 1 | | 1 (0)| 00:00:01 ||* 13 | FILTER | | | | | || 14 | NESTED LOOPS | | 1 | 162 | 3 (0)| 00:00:01 ||* 15 | TABLE ACCESS BY INDEX ROWID | TBPRODUCT | 1 | 13 | 2 (0)| 00:00:01 ||* 16 | INDEX UNIQUE SCAN | PK_TBPRODUCT | 1 | | 1 (0)| 00:00:01 ||* 17 | TABLE ACCESS BY INDEX ROWID | TBDATAACCESS_DEP | 1 | 149 | 1 (0)| 00:00:01 ||* 18 | INDEX SKIP SCAN | PK_DATAACCESSDEP | 1 | | 1 (0)| 00:00:01 || 19 | NESTED LOOPS | | 1 | 40 | 3 (0)| 00:00:01 ||* 20 | TABLE ACCESS BY INDEX ROWID | TBPRODUCT | 1 | 22 | 2 (0)| 00:00:01 ||* 21 | INDEX UNIQUE SCAN | PK_TBPRODUCT | 1 | | 1 (0)| 00:00:01 ||* 22 | TABLE ACCESS BY INDEX ROWID | TSYS_BRANCH | 1760 | 31680 | 1 (0)| 00:00:01 ||* 23 | INDEX UNIQUE SCAN | PK_SYSBRANCH | 1 | | 0 (0)| 00:00:01 || 24 | NESTED LOOPS | | | | | || 25 | NESTED LOOPS | | 1 | 181 | 3 (0)| 00:00:01 || 26 | NESTED LOOPS | | 1 | 163 | 3 (0)| 00:00:01 ||* 27 | TABLE ACCESS BY INDEX ROWID| TBPRODUCT | 1 | 13 | 2 (0)| 00:00:01 ||* 28 | INDEX UNIQUE SCAN | PK_TBPRODUCT | 1 | | 1 (0)| 00:00:01 ||* 29 | TABLE ACCESS BY INDEX ROWID| TBDATAACCESS_BRAN | 1 | 150 | 1 (0)| 00:00:01 ||* 30 | INDEX SKIP SCAN | PK_DATAACCBRANCH | 1 | | 1 (0)| 00:00:01 ||* 31 | INDEX UNIQUE SCAN | PK_SYSBRANCH | 1 | | 0 (0)| 00:00:01 ||* 32 | TABLE ACCESS BY INDEX ROWID | TSYS_BRANCH | 1 | 18 | 0 (0)| 00:00:01 || 33 | NESTED LOOPS | | 1 | 30 | 3 (0)| 00:00:01 ||* 34 | TABLE ACCESS BY INDEX ROWID | TBPRODUCT | 1 | 13 | 2 (0)| 00:00:01 ||* 35 | INDEX UNIQUE SCAN | PK_TBPRODUCT | 1 | | 1 (0)| 00:00:01 ||* 36 | TABLE ACCESS BY INDEX ROWID | TBDATAACCESS_USER | 1 | 17 | 1 (0)| 00:00:01 ||* 37 | INDEX UNIQUE SCAN | PK_DATAACCUSER | 1 | | 0 (0)| 00:00:01 |-------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id):--------------------------------------------------- 2 - filter( EXISTS ( (SELECT "PRD_CODE" FROM "TBPRODUCT" "TBPRODUCT" WHERE "PRD_CODE"=:B1 AND "DEP_ID"<>"DEP_ID" AND "TA_CODE"='LF' AND "MODEL_FLAG"<>'1')UNION (SELECT "TEMP_B"."PRD_CODE" FROM "TBPRODUCT" "TEMP_B","TBDATAACCESS_DEP" "TEMP_A" WHERE NULL IS NOT NULL AND "TEMP_A"."RESERVE1" LIKE '1%' AND "TEMP_A"."PRD_CODE"=:B2 AND "TEMP_B"."PRD_CODE"=:B3 AND "TEMP_B"."TA_CODE"='LF' AND "TEMP_B"."MODEL_FLAG"<>'1')UNION (SELECT "TEMP_A"."PRD_CODE" FROM IFM30."TSYS_BRANCH" "TSYS_BRANCH","TBPRODUCT" "TEMP_A" WHERE "TEMP_A"."PRD_CODE"=:B4 AND "TEMP_A"."TA_CODE"='LF' AND (RTRIM("TEMP_A"."DEP_ID") IS NULL OR LENGTH(RTRIM("TEMP_A"."DEP_ID"))=0) AND "TEMP_A"."MODEL_FLAG"<>'1' AND "TEMP_A"."BRANCH_NO"="BRANCH_CODE" AND ("BRANCH_PATH" LIKE '11%' OR "BRANCH_PATH"='11'))UNION (SELECT "TEMP_C"."PRD_CODE" FROM IFM30."TSYS_BRANCH" "TSYS_BRANCH","TBDATAACCESS_BRAN" "TEMP_D","TBPRODUCT" "TEMP_C" WHERE "TEMP_C"."PRD_CODE"=:B5 AND "TEMP_C"."TA_CODE"='LF' AND "TEMP_C"."MODEL_FLAG"<>'1' AND "TEMP_D"."RESERVE1" LIKE '1%' AND "TEMP_D"."PRD_CODE"=:B6 AND "TEMP_D"."BRANCH_NO"="BRANCH_CODE" AND ("BRANCH_PATH" LIKE '11%' OR "BRANCH_PATH"='11'))UNION (SELECT "TEMP_B"."PRD_CODE" FROM "TBPRODUCT" "TEMP_B","TBDATAACCESS_USER" "TEMP_A" WHERE "TEMP_A"."PRD_CODE"=:B7 AND "TEMP_A"."USER_ID"='007649' AND "TEMP_A"."RESERVE1" LIKE '1%' AND "TEMP_B"."PRD_CODE"=:B8 AND "TEMP_B"."TA_CODE"='LF' AND "TEMP_B"."MODEL_FLAG"<>'1'))) 5 - access("C"."TA_CODE"='TL') 6 - filter("A"."PRD_CODE"='CA1003' AND "A"."TA_CODE"='TL') 7 - access("A"."BANK_ACC"='6221415001161727') 8 - access("A"."IN_CLIENT_NO"="B"."IN_CLIENT_NO") 11 - filter("DEP_ID"<>"DEP_ID" AND "TA_CODE"='LF' AND "MODEL_FLAG"<>'1') 12 - access("PRD_CODE"=:B1) 13 - filter(NULL IS NOT NULL) 15 - filter("TEMP_B"."TA_CODE"='LF' AND "TEMP_B"."MODEL_FLAG"<>'1') 16 - access("TEMP_B"."PRD_CODE"=:B1) 17 - filter("TEMP_A"."RESERVE1" LIKE '1%') 18 - access("TEMP_A"."PRD_CODE"=:B1) filter("TEMP_A"."PRD_CODE"=:B1) 20 - filter("TEMP_A"."TA_CODE"='LF' AND (RTRIM("TEMP_A"."DEP_ID") IS NULL OR LENGTH(RTRIM("TEMP_A"."DEP_ID"))=0) AND "TEMP_A"."MODEL_FLAG"<>'1') 21 - access("TEMP_A"."PRD_CODE"=:B1) 22 - filter("BRANCH_PATH" LIKE '11%' OR "BRANCH_PATH"='11') 23 - access("TEMP_A"."BRANCH_NO"="BRANCH_CODE") 27 - filter("TEMP_C"."TA_CODE"='LF' AND "TEMP_C"."MODEL_FLAG"<>'1') 28 - access("TEMP_C"."PRD_CODE"=:B1) 29 - filter("TEMP_D"."RESERVE1" LIKE '1%') 30 - access("TEMP_D"."PRD_CODE"=:B1) filter("TEMP_D"."PRD_CODE"=:B1) 31 - access("TEMP_D"."BRANCH_NO"="BRANCH_CODE") 32 - filter("BRANCH_PATH" LIKE '11%' OR "BRANCH_PATH"='11') 34 - filter("TEMP_B"."TA_CODE"='LF' AND "TEMP_B"."MODEL_FLAG"<>'1') 35 - access("TEMP_B"."PRD_CODE"=:B1) 36 - filter("TEMP_A"."RESERVE1" LIKE '1%') 37 - access("TEMP_A"."USER_ID"='007649' AND "TEMP_A"."PRD_CODE"=:B1)BANK_ACC VARCHAR2(32) select count(*) num from (select a.*, b.client_name client_name2, a.err_msg err_msg2 from tbtranscfm a inner join tbclient b on a.in_client_no = b.in_client_no inner join tbtainfo c on c.ta_code = a.ta_code where a.bank_acc = '6221415001161727' and (a.prd_code in (select prd_code from tbproduct where dep_id <> dep_id and ta_code = 'LF' and model_flag <> '1' union select temp_b.prd_code from tbdataaccess_dep temp_a inner join tbproduct temp_b on temp_a.prd_code = temp_b.prd_code where temp_a.dep_id = '' and temp_a.reserve1 like '1%' and ta_code = 'LF' and model_flag <> '1' union select temp_a.prd_code from tbproduct temp_a inner join tbbranch temp_b on temp_a.branch_no = temp_b.branch_no where (temp_b.internal_branch like '11%' or temp_b.internal_branch in ('11')) and (length(rtrim(temp_a.dep_id)) = 0 or rtrim(temp_a.dep_id) is null) and ta_code = 'LF' and model_flag <> '1' union select temp_c.prd_code from tbproduct temp_c inner join tbdataaccess_bran temp_d on temp_c.prd_code = temp_d.prd_code inner join tbbranch temp_e on temp_d.branch_no = temp_e.branch_no where (temp_e.internal_branch like '11%' or temp_e.internal_branch in ('11')) and temp_d.reserve1 like '1%' and ta_code = 'LF' and model_flag <> '1' union select temp_b.prd_code from tbdataaccess_user temp_a inner join tbproduct temp_b on temp_a.prd_code = temp_b.prd_code where temp_a.user_id = '007649' and temp_a.reserve1 like '1%' and ta_code = 'LF' and model_flag <> '1') or a.prd_code is null or a.prd_code = ' ') and a.ta_code = 'TL' and a.prd_code = 'CA1003') temp_count_sql; select * from tbtranscfm a where a.bank_acc = '6221415001161727' BANK_ACC VARCHAR2(32) ' ' explain plan for select * from tbtranscfm a where a.bank_acc = '6221415001161727' ; select * from table(dbms_xplan.display()); 1Plan hash value: 28589046812 3-----------------------------------------------------------------------------------------------4| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |5-----------------------------------------------------------------------------------------------6| 0 | SELECT STATEMENT | | 62 | 22692 | 68 (0)| 00:00:01 |7| 1 | TABLE ACCESS BY INDEX ROWID| TBTRANSCFM | 62 | 22692 | 68 (0)| 00:00:01 |8|* 2 | INDEX RANGE SCAN | TBTRANSCFM_IDX1 | 62 | | 3 (0)| 00:00:01 |9-----------------------------------------------------------------------------------------------10 11Predicate Information (identified by operation id):12---------------------------------------------------13 14 2 - access("A"."BANK_ACC"='6221415001161727') explain plan for select * from tbtranscfm a where a.bank_acc = 6221415001161727 ; select * from table(dbms_xplan.display()); PLAN_TABLE_OUTPUT1Plan hash value: 29131972022 3--------------------------------------------------------------------------------4| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |5--------------------------------------------------------------------------------6| 0 | SELECT STATEMENT | | 62 | 22692 | 86788 (1)| 00:17:22 |7|* 1 | TABLE ACCESS FULL| TBTRANSCFM | 62 | 22692 | 86788 (1)| 00:17:22 |8--------------------------------------------------------------------------------9 10Predicate Information (identified by operation id):11---------------------------------------------------12 13 1 - filter(TO_NUMBER("A"."BANK_ACC")=6221415001161727) 0 0
- sql 字符串 数值型不走索引
- sql分类汇总统计，字符串类型的区间数值
- 字符串转换数值 ，数值转换字符串
- SQL基础--SQL字符串函数操作,数值操作,日期操作,空值操作
- 字符串转换为数值
- 数值输出为字符串
- 标准数值格式化字符串
- 字符串转化为数值
- 字符串与数值转换
- Shell数值、字符串比较
- 数值字符串 减数字
- 字符串数值互转
- Shell数值、字符串比较
- shell 数值 字符串比较
- Shell数值、字符串比较
- js字符串、数值比较
- Shell数值、字符串比较
- 十六进制转字符串、数值
- Tinywebserver-一个简易的web服务器
- GYM 100090 D.Insomnia（递归）
- Webgis开源解决方案之开发环境搭建(一)
- 自动的运行时软件测试工具
- 【JZOJ3640】【COCI2014】utrka
- sql 字符串 数值型不走索引
- 链表的操作
- Android中五种数据传递的方法
- poj 1251 Jungle Roads 【prim】
- 栈的原理以及实现(基于数组)
- ThinkPHP中initialize和construct的不同
- 格式化JSON串用来展示
- “私人定制”——开启定制家具2.0时代
- MySql中转换字段的null值为0
