sql 字符串 数值型不走索引
程序员文章站
2022-04-12 21:18:00
sql 字符串 数值型不走索引
SQL_ID 99tfs2tpapwqk, child number 0
--------------------------------...
sql 字符串 数值型不走索引
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)
OWNER SEGMENT_NAME MB BLOCK_COUNT
1 IFM30 TSYS_BRANCH 0 0
2 IFM30 TBPRODUCT 0 0
3 IFM30 TBDATAACCESS_USER 0 0
4 IFM30 TBTAINFO 0 0
5 IFM30 PK_DATAACCUSER 0 0
6 IFM30 TBCLIENT 23 2
7 IFM30 TBTRANSCFM 2621 327
8 IFM30 PK_TBPRODUCT 0 0
9 IFM30 PK_TBTAINFO 0 0
10 IFM30 PK_TBCLIENT 7 0
11 IFM30 PK_SYSBRANCH 0 0
create index TBTRANSCFM_IDX1 on TBTRANSCFM(BANK_ACC) tablespace SALEDATA
Plan 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());
1 Plan hash value: 2858904681
2
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
11 Predicate 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_OUTPUT
1 Plan hash value: 2913197202
2
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
10 Predicate Information (identified by operation id):
11 ---------------------------------------------------
12
13 1 - filter(TO_NUMBER("A"."BANK_ACC")=6221415001161727)
上一篇： C、C++动态数组实现