t6_zd.sql

-- ZADANIE 1
CREATE OR REPLACE PROCEDURE test_timing_of_queries_z1 (
    p_repeat NUMBER
) IS

    timestart    TIMESTAMP;
    timeend      TIMESTAMP;
    timesecond   NUMBER;
    TYPE t_queries_type IS
        TABLE OF VARCHAR2(4000) INDEX BY VARCHAR2(20);

  -- declare a variable of the t_capital_type
    t_queries    t_queries_type;
    l_index      VARCHAR2(20);
BEGIN
    t_queries('PARTITION BY') := 'SELECT
                                        soh.salesorderheaderid
                                        , soh.customerid
                                        , soh.orderdate
                                        , round(max(soh.orderdate) over (partition by soh.customerid) - soh.orderdate, 0) days_date_diff
                                    FROM
                                        salesorderheader soh'
                                    ;
    t_queries('CORRELATED QUERY') := 'SELECT
                                            soh.salesorderheaderid
                                            , soh.customerid
                                            , soh.orderdate
                                            , round((select max(sohin.orderdate)last_order_date from salesorderheader sohin where sohin.customerid = soh.customerid) - soh.orderdate, 0) days_date_diff
                                        FROM
                                            salesorderheader soh';
    t_queries('JOIN') := 'SELECT
                                soh.salesorderheaderid
                                , soh.customerid
                                , soh.orderdate
                                , round(sohin.last_order_date - soh.orderdate, 0) days_date_diff
                            FROM
                                salesorderheader soh
                                join (select customerid, max(orderdate) last_order_date from salesorderheader group by customerid) sohin on soh.customerid = sohin.customerid'
    ;
    l_index := t_queries.FIRST;

    dbms_output.put_line('Running queries: ' || p_repeat || ' times');

    WHILE l_index IS NOT NULL LOOP
        timestart := systimestamp;
        FOR x IN 1..p_repeat LOOP EXECUTE IMMEDIATE ( t_queries(l_index) );
        END LOOP;

        timeend := systimestamp;
        timesecond := ( ( EXTRACT(HOUR FROM timeend) * 3600 ) + ( EXTRACT(MINUTE FROM timeend) * 60 ) + EXTRACT(SECOND FROM timeend
        ) ) - ( ( EXTRACT(HOUR FROM timestart) * 3600 ) + ( EXTRACT(MINUTE FROM timestart) * 60 ) + EXTRACT(SECOND FROM timestart
        ) );

        dbms_output.put_line('finished '
                             || l_index
                             || ': '
                             || timesecond
                             || ' seconds');

        l_index := t_queries.NEXT(l_index);
    END LOOP;

END test_timing_of_queries_z1;
/

SET SERVEROUTPUT ON

EXECUTE test_timing_of_queries_z1(100000);

SET TIMING ON;
--Example output:
--Running queries: 100000 times
--finished CORRELATED QUERY: 2.309 seconds
--finished JOIN: 6.306 seconds
--finished PARTITION BY: 1.277 seconds


-- ZADANIE 2
CREATE OR REPLACE FUNCTION fn_rtn_tbl_z2_v3 (
    pcustomerid         NUMBER := NULL
    , porderdatefrom      DATE := NULL
    , porderdateto        DATE := NULL
    , pshipdatefrom       DATE := NULL
    , pshipdateto         DATE := NULL
    , pdeliverydatefrom   DATE := NULL
    , pdeliverydateto     DATE := NULL
) RETURN rtn_table_z2 AS

    TYPE dnm_rt IS RECORD (
        customerid           NUMBER(38, 0)
        , salesorderdetailid   NUMBER(38, 0)
        , salesorderheaderid   NUMBER(38, 0)
        , productid            NUMBER(5, 0)
        , qty                  NUMBER(38, 0)
        , totalprice           NUMBER
        , orderdate            DATE
        , shipdate             DATE
        , deliverydate         DATE
    );
    lv_rec      dnm_rt;
    lv_rtn      rtn_table_z2 := rtn_table_z2();
    lv_cursor   SYS_REFCURSOR;
    lv_sql      VARCHAR(1000) := 'SELECT
                                    soh.customerid
                                    , sod.salesorderdetailid
                                    , sod.salesorderheaderid
                                    , sod.productid
                                    , sod.qty
                                    , sod.totalprice
                                    , soh.orderdate
                                    , soh.shipdate
                                    , soh.deliverydate
                                FROM
                                    salesorderdetail   sod
                                    JOIN salesorderheader   soh ON sod.salesorderheaderid = soh.salesorderheaderid
                                WHERE
                                    1=1'
    ;
