Home » RDBMS Server » Performance Tuning » views in 92
views in 92 [message #228679] |
Tue, 03 April 2007 12:51 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
moody1
Messages: 15 Registered: March 2007
|
Junior Member |
|
|
We have some views that worked fine in 8i but after converting to
92 the sql using these views are dog slow. Any one have any ideas?
|
|
|
|
|
Re: views in 92 [message #228689 is a reply to message #228682] |
Tue, 03 April 2007 13:57 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
moody1
Messages: 15 Registered: March 2007
|
Junior Member |
|
|
Statistics are gathered weekly.
This is the view:
SELECT
/*+ index (pd pk_promo_dates) index(p pk_promotions) index(u 1x_upc_id_store_promo) */ p.promo_id,
u.upc_id,
u.store,
pd.beg_date,
pd.end_date,
p.AD_TYPE,
p.AD_QTY,
p.AD_AMT,
p.AD_MIX_MATCH,
p.LQD_MAX_QTY,
u.tag_auth_override,
p.PURCHASE_REQ,
p.PURCHASE_TYPE,
u.AD_VEHICLE,
u.AD_START,
u.AD_END,
p.AD_NOTES,
p.SURVEY_EXCLUDE_FLAG,
u.IMMED_FLAG,
u.IMMED_DATE,
u.mini_special_buy,
p.CONTRACT_ID,
p.CONTRACT_NOTES,
u.UPDATE_ID,
u.UPDATE_DATE
FROM
promo_dates pd,
upc_id_store_promo u,
store s,
promotions p
WHERE
u.PROMO_ID = pd.PROMO_ID and
u.promo_id = p.promo_id and
u.store = s.store and
s.day_code = pd.day_code
This is the procedure being ran against the view:
familyvar VARCHAR2(50);
weekendvar DATE;
datevar DATE;
cntr NUMBER := 0;
--CURSOR c_buyer IS
--SELECT DISTINCT buyer_id FROM BUYER_MASTER;
CURSOR c_upc IS
SELECT *
FROM improd.upc_master
WHERE upc=primary_upc
AND upc_status='A';
CURSOR c_ad_long (upcidparam IN NUMBER) IS
SELECT u.upc_id, u.store,
pd.beg_date, pd.end_date,
p.AD_TYPE, p.AD_QTY, p.AD_AMT,
p.AD_MIX_MATCH, p.LQD_MAX_QTY,
u.tag_auth_override,
p.PURCHASE_REQ, p.PURCHASE_TYPE,
u.AD_VEHICLE, u.AD_START, u.AD_END,
p.AD_NOTES, p.SURVEY_EXCLUDE_FLAG,
u.IMMED_FLAG, u.IMMED_DATE,
u.mini_special_buy,
p.CONTRACT_ID, p.CONTRACT_NOTES,
u.UPDATE_ID, u.UPDATE_DATE,
workspace.buyer_id_function(improd.vendor_buyer_function(u.upc_id,u.store)) buyer_id,
improd.vendor_pack_function(u.upc_id,u.store,trunc(sysdate)) pack
FROM improd.promo_dates pd,
improd.upc_id_store_promo u,
improd.store s,
improd.promotions p
WHERE u.PROMO_ID = pd.PROMO_ID
AND u.promo_id = p.promo_id
AND u.store = s.store
AND s.day_code = pd.day_code
AND u.upc_id = upcidparam
AND pd.end_date >= trunc(sysdate)
AND u.store not in (164,798)
ORDER BY u.store;
--and end_date-beg_date > 7
CURSOR c_pend_date_long (upcidparam IN NUMBER) IS
SELECT upc_id, week_end_date
FROM (
SELECT u.upc_id,
next_day(trunc(pd.beg_date), 'saturday') week_end_date
FROM improd.promo_dates pd,
improd.upc_id_store_promo u,
improd.store s,
improd.promotions p
WHERE u.PROMO_ID = pd.PROMO_ID
AND u.promo_id = p.promo_id
AND u.store = s.store
AND s.day_code = pd.day_code
AND u.upc_id=upcidparam
AND pd.end_date-pd.beg_date > 7
AND pd.beg_date > trunc(sysdate)
AND u.store = (SELECT min(u2.store)
FROM improd.promo_dates pd2,
improd.upc_id_store_promo u2,
improd.store s2,
improd.promotions p2
WHERE u2.PROMO_ID = pd2.PROMO_ID
AND u2.promo_id = p2.promo_id
AND u2.store = s2.store
AND s2.day_code = pd2.day_code
AND u2.upc_id = upcidparam
AND u2.store not in (164,798)
AND pd2.end_date - pd2.beg_date > 7
AND pd2.beg_date > trunc(sysdate)))
group by upc_id, week_end_date;
CURSOR c_pend_long (upcidparam IN NUMBER,dateparam IN DATE) IS
SELECT u.upc_id, u.store,
pd.beg_date, pd.end_date,
p.AD_TYPE, p.AD_QTY, p.AD_AMT,
p.AD_MIX_MATCH, p.LQD_MAX_QTY,
u.tag_auth_override,
p.PURCHASE_REQ, p.PURCHASE_TYPE,
u.AD_VEHICLE, u.AD_START, u.AD_END,
p.AD_NOTES, p.SURVEY_EXCLUDE_FLAG,
u.IMMED_FLAG, u.IMMED_DATE,
u.mini_special_buy,
p.CONTRACT_ID, p.CONTRACT_NOTES,
u.UPDATE_ID, u.UPDATE_DATE,
workspace.buyer_id_function(improd.vendor_buyer_function(u.upc_id,u.store)) buyer_id,
improd.vendor_pack_function(u.upc_id,u.store,trunc(sysdate)) pack
FROM improd.promo_dates pd,
improd.upc_id_store_promo u,
improd.store s,
improd.promotions p
WHERE u.PROMO_ID = pd.PROMO_ID
AND u.promo_id = p.promo_id
AND u.store = s.store
AND s.day_code = pd.day_code
AND u.upc_id = upcidparam
AND dateparam BETWEEN pd.beg_date AND pd.end_date
AND pd.end_date - pd.beg_date > 7
AND pd.beg_date > trunc(sysdate)
AND u.store not in (164,798)
ORDER BY u.store;
CURSOR c_pend_date (upcidparam IN NUMBER) IS
SELECT upc_id, week_end_date
FROM (
SELECT u.upc_id,
next_day(trunc(pd.beg_date), 'saturday') week_end_date
FROM improd.promo_dates pd,
improd.upc_id_store_promo u,
improd.store s,
improd.promotions p
WHERE u.PROMO_ID = pd.PROMO_ID
AND u.promo_id = p.promo_id
AND u.store = s.store
AND s.day_code = pd.day_code
AND u.upc_id= upcidparam
AND pd.beg_date > trunc(sysdate)
AND not exists (
SELECT '1'
FROM improd.promo_dates pd2,
improd.upc_id_store_promo u2,
improd.store s2,
improd.promotions p2
WHERE u2.PROMO_ID = pd2.PROMO_ID
AND u2.promo_id = p2.promo_id
AND u2.store = s2.store
AND s2.day_code = pd2.day_code
AND u2.upc_id = u.upc_id
AND u2.store = u.store
AND u2.upc_id = upcidparam
AND pd2.end_date - pd2.beg_date > 7
AND u2.store = (SELECT min(u3.store)
FROM improd.promo_dates pd3,
improd.upc_id_store_promo u3,
improd.store s3,
improd.promotions p3
WHERE u3.PROMO_ID = pd3.PROMO_ID
AND u3.promo_id = p3.promo_id
AND u3.store = s3.store
AND s3.day_code = pd3.day_code
AND u3.upc_id = upcidparam
AND u3.store not in (164,798)
AND pd3.end_date - pd3.beg_date > 7
AND pd3.beg_date > trunc(sysdate) )))
GROUP BY upc_id,week_end_date;
CURSOR c_pend (upcidparam IN NUMBER,dateparam IN DATE) IS
SELECT p.promo_id, u.upc_id, u.store,
pd.beg_date, pd.end_date,
p.AD_TYPE, p.AD_QTY, p.AD_AMT,
p.AD_MIX_MATCH, p.LQD_MAX_QTY,
u.tag_auth_override,
p.PURCHASE_REQ, p.PURCHASE_TYPE,
u.AD_VEHICLE, u.AD_START, u.AD_END,
p.AD_NOTES, p.SURVEY_EXCLUDE_FLAG,
u.IMMED_FLAG, u.IMMED_DATE,
u.mini_special_buy,
p.CONTRACT_ID, p.CONTRACT_NOTES,
u.UPDATE_ID, u.UPDATE_DATE,
workspace.buyer_id_function(improd.vendor_buyer_function(u.upc_id,u.store)) buyer_id,
improd.vendor_pack_function(u.upc_id,u.store,trunc(sysdate)) pack
FROM improd.promo_dates pd,
improd.upc_id_store_promo u,
improd.store s,
improd.promotions p
WHERE u.PROMO_ID = pd.PROMO_ID
AND u.promo_id = p.promo_id
AND u.store = s.store
AND s.day_code = pd.day_code
AND u.upc_id = upcidparam
AND dateparam BETWEEN pd.beg_date AND pd.end_date
AND pd.beg_DATE > trunc(sysdate)
AND u.store not in (164,798)
AND not exists (
SELECT '1'
FROM improd.promo_dates pd2,
improd.upc_id_store_promo u2,
improd.store s2,
improd.promotions p2
WHERE u2.PROMO_ID = pd2.PROMO_ID
AND u2.promo_id = p2.promo_id
AND u2.store = s2.store
AND s2.day_code = pd2.day_code
AND u2.upc_id = u.upc_id
AND u2.store = u.store
AND u2.upc_id = upcidparam
AND dateparam BETWEEN pd2.beg_date AND pd2.end_date
AND pd2.beg_DATE > trunc(sysdate)
AND pd2.end_date - pd2.beg_date > 7
AND pd2.beg_Date = pd.beg_Date
AND pd2.end_date = pd.end_date)
ORDER BY STORE;
BEGIN
dbms_output.enable(1000000);
--FOR v_buyer IN c_buyer LOOP
FOR v_upc IN c_upc LOOP
BEGIN
SELECT family_group_name
INTO familyvar
FROM improd.FAMILY_GROUP
WHERE family_group_nbr=v_upc.retail_family_group_nbr;
EXCEPTION
WHEN NO_DATA_FOUND THEN
familyvar := NULL;
END;
cntr := 0;
FOR v_ad_long IN c_ad_long(v_upc.upc_id) LOOP
IF cntr < 1 THEN
weekendvar := v_ad_long.beg_Date;
datevar := v_ad_long.beg_Date;
cntr := 0;
WHILE datevar < v_ad_long.end_date LOOP
SELECT NEXT_DAY(TRUNC(datevar),'saturday')
INTO weekendvar
FROM DUAL;
BEGIN
INSERT INTO SURVEY_WORK_TABLE VALUES (
v_ad_long.buyer_id,
v_upc.upc,
v_upc.upc_id,
weekendvar,
v_upc.item_div,
v_upc.item_nbr,
v_upc.product_description,
v_ad_long.pack,
v_upc.display_size,
v_upc.whse_nbr,
familyvar,
v_ad_long.ad_type,
v_ad_long.beg_date,
v_ad_long.end_date,
v_ad_long.ad_qty,
v_ad_long.ad_amt,
v_ad_long.ad_vehicle,
v_ad_long.survey_exclude_flag,
trunc(sysdate),v_upc.department);
COMMIT;
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
dbms_output.put_line(v_ad_long.buyer_id||','||v_upc.upc||','||
v_upc.upc_id||','||weekendvar);
END;
datevar := datevar + 7;
END LOOP;
cntr := cntr + 1;
ELSE
EXIT ;
END IF;
END LOOP;
FOR v_pend_date_long IN c_pend_date_long (v_upc.upc_id) LOOP
cntr := 0;
FOR v_pend_long IN c_pend_long(v_upc.upc_id,v_pend_date_long.week_end_date) LOOP
IF cntr < 1 THEN
/*
BEGIN
SELECT family_grp_name INTO familyvar FROM FAMILY_GROUP WHERE
family_grp_nbr=v_upc.family_grp_nbr;
EXCEPTION
WHEN NO_DATA_FOUND THEN
familyvar := NULL;
END;
*/
weekendvar := v_pend_long.beg_Date;
datevar := v_pend_long.beg_Date;
cntr := 0;
WHILE datevar < v_pend_long.end_date LOOP
SELECT NEXT_DAY(TRUNC(datevar),'saturday') INTO weekendvar
FROM DUAL;
BEGIN
INSERT INTO SURVEY_WORK_TABLE (
buyer_id,
upc,
upc_id,
week_ending_date,
item_div,
item_nbr,
description,
pack,
display_size,
whse_nbr,
family_group_name,
ad_type,
ad_beg,
ad_end,
ad_qty,
ad_amt,
ad_vehicle,
survey_exclude_flag,
update_date,department )
VALUES (
v_pend_long.buyer_id,
v_upc.upc,
v_upc.upc_id,
weekendvar,
v_upc.item_div,
v_upc.item_nbr,
v_upc.product_description,
v_pend_long.pack,
v_upc.display_size,
v_upc.whse_nbr,
familyvar,
v_pend_long.ad_type,
v_pend_long.beg_date,
v_pend_long.end_date,
v_pend_long.ad_qty,
v_pend_long.ad_amt,
v_pend_long.ad_vehicle,
v_pend_long.survey_exclude_flag,
trunc(sysdate),
v_upc.department);
COMMIT;
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
UPDATE SURVEY_WORK_TABLE
SET ad_type=v_pend_long.ad_type,
ad_beg=v_pend_long.beg_date,
ad_end=v_pend_long.end_date,
ad_qty=v_pend_long.ad_qty,
ad_amt=v_pend_long.ad_amt,
ad_vehicle=v_pend_long.ad_vehicle,
survey_exclude_flag=v_pend_long.survey_exclude_flag
WHERE buyer_id=v_pend_long.buyer_id
AND upc=v_upc.upc
AND upc_id=v_upc.upc_id
AND week_ending_date=weekendvar;
COMMIT;
END;
datevar := datevar + 7;
END LOOP;
cntr := cntr + 1;
ELSE
EXIT ;
END IF;
END LOOP;
END LOOP;
FOR v_pend_date IN c_pend_date (v_upc.upc_id) LOOP
cntr := 0;
FOR v_pend IN c_pend(v_upc.upc_id,v_pend_date.week_end_date) LOOP
IF cntr < 1 THEN
/*
BEGIN
SELECT family_grp_name INTO familyvar FROM FAMILY_GROUP WHERE
family_grp_nbr=v_upc.family_grp_nbr;
EXCEPTION
WHEN NO_DATA_FOUND THEN
familyvar := NULL;
END;
*/
weekendvar := v_pend.beg_Date;
datevar := v_pend.beg_Date;
cntr := 0;
WHILE datevar < v_pend.end_date LOOP
SELECT NEXT_DAY(TRUNC(datevar),'saturday') INTO weekendvar
FROM DUAL;
BEGIN
INSERT INTO SURVEY_WORK_TABLE (
buyer_id,
upc,
upc_id,
week_ending_date,
item_div,
item_nbr,
description,
pack,
display_size,
whse_nbr,
family_group_name,
ad_type,
ad_beg,
ad_end,
ad_qty,
ad_amt,
ad_vehicle,
survey_exclude_flag,
update_date,department )
VALUES (
v_pend.buyer_id,
v_upc.upc,
v_upc.upc_id,
weekendvar,
v_upc.item_div,
v_upc.item_nbr,
v_upc.product_description,
v_pend.pack,
v_upc.display_size,
v_upc.whse_nbr,
familyvar,
v_pend.ad_type,
v_pend.beg_date,
v_pend.end_date,
v_pend.ad_qty,
v_pend.ad_amt,
v_pend.ad_vehicle,
v_pend.survey_exclude_flag,trunc(sysdate),
v_upc.department);
COMMIT;
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
UPDATE SURVEY_WORK_TABLE
SET ad_type=v_pend.ad_type,
ad_beg=v_pend.beg_date,
ad_end=v_pend.end_date,
ad_qty=v_pend.ad_qty,
ad_amt=v_pend.ad_amt,
ad_vehicle=v_pend.ad_vehicle,
survey_exclude_flag=v_pend.survey_exclude_flag
WHERE buyer_id=v_pend.buyer_id
AND upc=v_upc.upc
AND upc_id=v_upc.upc_id
AND week_ending_date=weekendvar;
COMMIT;
END;
datevar := datevar + 7;
END LOOP;
cntr := cntr + 1;
ELSE
EXIT ;
END IF;
END LOOP;
END LOOP;
END LOOP;
--END LOOP;
END;
[EDIT] I've added [CODE] tags for you; next time, please, do it yourself to improve readability.
[Updated on: Tue, 03 April 2007 15:28] by Moderator Report message to a moderator
|
|
|
Goto Forum:
Current Time: Thu Aug 22 05:47:05 CDT 2024
|