SQL> SELECT COUNT(*) FROM T WHERE CONTAINS(NAME, 'TEST') > 0
2 AND STATUS = 'VALID'
3 AND TYPE = 'TABLE';

COUNT(*)
----------
49

　　执行计划

----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=24 Card=1 Bytes=53)
1 0 SORT (AGGREGATE)
2 1 BITMAP CONVERSION (COUNT)
3 2 BITMAP AND
4 3 BITMAP INDEX (SINGLE VALUE) OF 'IND_B_T_TYPE'
5 3 BITMAP INDEX (SINGLE VALUE) OF 'IND_B_T_STATUS'
6 3 BITMAP CONVERSION (FROM ROWIDS)
7 6 SORT (ORDER BY)
8 7 DOMAIN INDEX OF 'IND_T_NAME' (Cost=19)

　　统计信息

----------------------------------------------------------
79 recursive calls
0 db block gets
294 consistent gets
0 physical reads
0 redo size
303 bytes sent via SQL*Net to client
364 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed

　　上面没有改变查询的值，而是将‘*’换成了‘COUNT(*)’，那么Oracle会尝试将域索引转化为BITMAP索引，然后对所有的BITMAP索引进行AND操作，来避免表的访问。

　　上面是添加了直方图后，全文索引与BITMAP索引配合使用情况。对于BTREE索引来说，效果是类似的。

SQL> SELECT * FROM T WHERE CONTAINS(NAME, 'TEST') > 0 AND CREATED >= TO_DATE('2006-1-1');

ID NAME CREATED TYPE STATUS
---------- ------------------------------ ---------- ------------------ -------
30128 NDMAIN TEST 2006-02-23 INDEX VALID
31255 TEST ARTICLES_TAB 2006-11-09 TABLE VALID
.
.
.
31309 TEST TT2 2006-08-02 TABLE VALID

　　已选择56行。

　　执行计划

----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=27 Card=1 Bytes=56)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=27 Card=1 Bytes=56)
2 1 BITMAP CONVERSION (TO ROWIDS)
3 2 BITMAP AND
4 3 BITMAP CONVERSION (FROM ROWIDS)
5 4 SORT (ORDER BY)
6 5 INDEX (RANGE SCAN) OF 'IND_T_CREATED' (NON-UNIQUE) (Cost=2)
7 3 BITMAP CONVERSION (FROM ROWIDS)
8 7 SORT (ORDER BY)
9 8 DOMAIN INDEX OF 'IND_T_NAME' (Cost=19)

　　统计信息

----------------------------------------------------------
19 recursive calls
0 db block gets
56 consistent gets
0 physical reads
0 redo size
2977 bytes sent via SQL*Net to client
397 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
56 rows processed

SQL> SELECT * FROM T WHERE CONTAINS(NAME, 'TEST') > 0 AND CREATED >= TO_DATE('2006-11-1');

ID NAME CREATED TYPE STATUS
---------- ------------------------------ ---------- ------------------ -------
31255 TEST ARTICLES_TAB 2006-11-09 TABLE VALID
.
.
.
31305 TEST T 2006-11-14 TABLE VALID

　　已选择49行。

　　执行计划

----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=24 Card=1 Bytes=56)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=24 Card=1 Bytes=56)
2 1 INDEX (RANGE SCAN) OF 'IND_T_CREATED' (NON-UNIQUE) (Cost=2 Card=70)

　　统计信息

----------------------------------------------------------
59 recursive calls
0 db block gets
119 consistent gets
0 physical reads
0 redo size
2675 bytes sent via SQL*Net to client
397 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
49 rows processed

SQL> SELECT * FROM T WHERE CONTAINS(NAME, 'TEST') > 0 AND CREATED >= TO_DATE('2005-1-1');

ID NAME CREATED TYPE STATUS
---------- ------------------------------ ---------- ------------------ -------
31309 TEST TT2 2006-08-02 TABLE VALID
31308 TEST TT 2006-08-02 TABLE VALID
.
.
.
6720 SYS DBMS_JAVA_TEST 2005-09-21 PACKAGE VALID

　　已选择118行。

　　执行计划

----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=44 Card=118 Bytes=6608)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=44 Card=118 Bytes=6608)
2 1 DOMAIN INDEX OF 'IND_T_NAME' (Cost=19)

　　统计信息

----------------------------------------------------------
19 recursive calls
0 db block gets
67 consistent gets
0 physical reads
0 redo size
6124 bytes sent via SQL*Net to client
441 bytes received via SQL*Net from client
9 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
118 rows processed

　　从上面的一系列测试可以发现，如果在表和索引的统计信息的基础上，添加列的直方图，将更加有助于Oracle选择最有的执行计划。

　　根据测试，在大部分的情况下，只要收集了正确的统计信息，Oracle会字段的选择合适的执行计划，全文索引导致执行计划的改变并不会降低系统的性能。