Re: Sort Order in Nested query
- From: Fergal Taheny <ftaheny@xxxxxxxxx>
- To: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
- Date: Fri, 1 Feb 2013 11:43:09 +0000
Ok I'll try to explain without putting everyone to sleep.
I need to take transactions from the transaction_stage table which doesn't
have a primary key and load them into the transaction table.
I must generate key values for the transactions (transaction.id)
I need to assign batch_ids to the transactions so the transactions are
batched by customer and the sum of the values in a batch can't be greater
than 10.
I think this is called the a bin packing problem.
I don't need optimal batching. i.e. If I use a few more batches than the
minimum possible then that's ok.
I resorted to PL/SQL to do this.
declare
V_RUNNING_TOTAL NUMBER := 0;
V_LAST_CUSTOMER transaction_stage.CUSTOMER_ID%TYPE;
V_BATCH_SEQ NUMBER;
V_FIRST_LOOP BOOLEAN := TRUE ;
V_transaction transaction%rowtype;
begin
select batch_id_seq.nextval into V_BATCH_SEQ from dual;
FOR R_transaction_stage IN (select CUSTOMER_ID,VALUE from
transaction_stage order by CUSTOMER_ID) LOOP
SELECT transaction_pk_seq.nextval INTO V_transaction.ID FROM DUAL;
V_transaction.CUSTOMER_ID := R_transaction_stage.customer_id;
V_transaction.value := R_transaction_stage.value;
V_RUNNING_TOTAL := V_RUNNING_TOTAL + V_transaction.value;
IF V_FIRST_LOOP THEN
V_FIRST_LOOP := FALSE ;
ELSE
IF (V_LAST_CUSTOMER != V_transaction.CUSTOMER_ID or
V_RUNNING_TOTAL > 10) THEN
select batch_id_seq.nextval into V_BATCH_SEQ from dual;
V_RUNNING_TOTAL := V_transaction.value;
END IF;
END IF;
v_transaction.batch_id := V_BATCH_SEQ;
V_LAST_CUSTOMER := V_transaction.CUSTOMER_ID;
insert into transaction values V_transaction;
end loop;
end;
/
So I get what I want:
SQL*Plus <sys@ACBSOLT8> select * from transaction_stage
2 ;
CUSTOMER_ID VALUE
----------- ----------
1 5
1 6
2 2
2 6
2 3
select * from transaction;
ID CUSTOMER_ID VALUE BATCH_ID
---------- ----------- ---------- ----------
1 1 6 1
2 1 5 2
3 2 6 3
4 2 3 3
5 2 2 4
Now I hate doing anything in PL/SQl and I'll try to rewrite it maybe using
model sql.
But I was having a think about how to make the pl/sql less bad.
1. change it to a bulk insert. should definitely help
2. I though moving this
SELECT transaction_pk_seq.nextval INTO V_transaction.ID FROM DUAL;
out of the loop and into the cursor to avoid context switches.
but then I ended up with
select transaction_pk_seq.nextval,v.* from (select CUSTOMER_ID,VALUE from
transaction_stage order by CUSTOMER_ID)
which lead me to ask the question.
Regards,
Fergal
--
//www.freelists.org/webpage/oracle-l
Other related posts:
