--原查询sql如下：
select t.CUST_ID,
       t.CUST_NAME,
       t.CUST_SEX_DM,
       t.GRADE_NO,
       t.ID_CARD,
       t.CUST_AGE,
       t.TEL_WORK,
       t.TEL_HOME,
       t.TEL_MOBILE,
       t.CUST_EMAIL,
       t.HOME_ADDRESS,
       t.ADDRESS_WORK,
       t.POST_WORK,
       t.POST_HOME,
       t.CUST_TYPE,
       t.PROVINCE_DM,
       t.CITY_DM,
       t.DIPLOMA,
       t.EMOLUMENT,
       t.METIER,
       t.INTEREST,
       a.comname as COMPANYID
  from pcc_cust_infonew t
  left join PCC_CUST_CONTRACTCOMPANY a on t.COMPANYID = a.companyid
 where t.tel_work like '%138'
    or t.tel_home like '%138'
    or t.tel_mobile like '%138'
    or t.COMM_TEL like '%138'
 order by t.CUST_ID;
--原索引
create index CUST_INFONEW_TEL_MOBILE_IDX on PCC_CUST_INFONEW (TEL_MOBILE)
create index PCC_CUST_INFONEW_COMTEL on PCC_CUST_INFONEW (COMM_TEL)
create index PCC_CUST_INFONEW_TELH on PCC_CUST_INFONEW (TEL_HOME)
create index PCC_CUST_INFONEW_TELW on PCC_CUST_INFONEW (TEL_WORK)
--原执行设计
1865 rows selected.

Elapsed: 00:00:03.96

Execution Plan
----------------------------------------------------------
Plan hash value: 3605303961

---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                             |   380K|    44M|       | 18103   (3)| 00:03:38 |
|   1 |  SORT ORDER BY                |                             |   380K|    44M|    97M| 18103   (3)| 00:03:38 |
|   2 |   NESTED LOOPS OUTER          |                             |   380K|    44M|       |  7482   (5)| 00:01:30 |
|*  3 |    TABLE ACCESS FULL          | PCC_CUST_INFONEW            |   380K|    38M|       |  7482   (5)| 00:01:30 |
|   4 |    TABLE ACCESS BY INDEX ROWID| PCC_CUST_CONTRACTCOMPANY    |     1 |    18 |       |     0   (0)| 00:00:01 |
|*  5 |     INDEX UNIQUE SCAN         | PK_PCC_CUST_CONTRACTCOMPANY |     1 |       |       |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------------

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

   3 - filter("T"."TEL_WORK" LIKE '%138' OR "T"."TEL_HOME" LIKE '%138' OR "T"."TEL_MOBILE" LIKE '%138' OR
              "T"."COMM_TEL" LIKE '%138')
   5 - access("T"."COMPANYID"="A"."COMPANYID"(+))


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      32560  consistent gets
      32555  physical reads
          0  redo size
     118896  bytes sent via SQL*Net to client
       1749  bytes received via SQL*Net from client
        126  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
       1865  rows processed


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

优化如下
--1.增加reverse索引
create index tel_work_reverse on pcc_cust_infonew(reverse(tel_work));
create index tel_home_reverse on pcc_cust_infonew(reverse(tel_home));
create index tel_mobile_reverse on pcc_cust_infonew(reverse(tel_mobile));
create index COMM_TEL_reverse on pcc_cust_infonew(reverse(COMM_TEL));
exec DBMS_STATS.gather_table_stats('POST_KF','PCC_CUST_INFONEW');
--2.调整sql
select t.CUST_ID,
       t.CUST_NAME,
       t.CUST_SEX_DM,
       t.GRADE_NO,
       t.ID_CARD,
       t.CUST_AGE,
       t.TEL_WORK,
       t.TEL_HOME,
       t.TEL_MOBILE,
       t.CUST_EMAIL,
       t.HOME_ADDRESS,
       t.ADDRESS_WORK,
       t.POST_WORK,
       t.POST_HOME,
       t.CUST_TYPE,
       t.PROVINCE_DM,
       t.CITY_DM,
       t.DIPLOMA,
       t.EMOLUMENT,
       t.METIER,
       t.INTEREST,
       a.comname as COMPANYID
  from pcc_cust_infonew t
  left join PCC_CUST_CONTRACTCOMPANY a on t.COMPANYID = a.companyid
 where reverse(t.tel_work) like reverse('%138')
    or reverse(t.tel_home) like reverse('%138')
    or reverse(t.tel_mobile) like reverse('%138')
    or reverse(t.COMM_TEL) like reverse('%138')
 order by t.CUST_ID
