2

I prolly think - I am hoping against hope. But I am wanted to see if there's a glimmer here .... When you create a VT like this 

create multiset volatile table VT ,  no fallback,no log as   
( sel
 A.1, B.2 from A Join B on <conditon > 
  ) 
with data Primary Index (____) on commit preserve rows ;

The table columns are always NULLABLE even though the columns that form the VT are NOT NULL. So the in the above e.g. A.1 B.2 are NOT NULL columns as per A and B's DDL ( or I can write a filter condition in the sel clause to weed out NULLS ). In either case the VT table design is not intelligent to sense that A.1 & B.2 are NOT NULL so keep them that way in the VT.

Why does it matter ?

Because making a Not null column nullable causes null checking and processing overhead when the VT is joined in the subsequent query . So I am forced to grab the DDL using a "with No data" statement and add NOT NULL to the DDL and then issue a separate CT statement

Am I missing something or that is "just the way it is "

GregGalloway
  • 11,355
  • 3
  • 16
  • 47
user1874594
  • 2,277
  • 1
  • 25
  • 49
  • 1
    If you review the SQL Data Definition Language Manual, Chapter 5: CREATE TABLE (AS Clause), under the section labeled Using Subqueries To Customize An AS Clause you will find that if you do not specify column descriptors, the system will only copy data types of the source columns or resultant data types used in the subquery. So, "just the way it is" seems to apply here. – Rob Paller Nov 13 '15 at 18:03
  • 1
    Yes exactly .As I knew about how the CT AS works.. but looking at this an ominous situation which would be shared by many - I thought it be nice if there was a "smarter" CT AS which'd inc. the "column sensing" logic... that's why I began that as "hoping against hope" and it quite turned out that way. Thank You , Rob, for your help and being responsive. – user1874594 Nov 13 '15 at 18:15
  • If it were to Strictly copy "data types of the source columns " it'd have kept those Not Nulls as they were originally. Anyways ..not much you & me and do over there right now. ..its "just the way it is" ....like the song goes.. – user1874594 Nov 13 '15 at 18:19

1 Answers1

4

If you know ahead of time which columns are not nullable then you should be able specify them in the table definition as follows:

   create multiset volatile table VT ,  no fallback,no log 
    (Col1 NOT NULL, Col2 NOT NULL)
    as   
    ( sel
     A.1, B.2 from A Join B on <conditon > 
      ) 
    with data Primary Index (____) on commit preserve rows ;
Rob Paller
  • 7,736
  • 29
  • 26
  • I am aware of that . But that is not implicit table creation. These tables have more than 200 columns. Its easier to grab the DDL with "no data" option and add the NOT NULL Bit and create rather than use column ordering. Gees that is tougher ..cos you have something like `( c1 datatype not null, C2 Datatype , C3 )` .. Once you use column ordering you have to mention it ALL the way for EVERY column , which is a coding nightmare. – user1874594 Nov 13 '15 at 17:34
  • See comment on your original question. – Rob Paller Nov 13 '15 at 18:03