单列索引列与单列索引列为首的组合索引列需要同时建在表上吗？

在生产库的环境中经常会看到有些开发建索引如下：
... idx_p_id_1 ON t1(p_id,bno);
... idx_p_id_2 on t1(p_id);
而其中p_id为选择率较高的索引列，如1/10，甚至有些直接就是主键索引； 
真的需要同时存在这两个索引吗？
我们试验测试下；

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


Session altered.

SQL> drop table t1 purge;

Table dropped.

--创建一胶
SQL> create table t1 as select * from dba_objects;

Table created.

将表列object_id的选择率控制在1/10，并且分散开来不存储在1块
QL> declare
  2  cursor c1 is 
  3  select object_id from t1 where object_id like '%1'   union all
  4  select object_id from t1 where object_id like '%2'   union all
  5  select object_id from t1 where object_id like '%3'   union all
  6  select object_id from t1 where object_id like '%4'   union all
  7  select object_id from t1 where object_id like '%5'   union all
  8  select object_id from t1 where object_id like '%6'   union all
  9  select object_id from t1 where object_id like '%7'   union all
 10  select object_id from t1 where object_id like '%8'   union all
 11  select object_id from t1 where object_id like '%9'   union all
 12  select object_id from t1 where object_id like '%0'   ;
 13  type t_c1 is table of c1%rowtype;
 14  v_c1 t_c1;
 15  c_nt number;
 16  n1 number;
 17  begin
 18  c_nt :=1;
 19  open c1;
 20  loop 
 21  n1:=seq01.nextval;
 22  fetch c1 bulk collect into v_c1 limit 10;
 23  forall idx_id in 1..v_c1.count
 24  update t1 set object_id =n1 where object_id=v_c1(idx_id).object_id;
 25  if mod(c_nt,2)=0 then
 26  commit;
 27  end if;
 28  c_nt:=c_nt+1;
 29  exit when c1%NOTFOUND;
 30  end loop;
 31  commit;
 32  close c1;
 33  end;
 34  /


PL/SQL procedure successfully completed.

SQL> SQL> 
SQL> 
SQL> select count(distinct object_id) from t1;  --选择率接近1/10

COUNT(DISTINCTOBJECT_ID)
------------------------
                    7522

SQL> exec dbms_stats.gather_table_stats('AIKI','T1',CASCADE=>TRUE);

PL/SQL procedure successfully completed.

SQL> select object_id from t1 where rownum<11;

 OBJECT_ID
----------
    221072
    221824
    222576
    223327
    224080
    224831
    225585
    218815
    219567
    220319

10 rows selected.

--行数据分开存储不会存储在物理相邻的块上，避免全表扫描成本过低
SQL> select object_id ,dbms_rowid.rowid_block_number(rowid) bno from t1 where object_id=221072;

 OBJECT_ID        BNO
---------- ----------
    221072        523
    221072        523
    221072        523
    221072        523
    221072        523
    221072        523
    221072        523
    221072       3308
    221072       3308
    221072       3308

10 rows selected.

--查看统计信息，上面已存在列OBJECT_ID的索引IDX_T1_ID
Please enter Name of Table Owner (Null = AIKI): 
Please enter Table Name to show Statistics for: T1

***********
Table Level
***********


Table                   Number                 Empty Average    Chain Average Global User           Sample Date
Name                   of Rows   Blocks       Blocks   Space    Count Row Len Stats  Stats            Size MM-DD-YYYY
--------------- -------------- -------- ------------ ------- -------- ------- ------ ------ -------------- ----------
T1                      75,212    1,126            0       0        0      97 YES    NO             75,212 06-02-2014

Column                    Column                       Distinct          Number     Number Global User           Sample Date
Name                      Details                        Values Density Buckets      Nulls Stats  Stats            Size MM-DD-YYYY
------------------------- ------------------------ ------------ ------- ------- ---------- ------ ------ -------------- ----------
OWNER                     VARCHAR2(30)                       30       0       1          0 YES    NO             75,212 06-02-2014
OBJECT_NAME               VARCHAR2(128)                  46,184       0       1          0 YES    NO             75,212 06-02-2014
SUBOBJECT_NAME            VARCHAR2(30)                      141       0       1     74,718 YES    NO                494 06-02-2014
OBJECT_ID                 NUMBER(22)                      7,522       0       1          0 YES    NO             75,212 06-02-2014
DATA_OBJECT_ID            NUMBER(22)                      9,512       0       1     65,650 YES    NO              9,562 06-02-2014
OBJECT_TYPE               VARCHAR2(19)                       45       0       1          0 YES    NO             75,212 06-02-2014
CREATED                   DATE                            1,172       0       1          0 YES    NO             75,212 06-02-2014
LAST_DDL_TIME             DATE                            1,276       0       1          0 YES    NO             75,212 06-02-2014
TIMESTAMP                 VARCHAR2(19)                    1,341       0       1          0 YES    NO             75,212 06-02-2014
STATUS                    VARCHAR2(7)                         1       1       1          0 YES    NO             75,212 06-02-2014
TEMPORARY                 VARCHAR2(1)                         2       1       1          0 YES    NO             75,212 06-02-2014
GENERATED                 VARCHAR2(1)                         2       1       1          0 YES    NO             75,212 06-02-2014
SECONDARY                 VARCHAR2(1)                         2       1       1          0 YES    NO             75,212 06-02-2014
NAMESPACE                 NUMBER(22)                         21       0       1          0 YES    NO             75,212 06-02-2014
EDITION_NAME              VARCHAR2(30)                        0       0       0     75,212 YES    NO                    06-02-2014

                              B                                        Average     Average
Index                      Tree Leaf       Distinct         Number Leaf Blocks Data Blocks      Cluster Global User           Sample Date
Name            Unique    Level Blks           Keys        of Rows     Per Key     Per Key       Factor Stats  Stats            Size MM-DD-YYYY
--------------- --------- ----- ---- -------------- -------------- ----------- ----------- ------------ ------ ------ -------------- ----------
IDX_T1_ID       NONUNIQUE     1  151          7,522         75,212           1           1       10,678 YES    NO             75,212 06-02-2014

Index           Column                     Col Column
Name            Name                       Pos Details
--------------- ------------------------- ---- ------------------------
IDX_T1_ID       OBJECT_ID                    1 NUMBER(22)


--另外再创建以此索引为首的组合索引，并分测试2索引的开销
SQL> create index idx_t1_id2 on t1(object_id,created);

Index created.

SQL> 
SQL> exec dbms_stats.gather_table_stats('AIKI','T1',CASCADE=>TRUE);

PL/SQL procedure successfully completed.

 select status from t1 where object_id=99;

Execution Plan
----------------------------------------------------------
Plan hash value: 190799060

-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |     1 |    11 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1        |     1 |    11 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_T1_ID |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

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

   2 - access("OBJECT_ID"=99)


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


 select /*+ index(t1,idx_t1_id2) */status from  t1 where object_id=1800;
 
 Execution Plan
----------------------------------------------------------
Plan hash value: 426355385

------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |     1 |    11 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1         |     1 |    11 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_T1_ID2 |     1 |       |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

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

   2 - access("OBJECT_ID"=1800)


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

同上面的测试中可看出两组索引的成本是相当的，也就是在生产环境中，可以将第1个索引去除; 
而不需要建2个功能类似的索引上去，增大DML的成本；