I've got a pretty complex select query, but it's otherwise optimized based on the EXPLAIN. I want to create a concrete table from the results of this select. But the challenge is that this is a large table that consists of 70 million records. Two ways of creating this new table, from what I know, would be to do a Create Table As Select (CTAS), or creating the table first and then doing an INSERT INTO.
CTAS seems to be the most performant way to go, but I also wonder about doing this all in one shot for 70 million records. I normally would have thought about doing this in chunks, but I'm not sure if it's possible with CTAS, considering the table is being created at that point. And then the INSERT INTO option has the overhead of all the inserts.
I'd like to know if anyone has done something like this before, or if they had any other pointers for accomplishing this.