"or" and "( )" make execution plan difference

最新推荐文章于 2025-10-27 10:47:13 发布

转载最新推荐文章于 2025-10-27 10:47:13 发布 · 95 阅读

本文探讨了在Oracle数据库中针对特定查询语句的执行计划进行优化的方法。通过对查询条件的不同构造方式，展示了如何通过调整来获得更优的查询性能。具体包括使用函数索引、避免全表扫描及利用位图转换等技巧。

    test_c as 1160 rows.It has a columan called "STRDHHM".a function index is created in the column "STRDHHM".The statment is like that:
    create index IND_AUTO_CUSTOMERJUDGE_1 on TEST_C (LTRIM(STRDHHM,'0')) tablespace USERS;
    The distinct LTRIM(STRDHHM,'0') values is 1130.And test_c has been analyzed.
    when I use this query statement,It goes "table access full" or "BITMAP CONVERSION":
_____________________________________________________
    EXPLAIN PLAN FOR
SELECT *
from test_c c
where (LTRIM(STRDHHM,'0') = LTRIM('18999912345', '0') AND type = '1')
    OR
       ((LTRIM(STRDHHM,'0')=substr(LTRIM('18999912345', '0'), 1, 3)) AND type = '2')
   and (sysdate - dtjudgedate) * 1440 <= inthmdlimit
   and inthmdflag = 1;
SELECT * from TABLE(dbms_xplan.display)
________________________________________________________
Plan hash value: 1554345677

-------------------------------------------------------------------------------------------------------------
| Id | Operation                        | Name                     | Rows | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                          |     1 |    48 |     3   (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID     | TEST_C                   |     1 |    48 |     3   (0)| 00:00:01 |
|   2 |   BITMAP CONVERSION TO ROWIDS    |                          |       |       |            |          |
|   3 |    BITMAP OR                     |                          |       |       |            |          |
|   4 |     BITMAP CONVERSION FROM ROWIDS|                          |       |       |            |          |
|* 5 |      INDEX RANGE SCAN            | IND_AUTO_CUSTOMERJUDGE_1 |       |       |     1   (0)| 00:00:01 |
|   6 |     BITMAP CONVERSION FROM ROWIDS|                          |       |       |            |          |
|* 7 |      INDEX RANGE SCAN            | IND_AUTO_CUSTOMERJUDGE_1 |       |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------

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

   1 - filter("TYPE"='1' AND LTRIM("STRDHHM",'0')='18999912345' OR "INTHMDFLAG"=1 AND "TYPE"='2' AND
              LTRIM("STRDHHM",'0')='189' AND "INTHMDLIMIT">=(SYSDATE@!-"DTJUDGEDATE")*1440)
   5 - access(LTRIM("STRDHHM",'0')='18999912345')
   7 - access(LTRIM("STRDHHM",'0')='189')
   _________________________________________________________
    when I carry () in the both size of "or" statement,It goes "INLIST ITERATOR" what is I want:
   _______________________________________________________
EXPLAIN PLAN FOR
SELECT COUNT(*)
from test_c c
where ((LTRIM(STRDHHM,'0') = LTRIM('18999912345', '0') AND type = '1')
    OR
       ((LTRIM(STRDHHM,'0')=substr(LTRIM('18999912345', '0'), 1, 3)) AND type = '2'))
   and (sysdate - dtjudgedate) * 1440 <= inthmdlimit
   and inthmdflag = 1;
SELECT * from TABLE(dbms_xplan.display)
_________________________________________________
Plan hash value: 2951030008

----------------------------------------------------------------------------------------------------------
| Id | Operation                     | Name                     | Rows | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                          |     1 |    48 |     3   (0)| 00:00:01 |
|   1 | SORT AGGREGATE               |                          |     1 |    48 |            |          |
|   2 |   INLIST ITERATOR             |                          |       |       |            |          |
|* 3 |    TABLE ACCESS BY INDEX ROWID| TEST_C                   |     1 |    48 |     3   (0)| 00:00:01 |
|* 4 |     INDEX RANGE SCAN          | IND_AUTO_CUSTOMERJUDGE_1 |     2 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------

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

   3 - filter("INTHMDFLAG"=1 AND ("TYPE"='1' AND LTRIM("STRDHHM",'0')='18999912345' OR "TYPE"='2'
              AND LTRIM("STRDHHM",'0')='189') AND "INTHMDLIMIT">=(SYSDATE@!-"DTJUDGEDATE")*1440)
   4 - access(LTRIM("STRDHHM",'0')='18999912345' OR LTRIM("STRDHHM",'0')='189')