I have a view:
CREATE OR REPLACE VIEW v_Foo0 AS SELECT lotsofstuff FROM Bar INNER JOIN LotsOfOtherJoins CREATE TABLE old_Foo AS SELECT * FROM v_Foo LIMIT 0; CREATE OR REPLACE VIEW v_Foo AS SELECT * FROM v_Foo0 WHERE stuff NOT IN (SELECT stuff FROM old_Foo); CREATE TABLE Foo AS SELECT * FROM v_Foo LIMIT 0;
then I have DML which runs periodically in a procedure, and the normal way could be this:
BEGIN TRANSACTION; INSERT INTO old_Foo SELECT * FROM Foo; TRUNCATE TABLE Foo; INSERT INTO Foo SELECT * FROM v_Foo; ... INSERT INTO Foo SELECT * FROM old_Foo WHERE (stuff) NOT IN (SELECT stuff FROM old_Foo) TRUNCATE TABLE old_Foo; ... COMMIT;
The point is Foo is huge, and being re-built, incrementally, based on new stuff not already in Foo. Then lots of other stuff happens, then whatever from the old_Foo is not already in the rebuilt Foo will get copied into the rebuilt Foo and then old_Foo can be deleted.
But there is one unnecessary copy operation where 10s of millions of rows get shuffled around just to hang on to the old stuff. I would prefer just renaming Foo to old_Foo then rebuild the new Foo:
BEGIN TRANSACTION; ALTER TABLE Foo RENAME TO old_Foo; CREATE TABLE Foo AS SELECT * FROM v_Foo LIMIT 0; INSERT INTO Foo SELECT * FROM v_Foo; ... INSERT INTO Foo SELECT * FROM old_Foo WHERE (stuff) NOT IN (SELECT stuff FROM old_Foo) DROP TABLE old_Foo; ... COMMIT;
Problem is, I can't rename Foo nor drop old_Foo because they are referenced in the v_Foo views. And I want that view referencing whatever table is called Foo at the time it's executed, not cling to the table that was called Foo when the view was defined.
The reason for having this view is that it's a multi screen page full query, I don't want to put that into some procedure to be redefined every time. I just want that view there and when time to rebuild this Foo comes, I want it to do its thing.
And in the real world there are many tables like Foo, and I just don't want to redefine these views over and over again verbatim like that in a crazy 5000 line procedure.
Been thinking if I could use a partitioned table, but as soon as I change the partition key, I would have to do an UPDATE which would translate into the same copying of the millions of rows into the old_Foo partition. So nothing would be gained.