oracle 索引-1无效,[q][index]函数索引不生效的问题——经典问题

SQL> set time on

13:28:29 SQL> set timing on

13:28:31 SQL> set autotrace on

13:28:36 SQL> select * from v$version;

BANNER

----------------------------------------------------------------

Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production

PL/SQL Release 9.2.0.4.0 - Production

CORE    9.2.0.3.0       Production

TNS for HPUX: Version 9.2.0.4.0 - Production

NLSRTL Version 9.2.0.4.0 - Production

已用时间:  00: 00: 00.91

Execution Plan

----------------------------------------------------------

ERROR:

ORA-01039: 视图基本对象的权限不足

SP2-0612: 生成AUTOTRACE EXPLAIN报告时出错

Statistics

----------------------------------------------------------

128  recursive calls

0  db block gets

34  consistent gets

2  physical reads

0  redo size

762  bytes sent via SQL*Net to client

655  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

10  sorts (memory)

0  sorts (disk)

5  rows processed

13:28:45 SQL> select/*+parallel(test,16)*/ count(1) from test t

13:29:16   2  /

COUNT(1)

----------

6915520

已用时间:  00: 00: 04.85

Execution Plan

----------------------------------------------------------

0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1826 Card=1)

1    0   SORT (AGGREGATE)

2    1     INDEX (FAST FULL SCAN) OF 'IDX_TEST_F_DEAL' (NON-U

NIQUE) (Cost=1826 Card=6915520)

Statistics

----------------------------------------------------------

0  recursive calls

0  db block gets

30070  consistent gets

0  physical reads

0  redo size

491  bytes sent via SQL*Net to client

655  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

1  rows processed

13:29:22 SQL> select count(1) from test t

13:30:59   2  where to_char(t.deal_date,'yyyymmdd')='20051221';

COUNT(1)

----------

0

已用时间:  00: 01: 35.32

Execution Plan

----------------------------------------------------------

0      SELECT STATEMENT Optimizer=CHOOSE (Cost=7742 Card=1 Bytes=8)

1    0   SORT (AGGREGATE)

2    1     TABLE ACCESS (FULL) OF 'test' (Cost=7742 Card=6915

5 Bytes=553240)

Statistics

----------------------------------------------------------

0  recursive calls

0  db block gets

126433  consistent gets

126419  physical reads

0  redo size

487  bytes sent via SQL*Net to client

655  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

1  rows processed

13:34:54 SQL>  analyze table test delete statistics;

表已分析。

已用时间:  00: 00: 00.09

13:35:06 SQL> select count(1) from test t

13:35:19   2  where to_char(t.deal_date,'yyyymmdd')='20051221';

COUNT(1)

----------

0

已用时间:  00: 01: 35.70

Execution Plan

----------------------------------------------------------

0      SELECT STATEMENT Optimizer=CHOOSE

1    0   SORT (AGGREGATE)

2    1     TABLE ACCESS (FULL) OF 'test'

Statistics

----------------------------------------------------------

0  recursive calls

0  db block gets

126433  consistent gets

126418  physical reads

0  redo size

487  bytes sent via SQL*Net to client

655  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

1  rows processed

13:36:57 SQL>

13:37:27 SQL> analyze table test compute statistics for table for all indexes;

表已分析。

已用时间:  00: 03: 03.35

13:41:01 SQL> select count(1) from test t

13:41:30   2  where to_char(t.deal_date,'yyyymmdd')='20051221';

COUNT(1)

----------

0

已用时间:  00: 01: 25.50

Execution Plan

----------------------------------------------------------

0      SELECT STATEMENT Optimizer=CHOOSE (Cost=7742 Card=1 Bytes=9)

1    0   SORT (AGGREGATE)

2    1     TABLE ACCESS (FULL) OF 'test' (Cost=7742 Card=6915

5 Bytes=622395)

Statistics

----------------------------------------------------------

22  recursive calls

0  db block gets

126435  consistent gets

0  physical reads

0  redo size

487  bytes sent via SQL*Net to client

655  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

1  rows processed

13:48:42 SQL> show parameter query

NAME                                 TYPE

------------------------------------ --------------------------------

VALUE

------------------------------

query_rewrite_enabled                string

TRUE

query_rewrite_integrity              string

TRUSTED

奇怪，该用全表扫描，却利用了索引，当用索引，却用了权标扫描

？？？

2    1     INDEX (FAST FULL SCAN) OF 'IDX_TEST_F_DEAL' (NON-U[/COLOR] 13:37:27 SQL> analyze table test compute statistics for table for all indexes;[/COLOR]