如何使用索引进行查询(索引是有条件的)

Question

表格建立如下:
--Create Table TARIFF for type A
CREATE TABLE TARIFF_A
(
AutoID NUMBER(19,0) Not Null,
OnRampID Number(10,0) NOT NULL,
OffRampID Number(10,0) NOT NULL,
Toll Number(10,2) NOT NULL,
FailFlag Char(1) Default 0 Not Null,
REMARK varchar2(16)
);--Create Sequence for TARIFF_B
CREATE SEQUENCE SEQ_TARIFF_A
    MINVALUE 3100000000000000001
    MAXVALUE 3199999999999999999
    START WITH 3100000000000000001
    INCREMENT BY 1
    NOCACHE;
    
--Create Unique Index
CREATE UNIQUE INDEX UIDX_TARIFF_A
       ON TARIFF_A(DECODE(FailFlag,'0',OnRampID),
                   DECODE(FailFlag,'0',OffRampID));
       
       
--Create Trigger For TARIFF_A
CREATE OR REPLACE TRIGGER TARIFF_A_before_insert
BEFORE INSERT
    ON TARIFF_A
    FOR EACH ROWDECLARE
    v_AutoID NUMBER(19,0);BEGIN    SELECT SEQ_TARIFF_A.nextval INTO v_AutoID
    FROM dual;    :new.AutoID := v_AutoID;END;我想使用索引进行查询,请问应该怎么写SQL
我试了用select toll from tariff_a where onrampid=1 and offrampid=7 and failflag=0或者select toll from tariff_a where  failflag=0 and onrampid=1 and offrampid=7都是Table Access Full方式的,没有用到索引

Answer 1

这个索引明显有问题：CREATE UNIQUE INDEX UIDX_TARIFF_A
       ON TARIFF_A(DECODE(FailFlag,'0',OnRampID),
                   DECODE(FailFlag,'0',OffRampID));

Answer 2

可以这样：CREATE UNIQUE INDEX UIDX_TARIFF_A 
       ON TARIFF_A(DECODE(FailFlag,'0',OnRampID, NULL), 
                   DECODE(FailFlag,'0',OffRampID, NULL),
                   DECODE(FailFlag,'0',FailFlag, NULL)
                  ); 

Answer 3

我是想保证FailFlag=0时OnrampID,OffRampID的唯一性,所以建立的这个索引

Answer 4

写成这样会有什么区别吗?
我按照这个改过后,explain plan仍然是全表搜索

Answer 5

加一个字段，让它走这个索引, 免得再过滤一次
表分析了吗？
tariff_a 表中有多少记录？
按你的条件取出来有多少记录？

Answer 6

156条取1,我只是想实现索引查询,因为还有些数据量大的表也要用这种index,这个表只是测试怎么实现.

Answer 7

我的oracle9i, 试了，觉得好奇怪。
只用索引，可以强制它：select /*+index(tariff_a, UIDX_TARIFF_A)*/ 1 from tariff_a where onrampid=1 and offrampid=7 ...

Answer 8

强制之后确实能保证使用了index,但是Cost好高啊竟然有826,而不是用强制,Cost为2.
晕了,Cost是越低越好吧

Answer 9

COST  
The cost of the operation as estimated by the optimizer's cost-based approach. For statements that use the rule-based approach, this column is null. Cost is not determined for table access operations. The value of this column does not have any particular unit of measurement, it is merely a weighted value used to compare costs of execution plans.  看来Cost不代表啥,不知道为什么必须强制才能使用Index.
另外我按照--Create Unique Index
CREATE UNIQUE INDEX UIDX_TARIFF_A
       ON TARIFF_A(DECODE(FailFlag,'0',OnRampID),
                   DECODE(FailFlag,'0',OffRampID));
和加上failflag字段的效果是一样的,我试过了

Answer 10

有了这个索引之后
select count(1) from tariff_a 出来的结果都是错误的

Answer 11

cost不准确，看 consistent get.走那个索引 vs 不走索引，走索引的 consisteng get 会少一些。但是如果只创建一个正常的索引：
CREATE UNIQUE INDEX UIDX_TARIFF_A
       ON TARIFF_A(OnRampID ,OffRampID);则 consisteng get 会少很多

Answer 12

你试试看，这个结果对不对？我这里测试，oracle走了索引，统计的只有failflag=0的记录，=1的就没统计出来

Answer 13

不使用强制的话仍然不会走Index,返回的Cardinality=failflag为0的记录数,但Select语句执行的结果是对的

Answer 14

我的是
oracle release 9.2.0.1.0测试时 select count(1) from tariff_a 走了那个索引，结果给出的答案是错误的

Answer 15

你建的索引是函数索引，然后 查询的条件中又没有那样的条件，怎么用索引？
为了满足你的特殊的unique 需求，你可以使用你的索引,
不过为了查询，你可以再建立其他索引，比如
CREATE  INDEX UIDX_TARIFF_B
       ON TARIFF_A(OnRampID ,OffRampID);