在两个表做连接的时候,相关列都建了索引了,但是在取某些字段的时候,连接会用到索引,但是取另外一些字段的时候,连接又不会用到索引,求高手解读下原因。
附上这两种情况的脚本和执行计划:
case 1:SELECT BILLINGNEW_GBILL.SEQ_TF_B_WRIOFFLOG.NEXTVAL WRITEOFF_ID,
A.LATN_ID || A.TRADE_SEQ CHARGE_ID,
TO_NUMBER(SUBSTR(TO_CHAR(A.CREATE_DATE, 'YYYYMM'), 5, 2), 99) PARTITION_ID,
A.BILL_CYCLE_SEQ CYCLE_ID,
'20' NET_TYPE_CODE,
NVL((SELECT A.BILL_CYCLE_SEQ || T.BILL_SEQ
FROM BILLINGNEW_GBILL.MID_GW_ACCOUNT_ID T
WHERE T.OLD_ACCOUNT_ID = A.ACCOUNT_ID),
-1) BILL_ID,
(CASE
WHEN A.SPEC_PREPAY_SEQ IS NULL OR A.SPEC_PREPAY_SEQ = 0 THEN
NVL((SELECT ACCT_BALANCE_ID
FROM BILLINGNEW_GBILL.MID_GW_ACCT_BALANCE_ID
WHERE OLD_ACCOUNT_ID = A.ACCOUNT_ID),
-1)
ELSE
TO_NUMBER(A.LATN_ID || A.SPEC_PREPAY_SEQ)
END) ACCT_BALANCE_ID,
0 IMP_FEE,
0 NEW_BALANCE,
0 LATE_FEE,
0 LATE_BALANCE
FROM BILLING_GBILL.T_PAYMENT_DISTRIBUTION A,
BILLING_GBILL.T_BILL_BALANCE_DETAIL_201108 B
WHERE A.LATN_ID = B.LATN_ID
AND A.DISTRIBUTION_TYPE NOT IN ('04', '05')
AND A.IS_PART_PAY = 0
AND A.BILL_CYCLE_SEQ = 201108
AND A.STATUS IN ('0', '1')
AND B.PAYMENT_DIST_SEQ = A.PAYMENT_DIST_SEQ执行计划如下SELECT STATEMENT, GOAL = ALL_ROWS 51868 3846819 446231004
TABLE ACCESS BY INDEX ROWID BILLINGNEW_GBILL MID_GW_ACCOUNT_ID 4 1 18
INDEX RANGE SCAN BILLING_GBILL I_MID_GW_ACCOUNT_ID 3 1
TABLE ACCESS BY INDEX ROWID BILLINGNEW_GBILL MID_GW_ACCT_BALANCE_ID 4 1 17
INDEX RANGE SCAN BILLINGNEW_GBILL INDEX_MID_GW_ACCT_BAL_ID 3 1
SEQUENCE BILLINGNEW_GBILL SEQ_TF_B_WRIOFFLOG
HASH JOIN 51868 3846819 446231004
TABLE ACCESS FULL BILLING_GBILL T_PAYMENT_DISTRIBUTION 10387 1676255 179359285
INDEX FAST FULL SCAN BILLING_GBILL IND_DETBIL1_201108 13783 18203492 163831428case 2:SELECT BILLINGNEW_GBILL.SEQ_TF_B_WRIOFFLOG.NEXTVAL WRITEOFF_ID,
A.LATN_ID || A.TRADE_SEQ CHARGE_ID,
TO_NUMBER(SUBSTR(TO_CHAR(A.CREATE_DATE, 'YYYYMM'), 5, 2), 99) PARTITION_ID,
A.BILL_CYCLE_SEQ CYCLE_ID,
'20' NET_TYPE_CODE,
NVL((SELECT A.BILL_CYCLE_SEQ || T.BILL_SEQ
FROM BILLINGNEW_GBILL.MID_GW_ACCOUNT_ID T
WHERE T.OLD_ACCOUNT_ID = A.ACCOUNT_ID),
-1) BILL_ID,
(CASE
WHEN A.SPEC_PREPAY_SEQ IS NULL OR A.SPEC_PREPAY_SEQ = 0 THEN
NVL((SELECT ACCT_BALANCE_ID
FROM BILLINGNEW_GBILL.MID_GW_ACCT_BALANCE_ID
WHERE OLD_ACCOUNT_ID = A.ACCOUNT_ID),
-1)
ELSE
TO_NUMBER(A.LATN_ID || A.SPEC_PREPAY_SEQ)
END) ACCT_BALANCE_ID,
0 IMP_FEE,
B.TOTAL_PAID FEE,
B.TOTAL_PAID OLD_BALANCE,
0 NEW_BALANCE,
0 LATE_FEE,
0 LATE_BALANCE
FROM BILLING_GBILL.T_PAYMENT_DISTRIBUTION A,
BILLING_GBILL.T_BILL_BALANCE_DETAIL_201108 B
WHERE A.LATN_ID = B.LATN_ID
AND A.DISTRIBUTION_TYPE NOT IN ('04', '05')
AND A.IS_PART_PAY = 0
AND A.BILL_CYCLE_SEQ = 201108
AND A.STATUS IN ('0', '1')
AND B.PAYMENT_DIST_SEQ = A.PAYMENT_DIST_SEQ执行计划如下
SELECT STATEMENT, GOAL = ALL_ROWS 151311 3846819 457771461
TABLE ACCESS BY INDEX ROWID BILLINGNEW_GBILL MID_GW_ACCOUNT_ID 4 1 18
INDEX RANGE SCAN BILLING_GBILL I_MID_GW_ACCOUNT_ID 3 1
TABLE ACCESS BY INDEX ROWID BILLINGNEW_GBILL MID_GW_ACCT_BALANCE_ID 4 1 17
INDEX RANGE SCAN BILLINGNEW_GBILL INDEX_MID_GW_ACCT_BAL_ID 3 1
SEQUENCE BILLINGNEW_GBILL SEQ_TF_B_WRIOFFLOG
HASH JOIN 151311 3846819 457771461
TABLE ACCESS FULL BILLING_GBILL T_PAYMENT_DISTRIBUTION 10387 1676255 179359285
TABLE ACCESS FULL BILLING_GBILL T_BILL_BALANCE_DETAIL_201108 110644 18203492 218441904
可以看到,我只是在第二个sql中添加了2个字段,为何连接方式跟执行代价差别这么大?
附上这两种情况的脚本和执行计划:
case 1:SELECT BILLINGNEW_GBILL.SEQ_TF_B_WRIOFFLOG.NEXTVAL WRITEOFF_ID,
A.LATN_ID || A.TRADE_SEQ CHARGE_ID,
TO_NUMBER(SUBSTR(TO_CHAR(A.CREATE_DATE, 'YYYYMM'), 5, 2), 99) PARTITION_ID,
A.BILL_CYCLE_SEQ CYCLE_ID,
'20' NET_TYPE_CODE,
NVL((SELECT A.BILL_CYCLE_SEQ || T.BILL_SEQ
FROM BILLINGNEW_GBILL.MID_GW_ACCOUNT_ID T
WHERE T.OLD_ACCOUNT_ID = A.ACCOUNT_ID),
-1) BILL_ID,
(CASE
WHEN A.SPEC_PREPAY_SEQ IS NULL OR A.SPEC_PREPAY_SEQ = 0 THEN
NVL((SELECT ACCT_BALANCE_ID
FROM BILLINGNEW_GBILL.MID_GW_ACCT_BALANCE_ID
WHERE OLD_ACCOUNT_ID = A.ACCOUNT_ID),
-1)
ELSE
TO_NUMBER(A.LATN_ID || A.SPEC_PREPAY_SEQ)
END) ACCT_BALANCE_ID,
0 IMP_FEE,
0 NEW_BALANCE,
0 LATE_FEE,
0 LATE_BALANCE
FROM BILLING_GBILL.T_PAYMENT_DISTRIBUTION A,
BILLING_GBILL.T_BILL_BALANCE_DETAIL_201108 B
WHERE A.LATN_ID = B.LATN_ID
AND A.DISTRIBUTION_TYPE NOT IN ('04', '05')
AND A.IS_PART_PAY = 0
AND A.BILL_CYCLE_SEQ = 201108
AND A.STATUS IN ('0', '1')
AND B.PAYMENT_DIST_SEQ = A.PAYMENT_DIST_SEQ执行计划如下SELECT STATEMENT, GOAL = ALL_ROWS 51868 3846819 446231004
TABLE ACCESS BY INDEX ROWID BILLINGNEW_GBILL MID_GW_ACCOUNT_ID 4 1 18
INDEX RANGE SCAN BILLING_GBILL I_MID_GW_ACCOUNT_ID 3 1
TABLE ACCESS BY INDEX ROWID BILLINGNEW_GBILL MID_GW_ACCT_BALANCE_ID 4 1 17
INDEX RANGE SCAN BILLINGNEW_GBILL INDEX_MID_GW_ACCT_BAL_ID 3 1
SEQUENCE BILLINGNEW_GBILL SEQ_TF_B_WRIOFFLOG
HASH JOIN 51868 3846819 446231004
TABLE ACCESS FULL BILLING_GBILL T_PAYMENT_DISTRIBUTION 10387 1676255 179359285
INDEX FAST FULL SCAN BILLING_GBILL IND_DETBIL1_201108 13783 18203492 163831428case 2:SELECT BILLINGNEW_GBILL.SEQ_TF_B_WRIOFFLOG.NEXTVAL WRITEOFF_ID,
A.LATN_ID || A.TRADE_SEQ CHARGE_ID,
TO_NUMBER(SUBSTR(TO_CHAR(A.CREATE_DATE, 'YYYYMM'), 5, 2), 99) PARTITION_ID,
A.BILL_CYCLE_SEQ CYCLE_ID,
'20' NET_TYPE_CODE,
NVL((SELECT A.BILL_CYCLE_SEQ || T.BILL_SEQ
FROM BILLINGNEW_GBILL.MID_GW_ACCOUNT_ID T
WHERE T.OLD_ACCOUNT_ID = A.ACCOUNT_ID),
-1) BILL_ID,
(CASE
WHEN A.SPEC_PREPAY_SEQ IS NULL OR A.SPEC_PREPAY_SEQ = 0 THEN
NVL((SELECT ACCT_BALANCE_ID
FROM BILLINGNEW_GBILL.MID_GW_ACCT_BALANCE_ID
WHERE OLD_ACCOUNT_ID = A.ACCOUNT_ID),
-1)
ELSE
TO_NUMBER(A.LATN_ID || A.SPEC_PREPAY_SEQ)
END) ACCT_BALANCE_ID,
0 IMP_FEE,
B.TOTAL_PAID FEE,
B.TOTAL_PAID OLD_BALANCE,
0 NEW_BALANCE,
0 LATE_FEE,
0 LATE_BALANCE
FROM BILLING_GBILL.T_PAYMENT_DISTRIBUTION A,
BILLING_GBILL.T_BILL_BALANCE_DETAIL_201108 B
WHERE A.LATN_ID = B.LATN_ID
AND A.DISTRIBUTION_TYPE NOT IN ('04', '05')
AND A.IS_PART_PAY = 0
AND A.BILL_CYCLE_SEQ = 201108
AND A.STATUS IN ('0', '1')
AND B.PAYMENT_DIST_SEQ = A.PAYMENT_DIST_SEQ执行计划如下
SELECT STATEMENT, GOAL = ALL_ROWS 151311 3846819 457771461
TABLE ACCESS BY INDEX ROWID BILLINGNEW_GBILL MID_GW_ACCOUNT_ID 4 1 18
INDEX RANGE SCAN BILLING_GBILL I_MID_GW_ACCOUNT_ID 3 1
TABLE ACCESS BY INDEX ROWID BILLINGNEW_GBILL MID_GW_ACCT_BALANCE_ID 4 1 17
INDEX RANGE SCAN BILLINGNEW_GBILL INDEX_MID_GW_ACCT_BAL_ID 3 1
SEQUENCE BILLINGNEW_GBILL SEQ_TF_B_WRIOFFLOG
HASH JOIN 151311 3846819 457771461
TABLE ACCESS FULL BILLING_GBILL T_PAYMENT_DISTRIBUTION 10387 1676255 179359285
TABLE ACCESS FULL BILLING_GBILL T_BILL_BALANCE_DETAIL_201108 110644 18203492 218441904
可以看到,我只是在第二个sql中添加了2个字段,为何连接方式跟执行代价差别这么大?
在case1中,返回的字段里头,没有B表的字段,那么,在B表上的join字段建立了索引的,该索引将会被用到。
而在case2中,返回字段有B表字段,则,该索引将不会被用到。该种情况见测试1.测试过程:
1.建表:
create t1 as select rownum id,a.* from all_objects a;
create t2 as select rownum id,a.* from all_objects a;
2.
create index idx_t1_object_id on t1(object_id);3查询分析过程
3.1
select a.object_id from T1 a,T2 b where a.object_id=b.object_id;
--使用T1索引
3.2
select a.owner,a.object_id from T1 a,T2 b where a.object_id=b.object_id;
--未使用T1索引
--3.1和3.2对比可知,select返回的,如果只有索引字段,那么该索引将会被用到
3.3
--在select中有索引字段时
select b.owner,a.object_id from T1 a,T2 b where a.object_id=b.object_id;
--使用T1索引
3.4
select a.owner,b.object_name from T1 a,T2 b where b.object_id=a.object_id;
--未用T1索引
3.5
--在where中加入索引字段等式值
select a.owner,b.object_name from T1 a,T2 b where a.object_id=b.object_id and a.object_id='1759';
--使用T1索引解决办法:
1.在where条件中添加B.TOTAL_PAID的等式。如果Total_PAID为数字型,可以添加“and total_paid>=0”类似的条件,但是不可以使用(!=,is not null,is null,<>),该等式对查询的结果应该不能产生影响。2.所建的索引都为连接的字段(PAYMENT_DIST_SEQ 和 LATN_ID),根据查询计划的结果,可以在这两个字段上添加约束条件。(如果可以的话)
不知道你会不会用。如果提示没写正确ORACLE是不会使用的