Oracle 为什么只对一行来说TABLE访问BY索引行ID的成本高

亲爱的汤姆，
我对有函数基索引的表的查询有问题。
创建索引：

create index customer_idx_idno on Customer (lower(id_no)) ; --- id_no varchar2(40)

Query 1:执行时间0.031s ，但成本5,149,1行返回

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:执行时间0.484s开销9,821 ，结果= 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        

请告诉我为什么使用函数基索引查询会减慢我的查询（查询2 ）中的问题，如何优化它。我在等你的回答！

只看简单的查询，你说它返回一行，但是优化器认为它将返回5000多行。（因此，费用数字类似）

这应该是你调查的第一点，因为它表明统计是不正确的，过时的，缺失的，或没有足够的统计作出一个良好的判断，基数的表达。

使用UER_TAB_COLS检查隐藏列的统计信息（这基本上就是基于函数的索引列的形式）。很可能您需要在此列上创建一个直方图。然后重复你的测试，看看基数的估计是否更接近现实。

如果没有我们可以在这里运行的测试用例脚本来自己查看（请查看问题指南） ，那么我们真的不能确定。