--
Per rectum ad astrum

--
Per rectum ad astrum

-- Create tables
create table TESTBIG
(
  CLIENT       NUMBER not null,
  ORDER_ID     NUMBER not null,
  ORDER_AMOUNT NUMBER not null
);

alter table TESTBIG add constraint TESTBIG_PK primary key (CLIENT, ORDER_ID) using index;

create table TESTTOTALS
(
  CLIENT       NUMBER not null,
  CLIENT_TOTAL NUMBER
);

alter table TESTTOTALS add constraint TESTTOTALS_PK primary key (CLIENT) using index;

-- Populate with data - inserts 1 million rows. Создаёт 128 клиентов
INSERT /*+ APPEND */ INTO testbig
  (client, order_id, order_amount)
SELECT ORA_HASH(id, 127 ) client, id order_id, MOD(id, 1000 ) amt
  FROM (SELECT LEVEL id FROM dual CONNECT BY LEVEL<= 1000000 );
COMMIT;

BEGIN
  DBMS_STATS.gather_table_stats(user,'TESTBIG',method_opt => 'for all indexed columns size auto' ,cascade => true);
  DBMS_STATS.gather_table_stats(user,'TESTTOTALS',method_opt => 'for all indexed columns size auto' ,cascade => true);
END;
/

--
Per rectum ad astrum

--
Per rectum ad astrum

INSERT INTO testtotals 
SELECT client, SUM(order_amount) 
FROM testbig
GROUP BY client

set term off
CREATE OR REPLACE PROCEDURE test1 AS
    iTotalAmt   NUMBER;
/*
    можно было просто NUMBER написать, но захотелось показать 
    тесную интеграцию процедурного кода и структуры данных
*/
    iLastClient testtotals.client%TYPE; 
BEGIN 
    FOR cc IN ( SELECT client, order_amount
                  FROM testbig
                 ORDER BY client)
    LOOP
        If iLastClient IS NULL Then
            iLastClient := cc.Client;
            iTotalAmt   :=  0 ;
        End If;
        
        If iLastClient != cc.Client Then
            UPDATE testtotals SET
                 client_total = iTotalAmt
             WHERE client = iLastClient;
            If SQL%ROWCOUNT =  0  Then
                INSERT INTO testtotals
                       (client, client_total)
                VALUES (iLastClient, iTotalAmt);
            End If;
            iLastClient := cc.Client;
            iTotalAmt   :=  0 ;
        Else
            iTotalAmt := iTotalAmt + cc.order_amount;
        End If;
    END LOOP;
    COMMIT;
END;
/
-- Непосредственно тестирование, запускаем три раза
DELETE FROM testtotals WHERE client <  64 
/
COMMIT
/
set timing on term on
PROMPT Start
EXECUTE test1

set timing off term off
DELETE FROM testtotals WHERE client <  64 
/
COMMIT
/
set timing on term on
EXECUTE test1

set timing off term off
DELETE FROM testtotals WHERE client <  64 
/
COMMIT
/
set timing on term on
EXECUTE test1

set timing off term off
DROP PROCEDURE test1
/
set term on
PROMPT Done