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

[nancliu]

表格建立如下:

--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 ROW

DECLARE
    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方式的,没有用到索引

[codearts]

这个索引明显有问题：

CREATE UNIQUE INDEX UIDX_TARIFF_A
ON TARIFF_A(DECODE(FailFlag,'0',OnRampID),
DECODE(FailFlag,'0',OffRampID));

[codearts]

可以这样：

CREATE UNIQUE INDEX UIDX_TARIFF_A
ON TARIFF_A(DECODE(FailFlag,'0',OnRampID, NULL),
DECODE(FailFlag,'0',OffRampID, NULL),
DECODE(FailFlag,'0',FailFlag, NULL)
);

[nancliu]

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

[codearts]

记得分析表或索引

[nancliu]

[Quote=引用 2 楼 codearts 的回复:]
可以这样：

CREATE UNIQUE INDEX UIDX_TARIFF_A
ON TARIFF_A(DECODE(FailFlag,'0',OnRampID, NULL),
DECODE(FailFlag,'0',OffRampID, NULL),
DECODE(FailFlag,'0',FailFlag, NULL)
);

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

[codearts]

加一个字段，让它走这个索引, 免得再过滤一次


表分析了吗？
tariff_a 表中有多少记录？
按你的条件取出来有多少记录？

[nancliu]

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

[codearts]

[Quote=引用 7 楼 nancliu 的回复:]
156条取1,我只是想实现索引查询,因为还有些数据量大的表也要用这种index,这个表只是测试怎么实现.
[/Quote]

我的oracle9i, 试了，觉得好奇怪。
只用索引，可以强制它：

select /*+index(tariff_a, UIDX_TARIFF_A)*/ 1 from tariff_a where onrampid=1 and offrampid=7 ...

[nancliu]

[Quote=引用 8 楼 codearts 的回复:]
引用 7 楼 nancliu 的回复:
156条取1,我只是想实现索引查询,因为还有些数据量大的表也要用这种index,这个表只是测试怎么实现.


我的oracle9i, 试了，觉得好奇怪。
只用索引，可以强制它：

select /*+index(tariff_a, UIDX_TARIFF_A)*/ 1 from tariff_a where onrampid=1 and offrampid=7 ...
[/Quote]
强制之后确实能保证使用了index,但是Cost好高啊竟然有826,而不是用强制,Cost为2.
晕了,Cost是越低越好吧

[nancliu]

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字段的效果是一样的,我试过了

[codearts]

有了这个索引之后
select count(1) from tariff_a

出来的结果都是错误的

[codearts]

cost不准确，看 consistent get.

走那个索引 vs 不走索引，走索引的 consisteng get 会少一些。

但是如果只创建一个正常的索引：
CREATE UNIQUE INDEX UIDX_TARIFF_A
ON TARIFF_A(OnRampID ,OffRampID);

则 consisteng get 会少很多

[codearts]

[Quote=引用 11 楼 codearts 的回复:]
有了这个索引之后
select count(1) from tariff_a

出来的结果都是错误的
[/Quote]

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

[nancliu]

[Quote=引用 13 楼 codearts 的回复:]
引用 11 楼 codearts 的回复:
有了这个索引之后
select count(1) from tariff_a

出来的结果都是错误的


你试试看，这个结果对不对？我这里测试，oracle走了索引，统计的只有failflag=0的记录，=1的就没统计出来
[/Quote]
不使用强制的话仍然不会走Index,返回的Cardinality=failflag为0的记录数,但Select语句执行的结果是对的

[codearts]

我的是
oracle release 9.2.0.1.0

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

[codearts]

帮顶，望高手来解答

[nancliu]

等待高手指导

[kongkongye]

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

[nancliu]

[Quote=引用 18 楼 kongkongye 的回复:]
你建的索引是函数索引，然后 查询的条件中又没有那样的条件，怎么用索引？
为了满足你的特殊的unique 需求，你可以使用你的索引,
不过为了查询，你可以再建立其他索引，比如
CREATE INDEX UIDX_TARIFF_B
ON TARIFF_A(OnRampID ,OffRampID);
[/Quote]
那多一个索引插入数据时,性能损耗大吗?