Happy New Year everybody.
I have a price table in my system which has multiple prices for each item/product with the date the price became effective.
I have queries throughout the system which need to retrieve the most recent effective price for the date of the transaction.
The easiest way I can find to implement is to have a user-defined function to retrieve the price.
My problem is that many of my queries are accessing large volumes of data (e.g. transactions) and my price table is also quite large - both have millions of records. By using a user-defined Pl/SQL function in my query, I get lots of context-switching between SQL and PL/SQL and my queries do not perform well
here is some sample code, which simplifies my scenario:
drop table xo_stock_trans;
create table xo_stock_trans (item varchar2(25), trans_date date, quantity number(20,4));
insert into xo_stock_trans values('A',TO_DATE('25-DEC-2014','DD-MON-YYYY'), 4);
insert into xo_stock_trans values('A',TO_DATE('27-DEC-2014','DD-MON-YYYY'), -2);
insert into xo_stock_trans values('A',TO_DATE('28-DEC-2014','DD-MON-YYYY'), 5);
insert into xo_stock_trans values('B',TO_DATE('23-DEC-2014','DD-MON-YYYY'), 20);
insert into xo_stock_trans values('B',TO_DATE('26-DEC-2014','DD-MON-YYYY'), -6);
insert into xo_stock_trans values('B',TO_DATE('29-DEC-2014','DD-MON-YYYY'), 15);
/
-- Generate lots more data
BEGIN
-- Generate more trans dates
for r in 1..1000
LOOP
insert into xo_stock_trans
select item, trans_date - r - 7 as trans_date, ROUND(dbms_random.value(1,50),2) as quantity
from xo_stock_trans
where trans_date between TO_DATE('23-DEC-2014','DD-MON-YYYY') AND TO_DATE('29-DEC-2014','DD-MON-YYYY')
and item in ('A','B');
END LOOP;
COMMIT;
-- generate more items
for lt in 1..12
LOOP
-- generate C,D, E, items
INSERT into xo_stock_trans
SELECT chr(ascii(item)+(lt*2)) as item, trans_date, quantity
from xo_stock_trans
where item in ('A','B');
-- generate A1, A2, B1, B2, etc
for nm in 1..10
LOOP
INSERT INTO xo_stock_trans
select item || to_char(nm), trans_date, quantity
from xo_stock_trans
where length(item) = 1;
END LOOP;
COMMIT;
END LOOP;
COMMIT;
END;
/
create index xo_stock_trans_ix1 on xo_stock_trans (item);
create index xo_stock_trans_ix2 on xo_stock_trans (trans_date);
exec dbms_stats.gather_table_stats(ownname =>user, tabname => 'XO_STOCK_TRANS' , estimate_percent => 100, degree => dbms_stats.auto_degree, cascade=>true);
/
drop table xo_prices;
create table xo_prices (item varchar2(25), price_date date, gross_price number(20,4), net_price number(20,4), special_price number(20,4) );
insert into xo_prices values ('A', to_date('01-DEC-2014','DD-MON-YYYY'), 10, 8, 6);
insert into xo_prices values ('A', to_date('25-DEC-2014','DD-MON-YYYY'), 9, 8, 6);
insert into xo_prices values ('A', to_date('26-DEC-2014','DD-MON-YYYY'), 7, 6, 4);
insert into xo_prices values ('B', to_date('01-DEC-2014','DD-MON-YYYY'), 5.50, 4.50, 3);
insert into xo_prices values ('B', to_date('25-DEC-2014','DD-MON-YYYY'), 5.00, 4.00, 3);
insert into xo_prices values ('B', to_date('26-DEC-2014','DD-MON-YYYY'), 3.50, 2.50, 2);
/
-- Generate lots more data
BEGIN
-- Generate more price dates
for r in 1..1000
LOOP
insert into xo_prices
select item, price_date - r - 7 as price_date,gross_price, net_price, special_price
from xo_prices
where price_date between TO_DATE('23-DEC-2014','DD-MON-YYYY') AND TO_DATE('29-DEC-2014','DD-MON-YYYY')
and item in ('A','B');
END LOOP;
COMMIT;
-- generate more items
for lt in 1..12
LOOP
-- generate C,D, E, items
INSERT into xo_prices
SELECT chr(ascii(item)+(lt*2)) as item, price_date, gross_price + (lt*2), net_price + (lt*2), special_price + (lt*2)
from xo_prices
where item in ('A','B');
-- generate A1, A2, B1, B2, etc
for nm in 1..10
LOOP
INSERT INTO xo_prices
select item || to_char(nm), price_date, gross_price, net_price, special_price
from xo_prices
where length(item) = 1;
END LOOP;
COMMIT;
END LOOP;
END;
/
create index xo_prices_ix1 on xo_prices (item, price_date);
exec dbms_stats.gather_table_stats(ownname =>user, tabname => 'XO_PRICES' , estimate_percent => 100, degree => dbms_stats.auto_degree, cascade=>true);
/
create or replace function xo_get_price(I_Item in VARCHAR2, I_Date in DATE, i_Price_type IN VARCHAR2) RETURN NUMBER
IS
-- Function to get most recent effective price prior to the date
CURSOR c_get_prices(P_Item VARCHAR2, P_Date VARCHAR2)
IS
SELECT gross_price, net_price, special_price
FROM XO_PRICES
WHERE item = P_Item
AND price_date <= P_Date
ORDER BY price_date desc; -- most recent price
l_gross_price NUMBER(20,4);
l_net_price NUMBER(20,4);
l_special_price NUMBER(20,4);
BEGIN
OPEN c_get_prices(I_Item, I_Date);
FETCH c_get_prices INTO l_gross_price, l_net_price, l_special_price;
CLOSe c_get_prices;
IF I_Price_Type='GROSS' then return l_gross_price;
ELSIF I_Price_Type= 'NET' then return l_net_price;
ELSIF I_Price_Type= 'SPECIAL' then return l_special_price;
END IF;
END xo_get_price;
/
-- Here is a typical query I am trying to perform
select tr.item, tr.trans_date, tr.quantity
, xo_get_price(tr.item, tr.trans_date, 'GROSS') as gross_price
, xo_get_price(tr.item, tr.trans_date, 'NET') as net_price
, xo_get_price(tr.item, tr.trans_date, 'SPECIAL') as special_price
from xo_stock_trans tr
where tr.trans_date between '01-AUG-2014' and '31-AUG-2014';
i would like to refactor my query so that I do not use Pl/SQL user functions, but so far I cannot get something which performs better than the above SQL. For example, the following query takes MUCH longer:
select tr.item, tr.trans_date, tr.quantity
, pr.gross_price
, pr.net_price
, pr.special_price
from xo_stock_trans tr
join xo_prices pr on pr.item = tr.item
and pr.price_date = (select max(pr2.price_date)
from xo_prices pr2
where pr2.item = pr.item
and pr2.price_date <= tr.trans_date
)
where tr.trans_date between '01-AUG-2014' and '31-AUG-2014';
I am interested in whether anybody has addressed a similar scenario and have managed to write better performing code.
I have looked at determinism/manual caching of the function, but the item/date combinations are fairly unique and therefore it does not benefit from it.
Any suggestion considered - parallelism, analytic functions, pipelining, etc.
Alan