CODEWITHSUNDEEP
sqlperformanceoracle11g
Avias
Avias

Reputation: 354

Alternatives to UPDATE statement Oracle 11g

I'm currently using Oracle 11g and let's say I have a table with the following columns (more or less)

Table1

And this table has about 1 Billion rows. I would want to update the status column with a specific where clause, let's say



    where transaction_date = somedatehere

What other alternatives can I use rather than just the normal UPDATE statement?

Currently what I'm trying to do is using CTAS or Insert into select to get the rows that I want to update and put on another table while using AS COLUMN_NAME so the values are already updated on the new/temporary table, which looks something like this:



    INSERT INTO TABLE1_TEMPORARY (
        ID, 
        STATUS, 
        TRANSACTION_DATE, 
        TONS_OF_OTHER_COLUMNS)
    SELECT
        ID
        3 AS STATUS,
        TRANSACTION_DATE,
        TONS_OF_OTHER_COLUMNS
    FROM TABLE1
    WHERE
        TRANSACTION_DATE = SOMEDATE

So far everything seems to work faster than the normal update statement. The problem now is I would want to get the remaining data from the original table which I do not need to update but I do need to be included on my updated table/list.

What I tried to do at first was use DELETE on the same original table using the same where clause so that in theory, everything that should be left on that table should be all the data that i do not need to update, leaving me now with the two tables:



    TABLE1 --which now contains the rows that i did not need to update
    TABLE1_TEMPORARY --which contains the data I updated

But the delete statement in itself is also too slow or as slow as the orginal UPDATE statement so without the delete statement brings me to this point.



    TABLE1 --which contains BOTH the data that I want to update and do not want to update
    TABLE1_TEMPORARY --which contains the data I updated

What other alternatives can I use in order to get the data that's the opposite of my WHERE clause (take note that the where clause in this example has been simplified so I'm not looking for an answer of NOT EXISTS/NOT IN/NOT EQUALS plus those clauses are slower too compared to positive clauses)

I have ruled out deletion by partition since the data I need to update and not update can exist in different partitions, as well as TRUNCATE since I'm not updating all of the data, just part of it.

Is there some kind of JOIN statement I use with my TABLE1 and TABLE1_TEMPORARY in order to filter out the data that does not need to be updated?

I would also like to achieve this using as less REDO/UNDO/LOGGING as possible.

Thanks in advance.

Upvotes: 1

Views: 2491

Answers (2)

Thomas W
Thomas W

Reputation: 14164

How about keeping in the UPDATE in the same table, but breaking it into multiple small chunks?

UPDATE .. WHERE transaction_date = somedatehere AND id BETWEEN 0000000 and 0999999
COMMIT
UPDATE .. WHERE transaction_date = somedatehere AND id BETWEEN 1000000 and 1999999
COMMIT
UPDATE .. WHERE transaction_date = somedatehere AND id BETWEEN 2000000 and 2999999
COMMIT

This could help if the total workload is potentially manageable, but doing it all in one chunk is the problem. This approach breaks it into modest-sized pieces.

Doing it this way could, for example, enable other apps to keep running & give other workloads a look in; and would avoid needing a single humungous transaction in the logfile.

Upvotes: 1

kubanczyk
kubanczyk

Reputation: 5941

I'm assuming this is not a one-time operation, but you are trying to design for a repeatable procedure.

Partition/subpartition the table in a way so the rows touched are not totally spread over all partitions but confined to a few partitions.

Ensure your transactions wouldn't use these partitions for now.

Per each partition/subpartition you would normally UPDATE, perform CTAS of all the rows (I mean even the rows which stay the same go to TABLE1_TEMPORARY). Then EXCHANGE PARTITION and rebuild index partitions.

At the end rebuild global indexes.

If you don't have Oracle Enterprise Edition, you would need to either CTAS entire billion of rows (followed by ALTER TABLE RENAME instead of ALTER TABLE EXCHANGE PARTITION) or to prepare some kind of "poor man's partitioning" using a view (SELECT UNION ALL SELECT UNION ALL SELECT etc) and a bunch of tables.

There is some chance that this mess would actually be faster than UPDATE.

I'm not saying that this is elegant or optimal, I'm saying that this is the canonical way of speeding up large UPDATE operations in Oracle.

Upvotes: 1

Related Questions