안녕하세요 뷰속도 에대하 여 질문 했다가 여기로 글을 올리게 됐습니다.

좋은글들로 어느정도 감은 잡았지만  속도가 많이 느린 쿼리 하나로 예를 올립니다.

버전 : 10.2.0.4.0

쿼리 :

select distinct tstaff,
       (select staffnm from b03staffinfo where placecd='011036' and staffid=tstaff) staffnm
from (select tstaff from p06ticket_v where to_char(tdate, 'yyyymmdd')>='20080829'
                                       and to_char(tdate, 'yyyymmdd')<='20080829'
                                       and placecd='011036' and trim(tcd)='004'
                                  group by tstaff
                                 union all
      select refund_id from p06ticket_v where to_char(refund_date, 'yyyymmdd')>='20080829'
                                          and to_char(refund_date, 'yyyymmdd')<='20080829'
                                          and  placecd='011036'
                                          and trim(refund_tcd)='004'
                                          and refund_id is not null group by refund_id )

실행계획 :

------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |     5 |    35 |   114K  (1)| 00:22:55 |
|   1 |  TABLE ACCESS BY INDEX ROWID| B03STAFFINFO     |     1 |    19 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | PK_B03STAFFINFO  |     1 |       |     1   (0)| 00:00:01 |
|   3 |  HASH UNIQUE                |                  |     5 |    35 |   114K  (1)| 00:22:55 |
|   4 |   VIEW                      |                  |     5 |    35 |   114K  (1)| 00:22:55 |
|   5 |    UNION-ALL                |                  |       |       |            |          |
|   6 |     HASH GROUP BY           |                  |     3 |    93 | 57270   (1)| 00:11:28 |
|   7 |      VIEW                   | P06TICKET_V      |     3 |    93 | 57269   (1)| 00:11:28 |
|   8 |       SORT UNIQUE           |                  |     3 |   937 | 57269   (7)| 00:11:28 |
|   9 |        UNION-ALL            |                  |       |       |            |          |
|* 10 |         TABLE ACCESS FULL   | P06TICKET        |     2 |   614 | 54132   (1)| 00:10:50 |
|* 11 |         TABLE ACCESS FULL   | P06TICKET_REFUND |     1 |   323 |  3135   (1)| 00:00:38 |
|  12 |     HASH GROUP BY           |                  |     2 |    62 | 57235   (1)| 00:11:27 |
|  13 |      VIEW                   | P06TICKET_V      |     2 |    62 | 57234   (1)| 00:11:27 |
|  14 |       SORT UNIQUE           |                  |     2 |   630 | 57234   (7)| 00:11:27 |
|  15 |        UNION-ALL            |                  |       |       |            |          |
|* 16 |         TABLE ACCESS FULL   | P06TICKET        |     1 |   307 | 54095   (1)| 00:10:50 |
|* 17 |         TABLE ACCESS FULL   | P06TICKET_REFUND |     1 |   323 |  3137   (1)| 00:00:38 |
------------------------------------------------------------------------------------------------

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

   2 - access("STAFFID"=:B1 AND "PLACECD"='011036')
  10 - filter("PLACECD"='011036' AND TRIM("TCD")='004' AND
              TO_CHAR(INTERNAL_FUNCTION("TDATE"),'yyyymmdd')>='20080829' AND
              TO_CHAR(INTERNAL_FUNCTION("TDATE"),'yyyymmdd')<='20080829')
  11 - filter("PLACECD"='011036' AND TRIM("TCD")='004' AND
              TO_CHAR(INTERNAL_FUNCTION("TDATE"),'yyyymmdd')>='20080829' AND
              TO_CHAR(INTERNAL_FUNCTION("TDATE"),'yyyymmdd')<='20080829')
  16 - filter("REFUND_ID" IS NOT NULL AND "PLACECD"='011036' AND
              TRIM("REFUND_TCD")='004' AND TO_CHAR(INTERNAL_FUNCTION("REFUND_DATE"),'yyyymmdd')>='2008
              0829' AND TO_CHAR(INTERNAL_FUNCTION("REFUND_DATE"),'yyyymmdd')<='20080829')
  17 - filter("PLACECD"='011036' AND TRIM("REFUND_TCD")='004' AND
              TO_CHAR(INTERNAL_FUNCTION("REFUND_DATE"),'yyyymmdd')>='20080829' AND
              TO_CHAR(INTERNAL_FUNCTION("REFUND_DATE"),'yyyymmdd')<='20080829' AND "REFUND_ID" IS NOT
              NULL)

설명:

간략한 설명입니다.
po6ticket_v 가 p06ticket 발권된 티켓 정보
               p06ticket_refund 환불된 티켓 에 대한 정보를 가지고 있습니다
b03staffinfo 는 알바생들 정보를 가지고 있습니다.

티켓 테이블에서 환불이 일어나면 티켓 테이블에서 지워지고
환불 테이블로 이동하게 됩니다.
두테이블의 구조는 동일합니다.

b03staffinfo
staffnm     :알바생이름
staffid     :알바생 ID   :index 있음

p06ticket_v
tdate       : 발권일시
tstaff      : 발권한 알바 ID
tcd         : 발권한 창구

refund_date : 환불일자
refund_tcd  : 환불한 창구
refund_id   : 환불한 알바 ID
placecd     : 점포CD         :index 있음

쿼리는 점포와 날자 창구 번호를 선택하면
그창구에서 발권 하거나 환불한 알바 이름들이 나오게됩니다.

예를 든다고 성능이 안나오는 쿼리를 올렸지만 다른 상당수의 쿼리가 실행계획의 12 ~ 17 이 빠지질 않습니다