SQL> desc df_data_stream_desc
 Name                                                                                                              Null?    Type
 ----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
 DESC_ID                                                                                                           NOT NULL NUMBER(32)
 VERSION_DETAIL_ID                                                                                                          NUMBER(20)
 DATA_STREAM_ID                                                                                                             VARCHAR2(32)
 DATA_STREAM_UNIT_ID                                                                                                        NUMBER(20)
 UNIFIED_ID                                                                                                                 VARCHAR2(32)
 REMARK                                                                                                                     VARCHAR2(512)
 CREATE_TIME                                                                                                                DATE
 UPDATE_TIME                                                                                                                DATE
 CREATE_PERSON                                                                                                              VARCHAR2(128)
 UNIFIED_UNIT_ID                                                                                                            NUMBER(20)

SQL> select index_name,column_name from user_ind_columns where table_name = 'DF_DATA_STREAM_DESC';

INDEX_NAME                     COLUMN_NAME
------------------------------ --------------------
PK_DF_DATA_STREAM_DESC         DESC_ID
IND_DF_DATA_STREAM_DESC        VERSION_DETAIL_ID
I_STREAM_VERSION_DETAIL_ID     VERSION_DETAIL_ID
I_STREAM_VERSION_DETAIL_ID     DATA_STREAM_ID

SQL>

SQL> select * from table(sys.dbms_xplan.display_cursor('7m9w85hj1ukqg'));
SQL_ID  7m9w85hj1ukqg, child number 0
-------------------------------------
select t.* from df_data_stream_desc t where t.version_detail_id = :1
and t.unified_id is not null

Plan hash value: 2050773398

----------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                            |       |       |   731 (100)|          |
|*  1 |  TABLE ACCESS BY INDEX ROWID| DF_DATA_STREAM_DESC        |   757 | 36336 |   731   (0)| 00:00:09 |
|*  2 |   INDEX RANGE SCAN          | I_STREAM_VERSION_DETAIL_ID | 12059 |       |    53   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("T"."UNIFIED_ID" IS NOT NULL)
   2 - access("T"."VERSION_DETAIL_ID"=:1)


21 rows selected.


分析：
    该表数据约300w，建有组合索引 I_STREAM_VERSION_DETAIL_ID在字段version_detail_id number(20)和data_stream_id varchar2(32)上，
两字段均无null值但有重复值,另外unified_id varchar2(32)有空值。



测试全表扫描成本：
SQL> explain plan for
  2    select /*+ full(t) */ t.* from df_data_stream_desc t where t.version_detail_id = 4765 and t.unified_id is not null;

Explained.

SQL> select * from table(sys.dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 838388587

-----------------------------------------------------------------------------------------
| Id  | Operation         | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                     |   562 | 24728 |  7673   (1)| 00:01:33 |
|*  1 |  TABLE ACCESS FULL| DF_DATA_STREAM_DESC |   562 | 24728 |  7673   (1)| 00:01:33 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("T"."UNIFIED_ID" IS NOT NULL AND "T"."VERSION_DETAIL_ID"=4765)

13 rows selected.


再单独重新建个索引：
create index ind_df_data_stream_desc on df_data_stream_desc(VERSION_DETAIL_ID);
analyze table df_data_stream_desc compute statistics;
analyze table df_data_stream_desc compute statistics for all indexes;

再测试：
SQL> explain plan for select t.* from df_data_stream_desc t where t.version_detail_id = 4765 and t.unified_id is not null;

Explained.

SQL> select * from table(sys.dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 335983085

-------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                         |   562 | 24728 |   103   (0)| 00:00:02 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| DF_DATA_STREAM_DESC     |   562 | 24728 |   103   (0)| 00:00:02 |
|*  2 |   INDEX RANGE SCAN          | IND_DF_DATA_STREAM_DESC |  8864 |       |    22   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("T"."UNIFIED_ID" IS NOT NULL)
   2 - access("T"."VERSION_DETAIL_ID"=4765)

15 rows selected.

SQL>