oracle数据库like语句优化,使用reverse索引优化like语句

--原查询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