请教高手关于Oracle9i中手工指定索引无法生效的问题

[babystudio]

我在select中指定了索引，Oracle却不承认，请问是什么原因？

select /*+index(AALUSS IAALUSS2*/ * from aaluss where
c_agencyno = '123'
and c_netname = '456'
and c_cityno = '456'

分析结果
Optimizer goal Choose
SELECT STATEMENT, GOAL = CHOOSE
TABLE ACCESS BY INDEX ROWID GYRX AALUSS
INDEX RANGE SCAN GYRX IAALUSS1
我的数据结构
-- Create table
create table AALUSS
(
C_AGENCYNO CHAR(3),
C_NETNO VARCHAR2(9),
C_NETNAME VARCHAR2(36),
C_CITYNO VARCHAR2(4),
C_NETADDRESS VARCHAR2(200),
C_NETZIPCODE VARCHAR2(9),
C_NETCONTACT VARCHAR2(18),
C_NETPHONE VARCHAR2(36),
C_NETFAXNO VARCHAR2(36),
D_NETREGDATE DATE,
C_NETSTATUS CHAR(1),
C_TANO CHAR(2)
);
-- Create indexes
create index IAALUSS1 on AALUSS (C_NETNAME,C_CITYNO);
create index IAALUSS2 on AALUSS (C_AGENCYNO,C_NETNO,C_NETNAME,C_CITYNO);

[chanet]

C:\>sqlplus /nolog

SQL*Plus: Release 9.2.0.1.0 - Production on 星期四 6月 8 00:00:40 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> conn chanet/chanet@oradb
已连接。
SQL> create table AALUSS
2 (
3 C_AGENCYNO CHAR(3),
4 C_NETNO VARCHAR2(9),
5 C_NETNAME VARCHAR2(36),
6 C_CITYNO VARCHAR2(4),
7 C_NETADDRESS VARCHAR2(200),
8 C_NETZIPCODE VARCHAR2(9),
9 C_NETCONTACT VARCHAR2(18),
10 C_NETPHONE VARCHAR2(36),
11 C_NETFAXNO VARCHAR2(36),
12 D_NETREGDATE DATE,
13 C_NETSTATUS CHAR(1),
14 C_TANO CHAR(2)
15 );

表已创建。

SQL> create index IAALUSS1 on AALUSS (C_NETNAME,C_CITYNO);

索引已创建。

SQL> create index IAALUSS2 on AALUSS (C_AGENCYNO,C_NETNO,C_NETNAME,C_CITYNO);

索引已创建。

SQL> set autotrace traceonly explain

SQL> select /*+index(AALUSS IAALUSS2)*/ * from aaluss where
2 c_agencyno = '123'
3 and c_netname = '456'
4 and c_cityno = '456'
5 /

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=210)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'AALUSS' (Cost=1 Card=1 Bytes=210)
2 1 INDEX (RANGE SCAN) OF 'IAALUSS2' (NON-UNIQUE) (Cost=2 Card=1)

[chanet]

原因是.....

select /*+index(AALUSS IAALUSS2*/ * from aaluss where <- you
select /*+index(AALUSS IAALUSS2)*/ * from aaluss where <- me

看到没? 少了一个 ) 号~ 呵呵