SQL tune for index full scan!!!!

[claire_wu]

下面是出现full table scan 的SQL
1> SQL
SELECT /*+index(CD)*/
CD.subscr_no, CD.subscr_no_resets, CD.ACCOUNT_NO, CD.balance_id, CD.payment_mode, CD.balance_amount
FROM CDR_BILLED CB, BILL_INVOICE BI, CDR_DATA CD
where CB.trans_dt >= to_date('01/2009', 'MM/YYYY')
AND CB.trans_dt < add_months(to_date('01/2009', 'MM/YYYY'), 1)
AND BI.bill_ref_no = cb.bill_ref_no
and bi.bill_ref_resets = cb.bill_ref_resets
AND CB.msg_id = CD.msg_id
and CB.msg_id2 = cd.msg_id2
and CB.split_row_num = cd.split_row_num

2> index
Index on CDR_BILLED(bill_ref_no,bill_ref_resets)
index on BILL_INVOICE(bill_ref_no,bill_ref_resets)
primary key on CDR_DATA(msg_id, msg_id2, split_row_num)

4> Explain plan result
SELECT STATEMENT, GOAL = CHOOSE Cost=25 Cardinality=86 Bytes=5332
HASH JOIN Cost=25 Cardinality=86 Bytes=5332
NESTED LOOPS Cost=4 Cardinality=86 Bytes=2924
VIEW Object owner=CAMB30 Object name=index$_join$_001 Cost=3 Cardinality=92 Bytes=2484
HASH JOIN
INDEX FAST FULL SCAN Object owner=CAMB30 Object name=CDR_BILLED_PK Cost=1 Cardinality=92 Bytes=2484
INDEX FAST FULL SCAN Object owner=CAMB30 Object name=CDR_BILLED_XCB_BILL_REF_TRANS Cost=1 Cardinality=92 Bytes=2484
INDEX UNIQUE SCAN Object owner=CAMB30 Object name=BILL_INVOICE_XSUM_BILL_REF_NO Cost=1 Cardinality=1 Bytes=7
TABLE ACCESS BY INDEX ROWID Object owner=CAMB30 Object name=CDR_DATA Cost=20 Cardinality=611 Bytes=17108
INDEX FULL SCAN Object owner=CAMB30 Object name=MSG_ID_PK Cost=1 Cardinality=611


5> 问题：
a) 所有的index都用到了，为什么还会在表CDR_DATA上有index full scan呢？
b) 为什么当加上'cb.bill_ref_no = 10 and cb.bill_ref_resets = 1'，就可以避免index full scan呢？
c) 因为CDR_DATA比CDR_BILLED字段多很多，有没有办法能使CDR_BILLED上full table scan来避免CDR_DATA上的INDEX FULL SCAN?

Thanks in advance.

[hyee]

原因是CB表和CD表作了HASH JOIN,这意味着CD表只能做全表扫描或全索引扫描。
你可以试着把hint改为 /*+ordered use_nl(cb bi cd)*/

[claire_wu]

/*+ordered use_nl(cb bi cd)*/ 可以解决问题，万分感谢！