7 Replies Latest reply: Nov 27, 2012 12:33 PM by JustinCave RSS

    A better way than a global temp table to reuse a distinct select?

    946515
      I get the impression from other threads that global temp tables are frowned upon so I'm wondering if there is a better way to simplify what I need to do. I have some values scattered about a table with a relatively large number of records. I need to distinct them out and delete from 21 other tables where those values also occur. The values have a really low cardinality to the number of rows. Out of 500K+ rows there might be a dozen distinct values.

      I thought that rather than 21 cases of:
      DELETE FROM x1..21 WHERE value IN (SELECT DISTINCT value FROM Y)

      It would be better for performance to populate a global temp table with the distinct first:
      INSERT INTO gtt SELECT DISTINCT value FROM Y
      DELETE FROM x1..21 WHERE value IN (SELECT value FROM GTT)

      People asking questions about GTT's seem to get blasted so is this another case where there's a better way to do this? Should I just have the system bite the bullet on the DISTINCT 21 times? The big table truncates and reloads and needs to do so quickly so I was hoping not to have to index it and meddle with disable/rebuild index but if that's better than a temp table, I'll have to make do.

      As far as I understand WITH ... USING can't be used to delete from multiple tables or can it?
        • 1. Re: A better way than a global temp table to reuse a distinct select?
          Jonathan Lewis
          943512 wrote:
          I get the impression from other threads that global temp tables are frowned upon ...
          I think that the screams of outrage usually appear when a question looks like a SQL Server programmer complaining that Oracle doesn't do temporary tables "properly" rather than describing what they want to achieve. In your case you've described a scenario where it seems to be perfectly reasonable to use a global temporary table although, if the cost of the subquery is tiny, many people might say that you should set the isolation level to serializable and repeat the subquery for each delete.

          Oracle does have syntax of the form: "insert into {many tables depending on conditions} select ...", but I don't think it yet has "delete from {many tables depending on conditions} select ..." which is the sort of thing you'd otherwise need.

          Regards
          Jonathan Lewis
          • 2. Re: A better way than a global temp table to reuse a distinct select?
            rp0428
            >
            I have some values scattered about a table with a relatively large number of records. I need to distinct them out and delete from 21 other tables where those values also occur. The values have a really low cardinality to the number of rows. Out of 500K+ rows there might be a dozen distinct values.
            >
            That is a proper use for a GTT.

            However, simple is usually better. If you really only have a dozen distinct values you could
            1. run the query once to get the values
            2. hardcode an IN clause with those values
            3. past the IN clause into your 21 queries.
            IN ('abc', 'def', 'ghi', . . .)
            Hardly worth the trouble of even creating a GTT don't you think?
            • 3. Re: A better way than a global temp table to reuse a distinct select?
              946515
              Sorry, I wasn't clear. This needs to run every time the big table is loaded which is hourly. Scripting the IN clause on the fly per run is prohibitive at my skill level.

              Thanks for the advice, all. I am exploring how to do this all with a stored procedure but I get an table does not exist error. The create stored proc does not realize the first statement creates the temp table so it's OK for the subsequent delete statements to reference it. Hmm.
              • 4. Re: A better way than a global temp table to reuse a distinct select?
                rp0428
                >
                The create stored proc does not realize the first statement creates the temp table so it's OK for the subsequent delete statements to reference it. Hmm.
                >
                Well it a mistake to create tables dynamically like that because then ALL DDL has to be dynamic. It isn't the proc's fault. You can't compile static code that references a table that doesn't exist statically.

                You have no need to create a GTT dynamically in the proc, especially if you are doing hourly processing.

                Just create the GTT once, add any indexes you need and use it in the proc. If you are using the contents to delete from multiple tables you should probably create the GTT as ON COMMIT PRESERVE ROWS.
                • 5. Re: A better way than a global temp table to reuse a distinct select?
                  sb92075
                  943512 wrote:
                  Sorry, I wasn't clear. This needs to run every time the big table is loaded which is hourly. Scripting the IN clause on the fly per run is prohibitive at my skill level.

                  Thanks for the advice, all. I am exploring how to do this all with a stored procedure but I get an table does not exist error. The create stored proc does not realize the first statement creates the temp table so it's OK for the subsequent delete statements to reference it. Hmm.
                  tables that are created using dynamic SQL can only be accessed by dynamic SQL;
                  so keep digging the hole you now find yourself in.
                  • 6. Re: A better way than a global temp table to reuse a distinct select?
                    946515
                    Ah, so Oracle temp tables are temp of data, not temp of table. This must be what confuses all the SQL Server people. OK, well, it's turned into a moot point since it turns out there is a prohibition on temp tables anyway. I'm just going to have to index the thing and do the subqueries each time. Given the low cardinality and that this is a truncate and reload table, this should be a bitmap index if I understand the use of that type correctly, yes?

                    Special thanks to the other poster about digging myself into a hole. What a warm fuzzy feeling that comes from making such helpful remarks!
                    • 7. Re: A better way than a global temp table to reuse a distinct select?
                      JustinCave
                      Almost, but not quite, as efficient as using a temporary table would be to use a PL/SQL collection and FORALL statements and/or referencing the collection in your subsequent statements). Something like
                      DECLARE
                        TYPE value_nt IS TABLE OF y.value%type;
                      
                        l_values value_nt;
                      BEGIN
                        SELECT distinct value
                          BULK COLLECT INTO l_values
                          FROM y;
                      
                        FORALL i IN 1 .. l_values.count
                          DELETE FROM x1
                           WHERE value = l_values(i);
                      
                        FORALL i IN 1 .. l_values.count
                          DELETE FROM x2
                           WHERE value = l_values(i);
                      END;
                      or
                      CREATE TYPE value_nt 
                        IS TABLE OF varchar2(100); -- Guessing at the type of y.value
                      
                      DECLARE
                        l_values value_nt;
                      BEGIN
                        SELECT distinct value
                          BULK COLLECT INTO l_values
                          FROM y;
                      
                        DELETE FROM x1
                         WHERE value = (SELECT /*+ cardinality(v 10) */ column_value from table( l_values ) v );
                      
                        DELETE FROM x2
                         WHERE value = (SELECT /*+ cardinality(v 10) */ column_value from table( l_values ) v );
                      END;
                      Justin