【IT168 技术文档】测试环境仍然沿用上一篇文章介绍的。下面看一下这个查询语句：

SQL> SELECT * FROM T
2 WHERE CONTAINS(NAME, 'SYS') > 0
3 AND STATUS = 'INVALID';

ID NAME CREATED TYPE STATUS
---------- ------------------------------ ---------- ------------------ -------
5942 SYS ALL_REPPARAMETER_COLUMN 2005-09-21 VIEW INVALID
6319 SYS DBA_LOGSTDBY_LOG 2005-09-21 VIEW INVALID
.
.
.
10332 SYS _ALL_REPRESOLUTION 2005-09-21 VIEW INVALID

　　已选择19行。

　　执行计划

----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=28 Card=7103 Bytes=397768)
1 0 TABLE ACCESS (FULL) OF 'T' (Cost=28 Card=7103 Bytes=397768)

　　统计信息

----------------------------------------------------------
57 recursive calls
0 db block gets
373 consistent gets
0 physical reads
0 redo size
1471 bytes sent via SQL*Net to client
375 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
19 rows processed

　　由于包含SYS的记录太多，因此Oracle没有选择全文索引，但是Oracle为什么没有使用BITMAP索引呢，这主要是由于确实列的直方图分析。

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T', METHOD_OPT 
=> 'FOR ALL INDEXED COLUMNS SIZE 254')

　　PL/SQL 过程已成功完成。

SQL> SELECT * FROM T
2 WHERE CONTAINS(NAME, 'SYS') > 0
3 AND STATUS = 'INVALID';

ID NAME CREATED TYPE STATUS
---------- ------------------------------ ---------- ------------------ -------
5942 SYS ALL_REPPARAMETER_COLUMN 2005-09-21 VIEW INVALID
6319 SYS DBA_LOGSTDBY_LOG 2005-09-21 VIEW INVALID
.
.
.
10332 SYS _ALL_REPRESOLUTION 2005-09-21 VIEW INVALID

　　已选择19行。

　　执行计划

----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=10 Card=19 Bytes=1064)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=10 Card=19 Bytes=1064)
2 1 BITMAP CONVERSION (TO ROWIDS)
3 2 BITMAP INDEX (SINGLE VALUE) OF 'IND_B_T_STATUS'

　　统计信息

----------------------------------------------------------
57 recursive calls
0 db block gets
117 consistent gets
0 physical reads
0 redo size
1471 bytes sent via SQL*Net to client
375 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
19 rows processed

　　收集了列的直方图信息，Oracle采用了BITMAP索引，使得查询效率提到。