11 Replies Latest reply: Apr 8, 2013 9:33 AM by Mr Lonely RSS

    Temporary table best practice

    Mr Lonely
      Hi ,

      I have one temporary table and that table is being emptied ( delete from table_name) multiple times in a day through a procedure.

      I just wanted to know what is the best practice to handle this scenario considering read time / space of the table / any other advantages/disadvantages?
        • 1. Re: Temporary table best practice
          BluShadow
          What sort of temporary table?

          Is it a Global Temporary Table, which would delete it's contents at the end of a session (or on commit if you specify that)?
          Or is it a regular table?

          Not quite sure what you're asking.
          • 2. Re: Temporary table best practice
            Mr Lonely
            Hi BluShadow ,

            It's not a global temporary table. It's normal table used in the process to run the process on the filtered data.
            • 3. Re: Temporary table best practice
              BluShadow
              Mr Lonely wrote:
              Hi BluShadow ,

              It's not a global temporary table. It's normal table used in the process to run the process on the filtered data.
              So, as a normal table, you have potential contention issues between multiple sessions, unless you provide some specific session identifier in the data.
              Also, as a normal table, in a multi user environment, you cannot truncate the table to remove the data, you'll have to delete it, which is slower.
              Aside from that, it's just a normal table, so no real advantage/disadvantage.

              For me personally, if I needed temporary data to be stored, I'd be looking to use a GTT instead, unless it needed to be used from a stateless session, such as a web based app.

              As we don't know all the details of what you're doing it's hard to say exactly.
              • 4. Re: Temporary table best practice
                Mr Lonely
                It's not a multiuser environment. We prepare some data every 3 hours. So we call one procedure from Unix box using sqlplus every 3 hours. It prepares the data and loads into a final table.

                Please let me know in case any more details are required. I heard from one of my senior that deleting record from a table and inserting into it multiple time will increase the table size everyday. So I am not sure

                1. Whether I should delete all the record from the table
                2. I use truncate using execute immediae
                3. I drop and recreate the table using execute immediate
                4. I drop, purge and recreate the table using execute immediate.

                Also it's not a tracsaction. So even if we lost the temp table's data that not a problem. In next run anyway we will populate the data into it from base tables.
                • 5. Re: Temporary table best practice
                  jeneesh
                  Truncate and Load.

                  Or if possible, use an MVIEW instead of table..
                  • 6. Re: Temporary table best practice
                    APC
                    Mr Lonely wrote:
                    It's not a global temporary table.
                    Why not? Do you have more than one session which needs to view this transient data?

                    The "best practice" regarding temporary tables is to not use them at all but to rely on efficient SQL and/or PL/SQL collections to provide filtered data sets.

                    The next best practice is to use a global temporary table. Unless session state is a constraint.

                    ------------------------

                    While I was typing this answer you responded to Blu's question:
                    It's not a multiuser environment.
                    So, use a global temporary table (if efficient SQL or Pl/SQL collections are not a sufficient approach - it depends what you mean be "preparing" the data).

                    But otherwise
                    1. Whether I should delete all the record from the table
                    2. I use truncate using execute immediae
                    3. I drop and recreate the table using execute immediate
                    4. I drop, purge and recreate the table using execute immediate.
                    TRUNCATE with the REUSE STORAGE option is the least worst of these solutions; it's the quickest and the most space-efficient (and presumably you don't have any troublesome foreign key constraints to hinder you). Dropping and re-creating the table - with or without purge is something to be avoided at all costs.


                    Cheers, APC

                    Edited by: APC on Apr 8, 2013 12:04 PM
                    • 7. Re: Temporary table best practice
                      ascheffer
                      And what's wrong in using truncate table without using execute immediate?
                      And if the volume of your temporary data is changing a lot you can use
                      truncate table my_table drop storage
                      • 8. Re: Temporary table best practice
                        APC
                        ascheffer wrote:
                        And what's wrong in using truncate table without using execute immediate?
                        I presume the need to use EXECUTE IMNMEDIATE is because it needs to be undertaken programmatically. It's the first step in a process that is run several times a day, so it would make sense for it to be part of an autonomic PL/SQL job.

                        Cheers, APC
                        • 9. Re: Temporary table best practice
                          ascheffer
                          When I read "from Unix box using sqlplus every 3 hours" I don't see the need for execute immediate.
                          But of course, when you want to do it in PLSQL.
                          • 10. Re: Temporary table best practice
                            APC
                            Oh, I missed the fact that it was a {font:courier new}cron{font} (or whatever) job. Your comment is right then.

                            Cheers, APC
                            • 11. Re: Temporary table best practice
                              Mr Lonely
                              ascheffer wrote:
                              When I read "from Unix box using sqlplus every 3 hours" I don't see the need for execute immediate.
                              But of course, when you want to do it in PLSQL.
                              " So we call one *procedure* from Unix box using sqlplus every 3 hours".

                              There is some limitation ( I won't say limitation it's the way the system is designed to run multiple process with same Unix Script) on the Unix script. We can call only one procedure and I am not allowed to change it. So I can't add truncate statement before calling the procedure.


                              Thank you all. I never worked with global temporary table. But surely I will explore it more.