Re: Sort Order in Nested query

From: Fergal Taheny <ftaheny_at_gmail.com>
Date: Fri, 1 Feb 2013 11:43:09 +0000
Message-ID: <CAOuMUT5jGoHZZKoroJzzVx64WhcgcPmWg97qW4fffOHtTAHTNA_at_mail.gmail.com>

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_at_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

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Feb 01 2013 - 12:43:09 CET