Dear Tom,
I have problem with query on table have function base index.
create index :
create index customer_idx_idno on Customer (lower(id_no)) ; --- id_no varchar2(40)
Query 1: execute time 0.031s but cost 5,149, 1 row return
select * from customer where LOWER (id_no) = '46192145'
--------------------------------------------------------------------------------
Plan
SELECT STATEMENT ALL_ROWSCost: 5,149 Bytes: 2,440,656 Cardinality: 11,964 Partition #: 0
2 TABLE ACCESS BY INDEX ROWID CM_POS.CUSTOMER Cost: 5,149 Bytes: 2,440,656 Cardinality: 11,964 Partition #: 0
1 INDEX RANGE SCAN CM_POS.CUST_ID_NO_IDX Cost: 3 Bytes: 0 Cardinality: 5,171 Partition #: 0
Query 2: execute time 0.484s cost 9,821, result = 0
-- there are index on : sub.contract_id, con.contract_id(pk), con.cust_id, cust.cust_id (pk), sub.sub_id(pk), stock.sub_id
SELECT COUNT ( * )
FROM sub_mb sub
INNER JOIN
contract con
ON sub.contract_id = con.contract_id AND con.status IN (1, 2, 9)
INNER JOIN
customer cust
ON con.cust_id = cust.cust_id AND LOWER (cust.id_no) = '46192145'
WHERE sub.status IN (1, 2, 9)
AND EXISTS (SELECT 1
FROM sub_stock_model_rel stock
WHERE sub.sub_id = stock.sub_id AND stock.status = 1)
--------------------------------------------------------------------------------
Plan
SELECT STATEMENT ALL_ROWSCost: 9,256 Bytes: 127 Cardinality: 1 Partition #: 0
17 SORT AGGREGATE Cost: 0 Bytes: 127 Cardinality: 1 Partition #: 0
16 HASH JOIN SEMI Cost: 9,256 Bytes: 1,561,846 Cardinality: 12,298 Partition #: 0
14 HASH JOIN Cost: 8,732 Bytes: 1,461,076 Cardinality: 12,382 Partition #: 0
12 HASH JOIN Cost: 6,613 Bytes: 1,272,874 Cardinality: 12,358 Partition #: 0
4 VIEW CM_POS.index$_join$_004 Cost: 2,827 Bytes: 1,052,832 Cardinality: 11,964 Partition #: 0
3 HASH JOIN Cost: 0 Bytes: 0 Cardinality: 0 Partition #: 0
1 INDEX RANGE SCAN CM_POS.CUST_ID_NO_IDX Cost: 3 Bytes: 1,052,832 Cardinality: 11,964 Partition #: 0
2 INDEX FAST FULL SCAN CM_POS.CUSTOMER_PK Cost: 3,526 Bytes: 1,052,832 Cardinality: 11,964 Partition #: 0
11 VIEW CM_POS.index$_join$_002 Cost: 3,786 Bytes: 2,717,070 Cardinality: 181,138 Partition #: 0
10 HASH JOIN Cost: 0 Bytes: 0 Cardinality: 0 Partition #: 0
8 HASH JOIN Cost: 0 Bytes: 0 Cardinality: 0 Partition #: 0
6 INLIST ITERATOR Cost: 0 Bytes: 0 Cardinality: 0 Partition #: 0
5 INDEX RANGE SCAN CM_POS.IX68_CONTRACT Cost: 493 Bytes: 2,717,070 Cardinality: 181,138 Partition #: 0
7 INDEX FAST FULL SCAN CM_POS.IX67_CONTRACT Cost: 917 Bytes: 2,717,070 Cardinality: 181,138 Partition #: 0
9 INDEX FAST FULL SCAN CM_POS.PK_CONTRACT Cost: 882 Bytes: 2,717,070 Cardinality: 181,138 Partition #: 0
13 TABLE ACCESS FULL CM_POS.SUB_MB Cost: 2,118 Bytes: 2,676,195 Cardinality: 178,413 Partition #: 0
15 TABLE ACCESS FULL CM_POS.SUB_STOCK_MODEL_REL Cost: 523 Bytes: 2,021,832 Cardinality: 224,648 Partition #: 0
Please tell me why query with function base index is to slow what is problem in my query(query 2), how to optimize it. I waiting for your anwser!
Just looking at the simpler query, you say it returns one row, but the optimizer thinks it is going to return over 5000 rows. (Hence the similar cost figure)
That should be your first point of investigation, because it suggests the statistics are either incorrect, out of date, missing or there is insufficient statistics to make a good judgement on the cardinality of that expression.
Use USER_TAB_COLS to check the statistics for hidden columns (which is basically what a function based index column will be). It may well be the case that you might need to create a histogram on this column. Then repeat your tests to see if the cardinality estimates are closer to reality.
Without a test case script that we can run here to see for ourselves (check the question guidelines), then we really cant say for sure.