Re: Large Dataset - Estimated 87TB needed for TEMP - suggestions?

  • From: Chris Taylor <christopherdtaylor1994@xxxxxxxxx>
  • To: dmarc-noreply@xxxxxxxxxxxxx, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 27 Jan 2015 11:59:46 -0600

Here's something interesting.  The explain plan generated, I'm "losing" a
FILTER condition against Table B.
But if I add the FILTER specifically to the query, I get the FILTER I
expect.  Am I missing something or is Oracle choosing to do a join
elimination here or something?

See ORIGINAL plus CHANGE below it.
Original Condition:

​
  WHERE     A.STS_PERF_PERIOD = b.sts_perf_period
         AND A.STS_DPS_TYPE = b.sts_dps_type
         AND A.STG_GROUP_NO = b.sts_group_no
         AND a.stg_group_seq_no = b.sts_group_seq_no
         AND b.sts_provider = c.stp_provider
         AND b.sts_program_no = c.stp_program_no
         AND a.STS_PERF_PERIOD = '20141'
         AND b.sts_perf_source = 'CB'
         AND b.COWS_DIST_PERIOD in ('20141','20142')
         AND a.PWN_P_W_IND = 'W'
         AND c.stp_s03_program_cd <> '36'
GROUP BY a.STS_PERF_PERIOD,
         a.PART_ACCT_NO,
         a.PWN_PART_AKA_NME,
         a.PWN_P_W_IND,
         a.AFL_SOCIETY_NME,
         b.sts_CHANNEL_TYPE,
         c.stp_s03_program_cd
/

​
Plan hash value: 2657901994


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

   2 - access("A"."STS_PERF_PERIOD"="B"."STS_PERF_PERIOD" AND
"A"."STS_DPS_TYPE"="B"."STS_DPS_TYPE" AND
              "A"."STG_GROUP_NO"="B"."STS_GROUP_NO" AND
"A"."STG_GROUP_SEQ_NO"="B"."STS_GROUP_SEQ_NO")
   4 - access("B"."STS_PROVIDER"="C"."STP_PROVIDER" AND
"B"."STS_PROGRAM_NO"="C"."STP_PROGRAM_NO")
   8 - access(("B"."COWS_DIST_PERIOD"='20141' OR
"B"."COWS_DIST_PERIOD"='20142') AND "B"."STS_PERF_PERIOD"='20141' AND
              "B"."STS_PERF_SOURCE"='CB')
   9 - filter("C"."STP_S03_PROGRAM_CD"<>'36')
  11 - filter("A"."PWN_P_W_IND"='W' AND "A"."STS_PERF_PERIOD"='20141')



​NEW Filter Condition:

   WHERE     A.STS_PERF_PERIOD = b.sts_perf_period
         AND A.STS_DPS_TYPE = b.sts_dps_type
         AND A.STG_GROUP_NO = b.sts_group_no
         AND a.stg_group_seq_no = b.sts_group_seq_no
         AND b.sts_provider = c.stp_provider
         AND b.sts_program_no = c.stp_program_no
         AND a.STS_PERF_PERIOD = '20133'
         AND B.STS_PERF_PERIOD = '20133'  /* Added this explicit condition
*/
         AND b.sts_perf_source = 'CB'​


​   2 - access("ITEM_4"="ITEM_4" AND "ITEM_3"="ITEM_3" AND
"ITEM_2"="ITEM_2" AND "ITEM_1"="ITEM_1")
   6 - filter("A"."PWN_P_W_IND"='W' AND "A"."STS_PERF_PERIOD"='20133')
   9 - access("B"."STS_PROGRAM_NO"="C"."STP_PROGRAM_NO" AND
"B"."STS_PROVIDER"="C"."STP_PROVIDER")
  12 - access("B"."STS_PERF_PERIOD"='20133' AND "B"."STS_PERF_SOURCE"='CB')
       filter("B"."STS_PERF_PERIOD"='20133' AND "B"."STS_PERF_SOURCE"='CB')
  13 - filter("C"."STP_S03_PROGRAM_CD"<>'36')
​
​Thoughts anyone?  The Estimated Space changes significantly here. between
these 2:

(Without the FILTER)
--------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                      |
E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time   | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                           |
   556K|    75M|       |   857K  (1)| 00:57:12 |       |       |
|   1 |  HASH GROUP BY                        |                           |
   556K|    75M|  2502M|   857K  (1)| 00:57:12 |       |       |



(With the explicit FILTER)
----------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name
 | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time   | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |
|  97556 |    13M|       |   201K  (2)| 00:13:26 |       |       |
|   1 |  HASH GROUP BY                          |
|  97556 |    13M|    16M|   201K  (2)| 00:13:26 |       |       |​

Other related posts: