Test2 Schema is Range by Week.
Test3 Schema is Range by Month and then subpartition Hashed by Org_id
PHP Code:********************************************************************************
SELECT /* getFilteredForecast */
MIN(f.intvl_nbr) AS intvl_nbr,
SUM(f.usage_nbr * 4) * 0.0 AS usage_nbr,
COUNT(*) AS sample_count
FROM
test2.ACTIVE_DEVICE_MV ad,
test2.USAGE_FORECAST f
WHERE
ad.org_id= '4' AND
ad.svc_type_cd = 'E' AND
EXISTS
(SELECT mi.end_dt
FROM test2.METER_INTERVAL mi
WHERE mi.org_id = ad.org_id AND
mi.device_id = ad.device_id AND
mi.end_dt >= SYSDATE-3) AND
f.org_id = ad.org_id AND
f.cust_id = ad.cust_id AND
f.day_type_cd = 'WD'
GROUP BY f.intvl_nbr
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.59 0.56 0 772 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 49 0.77 0.79 74 1626 0 96
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 51 1.36 1.36 74 2398 0 96
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 46
Rows Row Source Operation
------- ---------------------------------------------------
96 SORT GROUP BY (cr=1626 r=74 w=0 time=794353 us)
3744 HASH JOIN SEMI (cr=1626 r=74 w=0 time=756400 us)
4224 HASH JOIN (cr=112 r=74 w=0 time=191241 us)
44 INDEX RANGE SCAN ACTIVE_DEVICE_MV3_IX (cr=1 r=0 w=0 time=418 us)(object id 135464)
4704 INDEX FAST FULL SCAN USG_FCST_USAGE_NBR_IX (cr=111 r=74 w=0 time=97762 us)(object id 134420)
3978 PARTITION RANGE ITERATOR PARTITION: KEY 9 (cr=1514 r=0 w=0 time=161343 us)
3978 TABLE ACCESS BY LOCAL INDEX ROWID METER_INTERVAL PARTITION: KEY 9 (cr=1514 r=0 w=0 time=152088 us)
3978 INDEX RANGE SCAN MTRINT_ORG_ID_IX PARTITION: KEY 9 (cr=19 r=0 w=0 time=69281 us)(object id 134946)
********************************************************************************
SELECT
MIN(mi.end_dt) AS end_dt,
SUM(mi.usage_nbr*4) AS usage_nbr,
COUNT(*) AS sample_count
FROM
test2.ACTIVE_DEVICE_MV ad,
test2.METER_INTERVAL mi
WHERE
ad.org_id='ME' AND
ad.svc_type_cd = 'E' AND
mi.org_id = ad.org_id AND
mi.device_id = ad.device_id AND
mi.end_dt >= ad.created_dt AND
mi.end_dt >= SYSDATE-3 AND
mi.end_dt <= SYSDATE
GROUP BY mi.end_dt
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.03 0.02 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 52 0.34 0.31 0 1642 0 102
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 54 0.37 0.34 0 1642 0 102
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 46
Rows Row Source Operation
------- ---------------------------------------------------
102 SORT GROUP BY (cr=1642 r=0 w=0 time=314837 us)
5503 FILTER (cr=1642 r=0 w=0 time=233585 us)
5503 HASH JOIN (cr=1642 r=0 w=0 time=219382 us)
58 INDEX RANGE SCAN ACTIVE_DEVICE_MV3_IX (cr=1 r=0 w=0 time=405 us)(object id 135464)
5503 PARTITION RANGE ITERATOR PARTITION: KEY KEY (cr=1641 r=0 w=0 time=144495 us)
5503 TABLE ACCESS BY LOCAL INDEX ROWID METER_INTERVAL PARTITION: KEY KEY (cr=1641 r=0 w=0 time=131264 us)
5503 INDEX RANGE SCAN MTRINT_ORG_ID_IX PARTITION: KEY KEY (cr=19 r=0 w=0 time=38819 us)(object id 134946)
********************************************************************************
SELECT /* getFilteredForecast */
MIN(f.intvl_nbr) AS intvl_nbr,
SUM(f.usage_nbr * 4) * 0.0 AS usage_nbr,
COUNT(*) AS sample_count
FROM
test3.ACTIVE_DEVICE_MV ad,
test3.USAGE_FORECAST f
WHERE
ad.org_id= '4' AND
ad.svc_type_cd = 'E' AND
EXISTS
(SELECT mi.end_dt
FROM test3.METER_INTERVAL mi
WHERE mi.org_id = ad.org_id AND
mi.device_id = ad.device_id AND
mi.end_dt >= SYSDATE-3) AND
f.org_id = ad.org_id AND
f.cust_id = ad.cust_id AND
f.day_type_cd = 'WD'
GROUP BY f.intvl_nbr
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.03 0.03 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 49 0.08 0.08 2 147 0 96
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 51 0.11 0.11 2 147 0 96
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 46
Rows Row Source Operation
------- ---------------------------------------------------
96 SORT GROUP BY (cr=147 r=2 w=0 time=83726 us)
3744 NESTED LOOPS (cr=147 r=2 w=0 time=44485 us)
39 NESTED LOOPS SEMI (cr=91 r=2 w=0 time=21272 us)
44 INDEX RANGE SCAN ACTIVE_DEVICE_MV3_IX (cr=1 r=0 w=0 time=433 us)(object id 135473)
39 INDEX RANGE SCAN MTRINT_END_DT_PK (cr=90 r=2 w=0 time=19877 us)(object id 135244)
3744 INDEX RANGE SCAN USG_FCST_USAGE_NBR_IX (cr=56 r=0 w=0 time=13994 us)(object id 135419)
********************************************************************************
SELECT
MIN(mi.end_dt) AS end_dt,
SUM(mi.usage_nbr*4) AS usage_nbr,
COUNT(*) AS sample_count
FROM
test3.ACTIVE_DEVICE_MV ad,
test3.METER_INTERVAL mi
WHERE
ad.org_id='ME' AND
ad.svc_type_cd = 'E' AND
mi.org_id = ad.org_id AND
mi.device_id = ad.device_id AND
mi.end_dt >= ad.created_dt AND
mi.end_dt >= SYSDATE-3 AND
mi.end_dt <= SYSDATE
GROUP BY mi.end_dt
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.03 0.02 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 52 1.34 1.31 0 6654 0 102
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 54 1.37 1.33 0 6654 0 102
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 46
Rows Row Source Operation
------- ---------------------------------------------------
102 SORT GROUP BY (cr=6654 r=0 w=0 time=1310898 us)
5503 FILTER (cr=6654 r=0 w=0 time=1217410 us)
5503 TABLE ACCESS BY GLOBAL INDEX ROWID OBJ#(134989) PARTITION: ROW LOCATION ROW LOCATION (cr=6654 r=0 w=0 time=1186406 us)
5562 NESTED LOOPS (cr=1540 r=0 w=0 time=1033466 us)
58 INDEX RANGE SCAN OBJ#(135473) (cr=1 r=0 w=0 time=975 us)(object id 135473)
5503 INDEX RANGE SCAN OBJ#(135244) (cr=1539 r=0 w=0 time=1002721 us)(object id 135244)
Reply With Quote