--调整后执行计划如下：
1865 rows selected.

Elapsed: 00:00:00.09

Execution Plan
----------------------------------------------------------
Plan hash value: 3817740199

--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                             |  3322 |   454K|       |   304   (2)| 00:00:04 |
|   1 |  SORT ORDER BY                     |                             |  3322 |   454K|   984K|   304   (2)| 00:00:04 |
|   2 |   NESTED LOOPS OUTER               |                             |  3322 |   454K|       |   198   (3)| 00:00:03 |
|   3 |    TABLE ACCESS BY INDEX ROWID     | PCC_CUST_INFONEW            |  3322 |   395K|       |   198   (3)| 00:00:03 |
|   4 |     BITMAP CONVERSION TO ROWIDS    |                             |       |       |       |            |          |
|   5 |      BITMAP OR                     |                             |       |       |       |            |          |
|   6 |       BITMAP CONVERSION FROM ROWIDS|                             |       |       |       |            |          |
|   7 |        SORT ORDER BY               |                             |       |       |       |            |          |
|*  8 |         INDEX RANGE SCAN           | TEL_HOME_REVERSE            |       |       |       |     4   (0)| 00:00:01 |
|   9 |       BITMAP CONVERSION FROM ROWIDS|                             |       |       |       |            |          |
|  10 |        SORT ORDER BY               |                             |       |       |       |            |          |
|* 11 |         INDEX RANGE SCAN           | TEL_WORK_REVERSE            |       |       |       |     3   (0)| 00:00:01 |
|  12 |       BITMAP CONVERSION FROM ROWIDS|                             |       |       |       |            |          |
|  13 |        SORT ORDER BY               |                             |       |       |       |            |          |
|* 14 |         INDEX RANGE SCAN           | TEL_MOBILE_REVERSE          |       |       |       |     3   (0)| 00:00:01 |
|  15 |       BITMAP CONVERSION FROM ROWIDS|                             |       |       |       |            |          |
|  16 |        SORT ORDER BY               |                             |       |       |       |            |          |
|* 17 |         INDEX RANGE SCAN           | COMM_TEL_REVERSE            |       |       |       |     3   (0)| 00:00:01 |
|  18 |    TABLE ACCESS BY INDEX ROWID     | PCC_CUST_CONTRACTCOMPANY    |     1 |    18 |       |     0   (0)| 00:00:01 |
|* 19 |     INDEX UNIQUE SCAN              | PK_PCC_CUST_CONTRACTCOMPANY |     1 |       |       |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------------

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

   8 - access(REVERSE("TEL_HOME") LIKE '831%')
       filter(REVERSE("TEL_HOME") LIKE '831%' AND REVERSE("TEL_HOME") LIKE '831%')
  11 - access(REVERSE("TEL_WORK") LIKE '831%')
       filter(REVERSE("TEL_WORK") LIKE '831%' AND REVERSE("TEL_WORK") LIKE '831%')
  14 - access(REVERSE("TEL_MOBILE") LIKE '831%')
       filter(REVERSE("TEL_MOBILE") LIKE '831%' AND REVERSE("TEL_MOBILE") LIKE '831%')
  17 - access(REVERSE("COMM_TEL") LIKE '831%')
       filter(REVERSE("COMM_TEL") LIKE '831%' AND REVERSE("COMM_TEL") LIKE '831%')
  19 - access("T"."COMPANYID"="A"."COMPANYID"(+))


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1751  consistent gets
          0  physical reads
          0  redo size
     118896  bytes sent via SQL*Net to client
       1749  bytes received via SQL*Net from client
        126  SQL*Net roundtrips to/from client
          5  sorts (memory)
          0  sorts (disk)
       1865  rows processed