can oracle types be updated like tables?

Question

I am converting GTT's to oracle types as explained in an excellent answer by APC. however, some GTT's are being updated based on a select query from another table. For example:

UPDATE my_gtt_1 c
   SET (street, city, STATE, zip) = (SELECT src.unit_address,
                                            src.unit_city,
                                            src.unit_state,
                                            src.unit_zip_code
                                       FROM (SELECT mbr.ROWID row_id,
                                                    unit_address,
                                                    RTRIM(a.unit_city) unit_city,
                                                    RTRIM(a.unit_state) unit_state,
                                                    RTRIM(a.unit_zip_code) unit_zip_code
                                               FROM table_1        b,
                                                    table_2          a,
                                                    my_gtt_1 mbr
                                              WHERE type = 'ABC'
                                                AND id = b.ssn_head
                                                AND a.h_id = b.h_id
                                                AND row_id >= v_start_row
                                                AND row_id <= v_end_row) src
                                      WHERE c.ROWID = src.row_id)
 WHERE state IS NULL
    OR state = ' ';

if my_gtt_1 was not a global temporary table but an oracle collection type then is it possible to do updates this complex? Or in these cases we are better off using the global temporary table?

Answer 1

I think this part of APC's answer to your previous question is relevant here:

Global temporary tables are also good if we have a lot of intermediate processing which is just too complicated to be solved with a single SQL query. Especially if that processing must be applied to subsets of the retrieved rows.

You cannot update the in-memory data with an UPDATE statement like you can a GTT; you would need to write procedural code to locate and change the array elements in question.

Answer 2

Hi Omnipresent,

you can not perform set UPDATE operations on object types. You will have to do it row by row, as in:

FOR i IN l_tab.FIRST..l_tab.LAST LOOP
   SELECT src.unit_address,
          src.unit_city,
          src.unit_state,
          src.unit_zip_code
     INTO l_tab(i).street, 
          l_tab(i).city,  
          l_tab(i).STATE,  
          l_tab(i).zip
     FROM (your_query) src;
 END LOOP;

You should therefore try to do all computations at creation time (where you can BULK COLLECT). Obviously, if your process needs many steps you might find that a global temporary table outperforms an in-memory structure.

---

From the last questions you have asked, it seems you are trying to replace all global temporary tables with object tables. I would suggest caution because in general, they are not interchangeable:

• Objects tables are in-memory structures: you don't want to load a million+ rows table into memory. They are mainly used as a buffer: you load a few (100 for example) rows into the structure, perform what you need to do with these rows then load the next batch. You can not easily treat this structure as a regular table: for example you can only search this structure efficiently with the standard indexing key (you cannot search by rowid in your example unless you define the structure to be indexed by rowid).
• Temporary tables on the other hand are very similar to ordinary tables. You can load millions of rows in them, perform joins, complex set operations. You can index the temporary table for further optimization.

In my opinion, the change your are trying to conduct will take a massive overhaul of your logic and it may not perform better. In general, you would not replace GTT with object tables. You may be able to remove GTT with significant gain in performance by using SET operations directly (perform massive UPDATE/DELETE/INSERT on your data directly without a staging table).

I would suggest performing benchmarks before choosing a solution (this is probably what you are doing right now :)