BEGIN
    CASE
        WHEN pcustomerid IS NOT NULL THEN
            lv_sql := lv_sql
                      || ' and customerid = '
                      || pcustomerid;
        WHEN porderdatefrom IS NOT NULL THEN
            lv_sql := lv_sql
                      || ' and OrderDateFrom >= '
                      || porderdatefrom;
        WHEN porderdateto IS NOT NULL THEN
            lv_sql := lv_sql
                      || ' and OrderDateTo <= '
                      || porderdateto;
        WHEN pshipdatefrom IS NOT NULL THEN
            lv_sql := lv_sql
                      || ' and ShipDateFrom >= '
                      || pshipdatefrom;
        WHEN pshipdateto IS NOT NULL THEN
            lv_sql := lv_sql
                      || ' and ShipDateTo <= '
                      || pshipdateto;
        WHEN pdeliverydatefrom IS NOT NULL THEN
            lv_sql := lv_sql
                      || ' and DeliveryDateFrom >= '
                      || pdeliverydatefrom;
        WHEN pdeliverydateto IS NOT NULL THEN
            lv_sql := lv_sql
                      || ' and DeliveryDateTo <= '
                      || pdeliverydateto;
        ELSE
            NULL;
    END CASE;

    OPEN lv_cursor FOR lv_sql;
    LOOP
        FETCH lv_cursor INTO lv_rec;
        EXIT WHEN lv_cursor%NOTFOUND;
        lv_rtn.extend;
        lv_rtn(lv_rtn.LAST) := rtn_type_z2(lv_rec.customerid, lv_rec.salesorderdetailid, lv_rec.salesorderheaderid
        , lv_rec.productid, lv_rec.qty, lv_rec.totalprice, lv_rec.orderdate, lv_rec.shipdate, lv_rec
        .deliverydate);
    END LOOP;
    CLOSE lv_cursor;

    RETURN lv_rtn;
END fn_rtn_tbl_z2_v3;
/

CREATE OR REPLACE FUNCTION fn_rtn_tbl_z2_v2 (
    pcustomerid         NUMBER := NULL
    , porderdatefrom      DATE := NULL
    , porderdateto        DATE := NULL
    , pshipdatefrom       DATE := NULL
    , pshipdateto         DATE := NULL
    , pdeliverydatefrom   DATE := NULL
    , pdeliverydateto     DATE := NULL
) RETURN rtn_table_z2 AS

    TYPE dnm_rt IS RECORD (
        customerid           NUMBER(38, 0)
        , salesorderdetailid   NUMBER(38, 0)
        , salesorderheaderid   NUMBER(38, 0)
        , productid            NUMBER(5, 0)
        , qty                  NUMBER(38, 0)
        , totalprice           NUMBER
        , orderdate            DATE
        , shipdate             DATE
        , deliverydate         DATE
    );
    TYPE dnm_rtt IS
        TABLE OF dnm_rt INDEX BY PLS_INTEGER;
    lv_recs     dnm_rtt;
    lv_rtn      rtn_table_z2 := rtn_table_z2();
    lv_cursor   SYS_REFCURSOR;
    lv_sql      VARCHAR(1000) := 'SELECT
                                    soh.customerid
                                    , sod.salesorderdetailid
                                    , sod.salesorderheaderid
                                    , sod.productid
                                    , sod.qty
                                    , sod.totalprice
                                    , soh.orderdate
                                    , soh.shipdate
                                    , soh.deliverydate
                                FROM
                                    salesorderdetail   sod
                                    JOIN salesorderheader   soh ON sod.salesorderheaderid = soh.salesorderheaderid
                                WHERE
                                    1=1'
    ;
