请教一个sql优化问题

lostx

请教一个sql优化问题,版本:10.2.0.4.0  , RECEPTION一直走全表,使用NO_EXPAND提示没什么效果,请大家帮忙看看怎么优化!

select *
  from RECEPTION A
where (A.RECORGID = :ORGID or
       A.RECORGID in (select ORGID
                         from ORGANIZATION B
                        where exists (select 1
                                 from ORGANIZATION_CHILD
                                where SUBWAYID = B.ORGID
                                  and PARWAYID = :ORGID)))
   and A.RECDATE between :SDATE and :EDATE
   and A.REGION = 311
   and (A.ISBACKPROCESS <> 1 or A.ISBACKPROCESS is null)
   and ROWNUM < :ROWNUMBER
order by oid;



Plan hash value: 2299894236

--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name              | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                   |  5234 |  1139K|       |  2290K  (3)| 04:29:04 |       |       |
|*  1 |  FILTER                      |                   |       |       |       |            |          |       |       |
|   2 |   SORT ORDER BY              |                   |  5234 |  1139K|    62M|  2290K  (3)| 04:29:04 |       |       |
|*  3 |    COUNT STOPKEY             |                   |       |       |       |            |          |       |       |
|*  4 |     FILTER                   |                   |       |       |       |            |          |       |       |
|   5 |      PARTITION RANGE ITERATOR|                   |   104K|    22M|       |  2286K  (3)| 04:28:33 |   KEY |   KEY |
|*  6 |       TABLE ACCESS FULL      | RECEPTION         |   104K|    22M|       |  2286K  (3)| 04:28:33 |   KEY |   KEY |
|   7 |      NESTED LOOPS            |                   |     1 |    44 |       |     3   (0)| 00:00:01 |       |       |
|*  8 |       INDEX UNIQUE SCAN      | PK_T_UCP_ORGAINFO |     1 |    17 |       |     2   (0)| 00:00:01 |       |       |
|*  9 |       INDEX UNIQUE SCAN      | PK_UCP_ORGACHILD  |     1 |    27 |       |     1   (0)| 00:00:01 |       |       |
--------------------------------------------------------------------------------------------------------------------------

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

   1 - filter(TO_DATE(:SDATE)<=TO_DATE(:EDATE))
   3 - filter(ROWNUM<TO_NUMBER(:ROWNUMBER))
   4 - filter("A"."RECORGID"=:ORGID OR  EXISTS (SELECT /*+ */ 0 FROM "COMMON"."T_UCP_ORGAINFO"
              "ORG","COMMON"."T_UCP_ORGACHILD" "ORGANIZATION_CHILD" WHERE "SUBWAYID"=:B1 AND "PARWAYID"=:ORGID AND
              "ORG"."ORGAID"=:B2))
   6 - filter("A"."REGION"=311 AND ("A"."ISBACKPROCESS"<>1 OR "A"."ISBACKPROCESS" IS NULL) AND
              "A"."RECDATE">=:SDATE AND "A"."RECDATE"<=:EDATE)
   8 - access("ORG"."ORGAID"=:B1)
   9 - access("PARWAYID"=:ORGID AND "SUBWAYID"=:B1)

棉花糖ONE

把or拆开成union

bell6248

Try!


select  *
  from
(select *
  from RECEPTION A
where  A.RECORGID = :ORGID
   and A.RECDATE between :SDATE and :EDATE
   and A.REGION = 311
   and nvl(A.ISBACKPROCESS, 0) <> 1
union
select *
  from RECEPTION A
where A.RECORGID <> :ORGID
   and exists(select 1
               from ORGANIZATION B
              where exists(select 1
                             from ORGANIZATION_CHILD C
                            where C.SUBWAYID = B.ORGID
                              and C.PARWAYID = :ORGID)
              where B.ORGID = A.A.RECORGID)
   and A.RECDATE between :SDATE and :EDATE
   and A.REGION = 311
   and nvl(A.ISBACKPROCESS, 0) <> 1)
where rownum < :ROWNUMBER
order by oid;



[ 本帖最后由 bell6248 于 2009-12-30 13:53 编辑 ]

morong

|   5 |      PARTITION RANGE ITERATOR|                   |   104K|    22M|       |  2286K  (3)| 04:28:33 |   KEY |   KEY |
|*  6 |       TABLE ACCESS FULL      | RECEPTION         |   104K|    22M|       |  2286K  (3)| 04:28:33 |   KEY |   KEY |

走的不是全表。《PARTITION RANGE ITERATOR》 只是分区扫描。

qingcheng_yuan

mark

qingcheng_yuan

hi

能分析下为什么这么拆分吗?
union 的性能会好很多吗?

MatriX-staff

