Forum Stats

  • 3,781,590 Users
  • 2,254,530 Discussions
  • 7,879,763 Comments

Discussions

Function for private temp table

foxhound
foxhound Member Posts: 24 Red Ribbon

I have a sql script with logic that inserts data to several private temporary tables where amount of data is controlled by values from ora$ptt_props and then later the rows from those private temp tables is duplicated, again controlled by values from ora$ptt_props, to actual tables in the db. The script has several anonymous blocks which execute parts of the job.

The problem I have is to avoid code duplication when accessing values from ora$ptt_props private temp table. Below is an simplified example of the script:

CREATE PRIVATE TEMPORARY TABLE ora$ptt_props

(

   col1 NUMBER,

   col2 NUMBER,

   col3 NUMBER

)

ON COMMIT DROP DEFINITION;


INSERT INTO ora$ptt_props

VALUES

(

   10000,

   10,

   34

);


CREATE OR REPLACE FUNCTION get_col1

RETURN NUMBER

IS col1_val NUMBER;

BEGIN

 SELECT col1

 INTO col1_val

 FROM ora$ptt_props;


 RETURN(col1_val);

END;

/


CREATE OR REPLACE FUNCTION get_col2

RETURN NUMBER

IS col2_val NUMBER;

BEGIN

 SELECT col2

 INTO col2_val

 FROM ora$ptt_props;


 RETURN(col2_val);

END;

/


CREATE PRIVATE TEMPORARY TABLE ora$ptt_realtab1

ON COMMIT DROP DEFINITION

AS

(

   SELECT

       realcol1, realcol2, realcol3

   FROM realtab1

   WHERE 1=2

);


CREATE PRIVATE TEMPORARY TABLE ora$ptt_realtab2

ON COMMIT DROP DEFINITION

AS

(

   SELECT

       realcol12, realcol22, realcol32

   FROM realtab2

   WHERE 1=2

);



-- preparing patterns in private temp tables

DECLARE

   v_patternamount NUMBER;

