Home » SQL & PL/SQL » SQL & PL/SQL » ORA-12801: error signaled in parallel query server PXXX (merged 3) (oracle ,12c)
|
|
|
|
|
|
|
|
Re: ORA-12801: error signaled in parallel query server PXXX [message #679071 is a reply to message #679069] |
Fri, 31 January 2020 03:57 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/179476.png) |
jgjeetu
Messages: 373 Registered: July 2013 Location: www.Orafaq.com/Forum
|
Senior Member |
![jgjeetu@gmail.com](/forum/theme/orafaq/images/google.png)
|
|
/* Formatted on 1/31/2020 3:28:24 PM (QP5 v5.185.11230.41888) */
INSERT /*+ APPEND */
INTO test_usage_facts_tt (characteristics,
usage_bin,
USAGE_BIN_period,
ui_FROM,
ui_to,
season_config_id,
program_id,
cust_class_ind,
day_type,
weather_zone,
rate_group_id,
channel_id,
dummy_location_id,
dummy_account_id,
dummy_co_id,
total_usage,
avg_usage,
total_days,
read_date,
next_read_date)
SELECT characteristics,
usage_bin,
USAGE_BIN_period,
ui_FROM,
ui_to,
season_config_id,
program_id,
cust_class_ind,
day_type,
weather_zone,
rate_group_id,
channel_id,
dummy_location_id,
dummy_account_id,
dummy_co_id,
total_usage,
avg_usage,
total_days,
read_date,
next_read_date
FROM (WITH dt
AS (SELECT /*+ materialize */
billingcycle, read_date, next_read_date
FROM (SELECT billingcycle,
read_date,
LEAD (
read_date)
OVER (PARTITION BY billingcycle
ORDER BY read_date)
next_read_date
FROM test_dates)
WHERE next_read_date BETWEEN TO_DATE (p_entry_date)
- parm_days_for_emr_recovery
AND TO_DATE (p_entry_date) - 1),
dummy_locations
AS (SELECT /*+ materialize ordered full(cum) parallel(cum,8 ) use_nl(md) use_nl(sm) */
cum.entry_date,
md.device_id,
sm.dummy_location_id,
sm.dummy_account_id,
md.readingcycle,
dt.billingcycle,
dt.read_date,
dt.next_read_date
FROM test_vee_override_gateways override,
test_cum_reads cum,
dummy_device md,
dt,
dummy_meter sm
WHERE cum.device_id = md.device_id
AND p_entry_date <> dt.next_read_date
AND cum.entry_date = p_entry_date
AND md.readingcycle = dt.billingcycle
AND md.device_id = sm.device_id
AND TO_DATE (p_entry_date) - 1 / 86400 BETWEEN sm.effective_date
AND sm.end_effective_date
- 1
/ 86400
AND cum.gateway_id = override.gateway_id)
SELECT /*+ ordered index(mra,dummy_usage_facts_daily_N1 ) use_nl(mra) */
characteristics,
usage_bin,
USAGE_BIN_period,
ui_FROM,
ui_to,
day_type,
weather_zone,
rate_group_id,
channel_id,
mra.dummy_location_id,
mra.dummy_account_id,
avg_usage AS total_usage,
avg_usage,
1 total_days,
dummy_locations.read_date,
dummy_locations.next_read_date,
SEASON_CONFIG_ID,
program_id,
cust_class_ind,
mra.dummy_co_id
FROM dummy_locations, dummy_usage_facts_daily mra
WHERE 1 = 1
AND dummy_locations.dummy_location_id =
mra.dummy_location_id
AND dummy_locations.dummy_account_id = mra.dummy_account_id
AND mra.ui_to >= dummy_locations.read_date + 1
AND mra.ui_to <= dummy_locations.next_read_date
AND mra.readcycle = dummy_locations.readingcycle)
/* Note : I have used dummy_names in above query */
[Updated on: Fri, 31 January 2020 03:58] Report message to a moderator
|
|
|
|
|
|
Re: ORA-12801: error signaled in parallel query server PXXX [message #679085 is a reply to message #679074] |
Mon, 03 February 2020 00:19 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/179476.png) |
jgjeetu
Messages: 373 Registered: July 2013 Location: www.Orafaq.com/Forum
|
Senior Member |
![jgjeetu@gmail.com](/forum/theme/orafaq/images/google.png)
|
|
/* Formatted on 1/31/2020 5:29:26 PM (QP5 v5.185.11230.41888) */
INSERT /*+ APPEND */
INTO test_usage_facts_tt (characteristics,
usage_bin,
usage_bin_period,
ui_from,
ui_to,
season_config_id,
program_id,
cust_class_ind,
day_type,
weather_zone,
rate_group_id,
channel_id,
dummy_location_id,
dummy_account_id,
dummy_co_id,
total_usage,
avg_usage,
total_days,
read_date,
next_read_date)
SELECT characteristics,
usage_bin,
usage_bin_period,
ui_from,
ui_to,
season_config_id,
program_id,
cust_class_ind,
day_type,
weather_zone,
rate_group_id,
channel_id,
dummy_location_id,
dummy_account_id,
dummy_co_id,
total_usage,
avg_usage,
total_days,
read_date,
next_read_date
FROM (WITH dt
AS (SELECT /*+ materialize */
billingcycle, read_date, next_read_date
FROM (SELECT billingcycle,
read_date,
LEAD (
read_date)
OVER (PARTITION BY billingcycle
ORDER BY read_date)
next_read_date
FROM test_dates)
WHERE next_read_date BETWEEN TO_DATE (p_entry_date)
- parm_days_for_emr_recovery
AND TO_DATE (p_entry_date) - 1),
dummy_locations
AS (SELECT /*+ materialize ordered full(cum) parallel(cum,8) use_nl(md) use_nl(sm) */
cum.entry_date,
md.device_id,
sm.dummy_location_id,
sm.dummy_account_id,
md.readingcycle,
dt.billingcycle,
dt.read_date,
dt.next_read_date
FROM test_vee_override_gateways override,
test_cum_reads cum,
dummy_device md,
dt,
dummy_meter sm
WHERE cum.device_id = md.device_id
AND p_entry_date <> dt.next_read_date
AND cum.entry_date = p_entry_date
AND md.readingcycle = dt.billingcycle
AND md.device_id = sm.device_id
AND TO_DATE (p_entry_date) - 1 / 86400 BETWEEN sm.effective_date
AND sm.end_effective_date
- 1
/ 86400
AND cum.gateway_id = override.gateway_id)
SELECT /*+ ordered index(mra,dummy_usage_facts_daily_N1 ) use_nl(mra) */
characteristics,
usage_bin,
usage_bin_period,
ui_from,
ui_to,
day_type,
weather_zone,
rate_group_id,
channel_id,
mra.dummy_location_id,
mra.dummy_account_id,
avg_usage AS total_usage,
avg_usage,
1 total_days,
dummy_locations.read_date,
dummy_locations.next_read_date,
season_config_id,
program_id,
cust_class_ind,
mra.dummy_co_id
FROM dummy_locations, dummy_usage_facts_daily mra
WHERE 1 = 1
AND dummy_locations.dummy_location_id =
mra.dummy_location_id
AND dummy_locations.dummy_account_id = mra.dummy_account_id
AND mra.ui_to >= dummy_locations.read_date + 1
AND mra.ui_to <= dummy_locations.next_read_date
AND mra.readcycle = dummy_locations.readingcycle)
/* Note : I have used dummy_names in above query */
|
|
|
Goto Forum:
Current Time: Sat Sep 07 21:41:26 CDT 2024
|