BEGIN
    CASE
        WHEN pcustomerid IS NOT NULL THEN
            lv_sql := lv_sql
                      || ' and customerid = '
                      || pcustomerid;
        WHEN porderdatefrom IS NOT NULL THEN
            lv_sql := lv_sql
                      || ' and OrderDateFrom >= '
                      || porderdatefrom;
        WHEN porderdateto IS NOT NULL THEN
            lv_sql := lv_sql
                      || ' and OrderDateTo <= '
                      || porderdateto;
        WHEN pshipdatefrom IS NOT NULL THEN
            lv_sql := lv_sql
                      || ' and ShipDateFrom >= '
                      || pshipdatefrom;
        WHEN pshipdateto IS NOT NULL THEN
            lv_sql := lv_sql
                      || ' and ShipDateTo <= '
                      || pshipdateto;
        WHEN pdeliverydatefrom IS NOT NULL THEN
            lv_sql := lv_sql
                      || ' and DeliveryDateFrom >= '
                      || pdeliverydatefrom;
        WHEN pdeliverydateto IS NOT NULL THEN
            lv_sql := lv_sql
                      || ' and DeliveryDateTo <= '
                      || pdeliverydateto;
        ELSE
            NULL;
    END CASE;

    OPEN lv_cursor FOR lv_sql;
    FETCH lv_cursor BULK COLLECT INTO lv_recs;
    CLOSE lv_cursor;

    FOR indx IN 1..lv_recs.COUNT LOOP
        lv_rtn.extend;
        lv_rtn(lv_rtn.LAST) := rtn_type_z2(lv_recs(indx).customerid, lv_recs(indx).salesorderdetailid, lv_recs(indx).salesorderheaderid
        , lv_recs(indx).productid, lv_recs(indx).qty, lv_recs(indx).totalprice, lv_recs(indx).orderdate, lv_recs(indx).shipdate, lv_recs
        (indx).deliverydate);

    END LOOP;

    RETURN lv_rtn;
END fn_rtn_tbl_z2_v2;
/

SELECT
    *
FROM
    fn_rtn_tbl_z2_v2 ( 1 );
SELECT
    *
FROM
    fn_rtn_tbl_z2_v3 ( 1 );
--Example Results:
--Running queries: 100 times
--finished SIMPLE LOOP: 7.766 seconds
--finished WITH BULK INTO: 3.416 seconds

-- ZADANIE 3

CREATE OR REPLACE PROCEDURE test_timing_of_queries_z3 (
    p_repeat NUMBER
) IS

    timestart    TIMESTAMP;
    timeend      TIMESTAMP;
    timesecond   NUMBER;
    TYPE t_queries_type IS
        TABLE OF VARCHAR2(4000) INDEX BY VARCHAR2(20);

  -- declare a variable of the t_capital_type
    t_queries    t_queries_type;
    l_index      VARCHAR2(20);
BEGIN
    t_queries('NOT_EXISTS') := 'select p.productid from product p where NOT EXISTS (select sod.productid from salesorderdetail sod where sod.productid = p.productid)'
    ;
    t_queries('NOT IN') := 'select p.productid from product p where p.productid not in (select productid from salesorderdetail)';
    t_queries('JOIN') := 'select distinct p.productid from product p left join salesorderdetail sod on sod.productid = p.productid'
    ;
    t_queries('MINUS') := 'select productid from Product minus select productid from salesorderdetail';
    l_index := t_queries.FIRST;

    dbms_output.put_line('Running queries: ' || p_repeat || ' times');

    WHILE l_index IS NOT NULL LOOP
        timestart := systimestamp;
        FOR x IN 1..p_repeat LOOP EXECUTE IMMEDIATE ( t_queries(l_index) );
        END LOOP;

        timeend := systimestamp;
        timesecond := ( ( EXTRACT(HOUR FROM timeend) * 3600 ) + ( EXTRACT(MINUTE FROM timeend) * 60 ) + EXTRACT(SECOND FROM timeend
        ) ) - ( ( EXTRACT(HOUR FROM timestart) * 3600 ) + ( EXTRACT(MINUTE FROM timestart) * 60 ) + EXTRACT(SECOND FROM timestart
        ) );

        dbms_output.put_line('finished '
                             || l_index
                             || ': '
                             || timesecond
                             || ' seconds');

        l_index := t_queries.NEXT(l_index);
    END LOOP;

END test_timing_of_queries_z3;
/

SET SERVEROUTPUT ON

EXECUTE test_timing_of_queries_z3(900000);

SET TIMING ON;

--Example output:
--Running queries: 900000 times
--finished JOIN: 19.699 seconds
--finished MINUS: 13.106 seconds
--finished NOT IN: 18.417 seconds
--finished NOT_EXISTS: 18.3 seconds