Problem with complicated select process.

From: Will Weir, SICAS Center <WEIRWR_at_SNYONEVA.CC.ONEONTA.EDU>
Date: Thu, 16 Mar 1995 11:45:49 -0500
Message-Id: <9503161936.AA02516@alice.jcc.com>

Hi all,

I am sort of a "Newbie" to the list. I have been a quiet observer for some time now and I've come across a real frustration.

I will genuinely appreciate any advice you can give.

I have a huge table with approximately one million rows that I need to access via a complex select statement in SQR. I tried to index the table on Activity Date and after running EXPLAIN PLAN I realized my index is not being used.

This is probably due to the TRUNC function I have on the Activity date in my select statement. I'm going to try it without the TRUNC to see what happens but I wanted to see what the other ORACLE People have to say about it.

This thing runs in about 15 hours on a small subset of data and we have not let it run long enough to finish on the real table. It needs to run in less than 2-3 hours.

Here's what I have:

EXPLAIN PLAN SET STATEMENT_ID = 'query1' FOR select y.sbbyaps_chg_detail_code,

        (y.sbbyaps_amount - x.sbbyaps_amount) sbbyaps_amount from saturn.sbbyaps x,

        saturn.sbbyaps y

where   x.sbbyaps_chg_detail_code = y.sbbyaps_chg_detail_code
and     x.sbbyaps_pay_detail_code = y.sbbyaps_pay_detail_code
and     x.sbbyaps_chg_term_code = y.sbbyaps_chg_term_code
and     x.sbbyaps_pay_term_code = y.sbbyaps_pay_term_code
and     trunc(x.sbbyaps_activity_date) = (select tmpydat_start
                                          from   taismgr.tmpydat)
and     trunc(y.sbbyaps_activity_date) = (select tmpydat_end
                                          from   taismgr.tmpydat)

union
select

        x.sbbyaps_chg_detail_code,
        x.sbbyaps_amount * -1

from saturn.sbbyaps x
where trunc(x.sbbyaps_activity_date) = (select tmpydat_start

                                          from   taismgr.tmpydat)
and     not exists
       (select 'x' from saturn.sbbyaps y
        where   x.sbbyaps_chg_detail_code = y.sbbyaps_chg_detail_code
        and     x.sbbyaps_pay_detail_code = y.sbbyaps_pay_detail_code
        and     x.sbbyaps_chg_term_code = y.sbbyaps_chg_term_code
        and     x.sbbyaps_pay_term_code = y.sbbyaps_pay_term_code
        and     trunc(y.sbbyaps_activity_date) = (select tmpydat_end from
                taismgr.tmpydat))

union
select

        y.sbbyaps_chg_detail_code,
        y.sbbyaps_amount

from saturn.sbbyaps y
where trunc(y.sbbyaps_activity_date) = (select tmpydat_end

                                          from   taismgr.tmpydat)
and     not exists
       (select 'x' from saturn.sbbyaps x
        where   y.sbbyaps_chg_detail_code = x.sbbyaps_chg_detail_code
        and     y.sbbyaps_pay_detail_code = x.sbbyaps_pay_detail_code
        and     y.sbbyaps_chg_term_code   = x.sbbyaps_chg_term_code
        and     y.sbbyaps_pay_term_code   = x.sbbyaps_pay_term_code
        and     trunc(x.sbbyaps_activity_date) = (select tmpydat_start from
                taismgr.tmpydat));

FYI... TMPYDAT is a table containing a start and end date. These were entered as parameters of CHAR and inserted into the table using to TO_DATE function.

Here's the Explained version:

08:54:37 SQL> @plan
08:54:42 SQL> SELECT LPAD(' ',2*level)||operation||' '||options||' '||object_name query_plan

08:54:42   2  FROM   plan_table
08:54:42   3  WHERE  statement_id = 'query1'
08:54:42   4  CONNECT BY PRIOR id = parent_id
08:54:42   5  AND statement_id = 'query1'
08:54:42   6  START WITH parent_id IS NULL
08:54:42   7  AND statement_id = 'query1'
08:54:42   8  ORDER BY id;

QUERY_PLAN

---

  PROJECTION
    UNION

      UNION
        SORT UNIQUE
          FILTER
            MERGE JOIN
              SORT JOIN
                TABLE ACCESS FULL SBBYAPS
              SORT JOIN
                TABLE ACCESS FULL SBBYAPS
            TABLE ACCESS FULL TMPYDAT
            TABLE ACCESS FULL TMPYDAT
        SORT UNIQUE
          FILTER
            TABLE ACCESS FULL SBBYAPS
            TABLE ACCESS FULL TMPYDAT
            FILTER
              TABLE ACCESS FULL SBBYAPS
              TABLE ACCESS FULL TMPYDAT
      SORT UNIQUE
        FILTER

QUERY_PLAN

---

          TABLE ACCESS FULL SBBYAPS
          TABLE ACCESS FULL TMPYDAT
          FILTER
            TABLE ACCESS FULL SBBYAPS
            TABLE ACCESS FULL TMPYDAT

26 rows selected.

Again, any help or advice will be greatly appreciated.

Thanks,

Will

---

|WILL WEIR |
|PROGRAMMER/ANALYST |
|SUNY SICAS Center |
|WEIRWR_at_SNYONEVA.CC.ONEONTA.EDU |

---

Received on Thu Mar 16 1995 - 14:36:17 CST