SQL & PL/SQL

Hi all, happy new year,
i am facing this problem : i am using the following query but the optimizer does not use an existing index

-- query
select tbl1.*, (select sum(bg_amt) from tbaadm.bgm where bgm.cust_id = tbl1.core_cust_id group by cust_id) amt
from
( select orgkey, core_cust_id from tbaadm.accounts where orgkey = '000012345' ) tbl1

--Here is the explain plan (from TOAD)
SELECT STATEMENT CHOOSE Cost: 1 Bytes: 17 Cardinality: 1
2 SORT GROUP BY Cost: 161 Bytes: 1,296 Cardinality: 108
1 TABLE ACCESS FULL TBAADM.BG_MASTER_TABLE Cost: 114 Bytes: 1,38 Cardinality: 115
4 TABLE ACCESS BY INDEX ROWID CRMUSER.ACCOUNTS Cost: 1 Bytes: 17 Cardinality: 1
3 INDEX UNIQUE SCAN UNIQUE CRMUSER.IX_ACCOUNTS_ORGKEY Cost: 2 Cardinality: 1


I tried the following using a hint , but the explain plan remained the same
-- /*+ INDEX(tbaadm.bgm IDX_BGM_CUST_ID) */ for some reason the * is missing in the following select statement

select tbl1.*, (select /*+ INDEX(tbaadm.bgm IDX_BGM_CUST_ID) */ sum(bg_amt) from tbaadm.bgm where bgm.cust_id = tbl1.core_cust_id group by cust_id) amt
from
( select orgkey,core_cust_id from tbaadm.accounts where orgkey = '000012345' ) tbl1

--explain plan for indexed version
SELECT STATEMENT CHOOSE Cost: 1 Bytes: 17 Cardinality: 1
2 SORT GROUP BY Cost: 161 Bytes: 1,296 Cardinality: 108
1 TABLE ACCESS FULL TBAADM.BG_MASTER_TABLE Cost: 114 Bytes: 1,38 Cardinality: 115
4 TABLE ACCESS BY INDEX ROWID CRMUSER.ACCOUNTS Cost: 1 Bytes: 17 Cardinality: 1
3 INDEX UNIQUE SCAN UNIQUE CRMUSER.IX_ACCOUNTS_ORGKEY Cost: 2 Cardinality: 1


If I use the specific orgkey in the subquery then the plan uses the index right away (or course this is not what i need, i just tried to see if the index works) :

select tbl1.*, (select sum(bg_amt) from tbaadm.bgm where bgm.cust_id = '000012345' group by cust_id) amt
from
( select orgkey,core_cust_id from tbaadm.accounts where orgkey = '000012345' ) tbl1

--explain plan for 3rd attemt
SELECT STATEMENT CHOOSE Cost: 1 Bytes: 17 Cardinality: 1
3 SORT GROUP BY NOSORT Cost: 2 Bytes: 180 Cardinality: 15
2 TABLE ACCESS BY INDEX ROWID TBAADM.BG_MASTER_TABLE Cost: 2 Bytes: 180 Cardinality: 15
1 INDEX RANGE SCAN NON-UNIQUE TBAADM.IDX_BGM_CUST_ID Cost: 1 Cardinality: 15
5 TABLE ACCESS BY INDEX ROWID CRMUSER.ACCOUNTS Cost: 1 Bytes: 17 Cardinality: 1
4 INDEX UNIQUE SCAN UNIQUE CRMUSER.IX_ACCOUNTS_ORGKEY Cost: 2 Cardinality: 1

Any ides would be greatly appreciated :)

Thanks in advance
Theodore

Edited by: juststarter on Jan 18, 2010 10:50 AM

Edited by: juststarter on Jan 18, 2010 10:51 AM

Edited by: juststarter on Jan 18, 2010 10:52 AM