关键是因为你的语句中条件 A.ISBACKPROCESS is null  这会导致优化器无法使用表RECEPTION上的索引

duiego

是你的 or语句存在问题，使用or 会屏蔽掉索引而走全扫，将or拆开 用union  
is null 没有影响

MatriX-staff

or 会屏蔽索引？什么情况下，

lostx

原帖由 棉花糖ONE 于 2009-12-30 12:37 发表
把or拆开成union

通过union,执行效率得到提升, 使用or的时候oracle 不走索引么?
explain plan for
select *
  from (select A.oid
          from RECEPTION A
         where (A.RECORGID = :ORGID)
           and A.RECDATE between :SDATE and :EDATE
           and A.REGION = 311
           and (A.ISBACKPROCESS <> 1 or A.ISBACKPROCESS is null)
           and ROWNUM < :ROWNUMBER
        union
        select A.oid
          from RECEPTION A
         where (A.RECORGID in (select ORGID
                                 from ORGANIZATION B
                                where exists (select 1
                                         from ORGANIZATION_CHILD
                                        where SUBWAYID = B.ORGID
                                          and PARWAYID = :ORGID)))
           and A.RECDATE between :SDATE and :EDATE
           and A.REGION = 311
           and (A.ISBACKPROCESS <> 1 or A.ISBACKPROCESS is null)
           and ROWNUM < :ROWNUMBER)
order by oid;


Plan hash value: 1993454495

---------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name                   | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                        |    28 |   364 |  9059   (1)| 00:01:04 |       |       |
|   1 |  SORT ORDER BY                         |                        |    28 |   364 |  9059   (1)| 00:01:04 |       |       |
|   2 |   VIEW                                 |                        |    28 |   364 |  9058   (1)| 00:01:04 |       |       |
|   3 |    UNION-ALL                           |                        |       |       |            |          |       |       |
|*  4 |     COUNT STOPKEY                      |                        |       |       |            |          |       |       |
|   5 |      PARTITION RANGE ITERATOR          |                        |    12 |   420 |   711   (1)| 00:00:06 |   KEY |   KEY |
|*  6 |       TABLE ACCESS BY LOCAL INDEX ROWID| RECEPTION              |    12 |   420 |   711   (1)| 00:00:06 |   KEY |   KEY |
|*  7 |        INDEX RANGE SCAN                | IDX_RECEPTION_RECORGID |   299 |       |   472   (0)| 00:00:04 |   KEY |   KEY |
|*  8 |     COUNT STOPKEY                      |                        |       |       |            |          |       |       |
|*  9 |      TABLE ACCESS BY LOCAL INDEX ROWID | RECEPTION              |     1 |    35 |   710   (1)| 00:00:06 |     1 |     1 |
|  10 |       NESTED LOOPS                     |                        |    16 |  1264 |  8347   (1)| 00:00:59 |       |       |
|  11 |        NESTED LOOPS                    |                        |    17 |   748 |    13   (8)| 00:00:01 |       |       |
|  12 |         SORT UNIQUE                    |                        |    17 |   459 |     3   (0)| 00:00:01 |       |       |
|* 13 |          INDEX RANGE SCAN              | PK_UCP_ORGACHILD       |    17 |   459 |     3   (0)| 00:00:01 |       |       |
|* 14 |         INDEX UNIQUE SCAN              | PK_T_UCP_ORGAINFO      |     1 |    17 |     1   (0)| 00:00:01 |       |       |
|  15 |        PARTITION RANGE ITERATOR        |                        |   299 |       |   471   (0)| 00:00:04 |   KEY |   KEY |
|* 16 |         INDEX RANGE SCAN               | IDX_RECEPTION_RECORGID |   299 |       |   471   (0)| 00:00:04 |   KEY |   KEY |
---------------------------------------------------------------------------------------------------------------------------------

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

   4 - filter(ROWNUM<TO_NUMBER(:ROWNUMBER))
   6 - filter("A"."REGION"=311 AND ("A"."ISBACKPROCESS"<>1 OR "A"."ISBACKPROCESS" IS NULL))
   7 - access("A"."RECORGID"=:ORGID AND "A"."RECDATE">=:SDATE AND "A"."RECDATE"<=:EDATE)
   8 - filter(ROWNUM<TO_NUMBER(:ROWNUMBER))
   9 - filter("A"."REGION"=311 AND ("A"."ISBACKPROCESS"<>1 OR "A"."ISBACKPROCESS" IS NULL))
  13 - access("PARWAYID"=:ORGID)
  14 - access("SUBWAYID"="ORG"."ORGAID")
  16 - access("A"."RECORGID"="ORG"."ORGAID" AND "A"."RECDATE">=:SDATE AND "A"."RECDATE"<=:EDATE)