I have one table with 10 millions rows for example
create table table 1
Now I am trying to create a table 2 and
create table table2
(SELECT 1,column2 from Table 1)WITH DATA;
So will my table2 have no issues and contain the same rows as in table1 or Do need I need to define the primary Index on coulmn_2 to fix the skewness.
Please do let me know
In the second case where you did not define PI, the value 1 becomes the PI of the table and will be skewed on a single AMP. Also the syntax is wrong.
Create table table2 as (select 1,column2 from table1) with data; This creates the table with all the 10 million rows on a single AMP and will be skewed as the PI is one the first column of table2 which obviously has only one value 1. The table will be created but will take a lot of time to get created. instead define the column2 as PI of table2
Yes, it will be exactly as krishaneesh suggested.
If this is not part of your automated batch, you can create Table2 WITH NO DATA option and then get the DDL and change it ro suit our need.
CREATE TABLE using SELECT will not pick a correct PI, whereas CREATE TABLE Table2 AS Table1 .... will create exactly same PI.
Just to add on Krishaneesh and Adeel Chaudhry's comments.
Create table using
create table table2 as (select 1 column1,column2 from table1) with data primary index (column_2);
in the drived table give column name without fail otherwise you will end up with 5506 error (in the above example (1 column_1)).