CREATE TABLE AS SELECT creates non-nullable columns

Question

I am using the CREATE TABLE AS SELECT statement in Oracle 11g to get data from SQL Server 2012 via a database link.

Oracle creates all these tables with non-nullable columns and that causes me problems later when I try to update them.

How can I prevent this behaviour in Oracle and make resulting columns nullable?

score 2 · Answer 1 · answered Dec 30 '15 at 11:48

2

Either create the table manually beforehand, or specify the column names an NULLability in the CTAS statement:

create table blah2 
( 
  ctascolumn1 not null,
  ctascolumn2 null
) 
as 
select col1, col2 from blah;

answered Dec 30 '15 at 11:48

Philᵀᴹ

Do they end up nullable because they come from a database link or is it always the case with CTAS regardless of the source? – Andriy M Dec 30 '15 at 13:31
Always the case, as far as I know – Philᵀᴹ Dec 30 '15 at 13:32
@AndriyM: I think Oracle decides this based on the values. If a column of the query only contains non null values, the table's column is defined with `NOT NULL`. If at least one value in the query result is `null` the column is defined as `NULL` – a_horse_with_no_name Dec 30 '15 at 13:48
@a_horse_with_no_name I doubt it. How would it know if there's a 4 Petabyte dataset? It won't scan the result set once to create, then again to insert – Philᵀᴹ Dec 30 '15 at 13:50
I'm pretty sure Oracle does scan the whole result because otherwise it won't be able to actually copy the data. But although I'm pretty sure I have seen this happening, I can't reproduce this on Oracle 12c – a_horse_with_no_name Dec 30 '15 at 13:58
@a_horse_with_no_name This is normal behaviour, CTAS copies `NOT NULL` constraints, as documented: http://docs.oracle.com/database/121/SQLRF/statements_7002.htm#SQLRF54626 . It is not based on values, and Oracle does not scan the whole table just to find it out, this is really easy to test and confirm with a statement like this `create table ... as select ... from mytable where 1 = 2`. This will create the new table with `NOT NULL` constraints in an instant, without reading any data from the source. – Balazs Papp Jan 05 '16 at 20:59

1 Answers1