BEGIN

      v_patternamount := getcol1 / get_col2;


       FOR i in 1..v_patternamount LOOP

           INSERT INTO ora$ptt_realtab1

               (realcol1, realcol2, realcol3)

           VALUES

               ('realval1' || i, 'realcol2, 'realcol3');


           INSERT INTO ora$ptt_realtab2

               (realcol12, realcol22, realcol32)

           VALUES

               ('realval2' || i, 'realcol2, 'realcol2');

       END LOOP;


END;

/


-- populating real tables in bulk inserts

BEGIN


       FOR i in 1..get_col2() LOOP

           INSERT INTO realtab1

         SELECT

            realcol1 || i, realcol2, realcol3

            FROM ora$ptt_realtab1


          INSERT INTO realtab2

         SELECT

            realcol12 || i, realcol22, realcol32

            FROM ora$ptt_realtab2


       END LOOP;


END;

/


The above sql code will fail because of functions compilation errors - stored function can't see private temporary table. This is obvious. But how can I implement it to have something like function defined once for the whole sql script that will remain for the time of the transaction like private temporary table does?

I can use SELECT INTO in each anonymous block but this is unnecessary code duplication in my opinion that is error prone.

Tagged:

Answers

  • Paulzip
    Paulzip Member Posts: 8,539 Blue Diamond

    The only way to have a function referring to a PTT is to make the function dynamic SQL and create the PTT with dynamic SQL as part of the function. Which is messy.

    I think you're overthinking it. Personally, I can't see anything wrong with SELECT INTO in anonymous block : It's a solution to the restrictions of permanent objects accessing PTT.

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,385 Red Diamond

    Hi, @foxhound

    Thanks for posting a test script, but make it a complete test script. Include CREATE TABLE and INSERT statements for all tables used (e.g. relatab1). Also, it seems to have mismatched single-quotes:

    -- preparing patterns in private temp tables

    ...

               INSERT INTO ora$ptt_realtab1

                   (realcol1, realcol2, realcol3)

               VALUES

                   ('realval1' || i, 'realcol2, 'realcol3');

    INSERT INTO ora$ptt_realtab2

                   (realcol12, realcol22, realcol32)

               VALUES

                   ('realval2' || i, 'realcol2, 'realcol2');

    Fix that before trying to go any farther.

    But how can I implement it to have something like function defined once for the whole sql script that will remain for the time of the transaction like private temporary table does?

    There's nothing like a private temporary function in Oracle, as far as I know.

    Why do you need private temporary tables? Why not use global temporary tables?

    If you must use private temporary tables, then stored procedures can access them using dynamic SQL. As always, think carefully about your requirements before spending any time on dynamic SQL.

    Your front end may have some features that help. In SQL*Plus, for example, you can use the @filename feature to simulate stored procedures.

  • Mike Kutz
    Mike Kutz Member Posts: 5,947 Silver Crown

    Global Temporary Tables are still private in the sense that access to the data in a GTT is Session based.

    AFAIK- Private Temporary Tables are more for analyzing data in a Read Only database (eg a standby).

    (Understand how Oracle uses GTTs and PTTs seems to be a stumbling step for SQL Server developers)

    I don't know if it's possible, but try defining the functions in the DECLARE section of the appropriate anonymous PL/SQL block.

    {place example code here}


  • foxhound
    foxhound Member Posts: 24 Red Ribbon
    edited Nov 3, 2021 2:07PM

    GTTs are stored in temporary tablespace, then on disk when PTT are pure in memory structures. Then much faster and automatically dropped after transaction finishes. That fits exactly in my needs here.

    Well in general there is nothing wrong with SELECT INTO in an anonymous block. But in this particular case I have more than one and when I will use it then script will become very difficult to maintain and error prone in case of any changes. Usually in such situation functions are used to provide consistency, you make change in one place and it is propagated to all anonymous blocks automagically.

    In context of the code as I wrote this is only simplified example when the actual script is long and complex. Among others that is why I provided this simplified form just to draw the problem.

  • User_H3J7U
    User_H3J7U Member Posts: 814 Gold Trophy
    edited Nov 3, 2021 3:44PM

    GTTs are stored in temporary tablespace, then on disk

    The physical io is buffered in memory and can be skiped.

    simplified example

    This example does the inserts into two tables. PTT and pl/sql are not needed.

  • Mike Kutz
    Mike Kutz Member Posts: 5,947 Silver Crown

    K.I.S.S.

    As a developer, you need to weigh the improvements gained by using more complicated, not normally used features against the increased cost of code maintenance.

    Your concern about Code Maintenance suggests that Code Maintenance will out weight the assumed* increased speed by using a PTT.

    (*)assumed - I don't believe you've actually benchmarked the performance difference between a PTT and a GTT. Most of us think it will be negligible.

  • foxhound
    foxhound Member Posts: 24 Red Ribbon
    edited Nov 3, 2021 7:36PM

    Actually I did quick benchmark for different methods of generating 50k rows.

    • recursive CTE took 2.10s
    • PTT depends on values for number of loops iterations, best was 1.6s
    • GTT almost same as above
    • separate INSERT per row in a loop 11s

    Oracle 19c7

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,385 Red Diamond

    Hi, @foxhound

    PTT depends on values for number of loops iterations, best was 1.6s

    GTT almost same as above

    So, there's no significant difference in performance between private and global temporary tables, and it's easier to work with global temporary tables. That makes the decision simple, doesn't it?

    Mike Kutz
  • Mike Kutz
    Mike Kutz Member Posts: 5,947 Silver Crown

    That makes the decision simple, doesn't it?

    Foxhound still needs to decide if 1.5s vs 2.1s is worth maintaining a GTT in the first place.

    Since I mostly do APEX applications on ATP Free Tier, the 0.6s difference is easily hidden within the slowness of the web.

    On the other hand, if I'm using the same CTE data over and over again, the GTT usually wins.