Best way to add calculated column in an Oracle CREATE TABLE .. SELECT statement

Jim Horn
Jim Horn used Ask the Experts™
on
(Warning:  SQL Server expert, Oracle N00b)

Hello Oracle experts

I have a requirement to build on the currently-in-production (mocked up) code below, adding a column pov_coh_count and populating it with the sum of columns pov_coh_Aa01 through .._Aa09.

CREATE TABLE TARGET PCTFREE 0 NOLOGGING AS
SELECT Column1, Column2, Column3, ColumnN, 
   case when bitand(coh.cohorts, 2) <> 0 then 1 else 0 end pov_coh_Aa01,
   case when bitand(coh.cohorts, 4) <> 0 then 1 else 0 end pov_coh_Aa02,
   case when bitand(coh.cohorts, 8) <> 0 then 1 else 0 end pov_coh_Aa03,
   case when bitand(coh.cohorts, 16) <> 0 then 1 else 0 end pov_coh_Aa04,
   case when bitand(coh.cohorts, 32) <> 0 then 1 else 0 end pov_coh_Aa05,
   case when bitand(coh.cohorts, 64) <> 0 then 1 else 0 end pov_coh_Aa06,
   case when bitand(coh.cohorts, 128) <> 0 then 1 else 0 end pov_coh_Aa07,
   case when bitand(coh.cohorts, 512) <> 0 then 1 else 0 end pov_coh_Aa08,
   case when bitand(coh.cohorts, 1024) <> 0 then 1 else 0 end pov_coh_Aa09
FROM SomeTable 
LEFT OUTER JOIN SomeOtherTable
LEFT OUTER JOIN YetAnotherTable coh
LEFT OUTER JOIN YouGetTheIdea

Open in new window


What would you recommend as the best way?  Calculated column in the SELECT, add the column after the above statement and then UPDATE, ...

Thanks.
Jim
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
Hands-down:  Modify the create table script!!!

CREATE TABLE TARGET PCTFREE 0 NOLOGGING AS
SELECT Column1, Column2, Column3, ColumnN, 
   pov_coh_Aa01,
   pov_coh_Aa02,
   pov_coh_Aa03,
   pov_coh_Aa04,
   pov_coh_Aa05,
   pov_coh_Aa06,
   pov_coh_Aa07,
   pov_coh_Aa08,
   pov_coh_Aa09,
   pov_coh_Aa01+ pov_coh_Aa02+ pov_coh_Aa03+ pov_coh_Aa04+ pov_coh_Aa05+ pov_coh_Aa06+ pov_coh_Aa07+ pov_coh_Aa08+ pov_coh_Aa09 pov_sum
from (
SELECT Column1, Column2, Column3, ColumnN, 
   case when bitand(coh.cohorts, 2) <> 0 then 1 else 0 end pov_coh_Aa01,
   case when bitand(coh.cohorts, 4) <> 0 then 1 else 0 end pov_coh_Aa02,
   case when bitand(coh.cohorts, 8) <> 0 then 1 else 0 end pov_coh_Aa03,
   case when bitand(coh.cohorts, 16) <> 0 then 1 else 0 end pov_coh_Aa04,
   case when bitand(coh.cohorts, 32) <> 0 then 1 else 0 end pov_coh_Aa05,
   case when bitand(coh.cohorts, 64) <> 0 then 1 else 0 end pov_coh_Aa06,
   case when bitand(coh.cohorts, 128) <> 0 then 1 else 0 end pov_coh_Aa07,
   case when bitand(coh.cohorts, 512) <> 0 then 1 else 0 end pov_coh_Aa08,
   case when bitand(coh.cohorts, 1024) <> 0 then 1 else 0 end pov_coh_Aa09
FROM SomeTable 
LEFT OUTER JOIN SomeOtherTable
LEFT OUTER JOIN YetAnotherTable coh
LEFT OUTER JOIN YouGetTheIdea
)

                                

Open in new window


An alternative if you are working in 11g or above:
Virtual columns.

https://oracle-base.com/articles/11g/virtual-columns-11gr1

They are computed when they are queried and not physically stored.  Think about it:  You update one of the column values and you don't have to worry about the total column.
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Author

Commented:
Works for me, and avoids calculating twice.  Thanks.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
I suppose I should ask:
This isn't going to be used as a "temp" table for some other processing?

If so, Oracle doesn't work like other database products that encourage that sort of thing...  there are better alternatives!
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Author

Commented:
Nope.  It's part of a monster Java/Flash UI with a wompload of graphs + Oracle Data Warehouse back-end build that has a wompload of 'dynamic features' in it.  For example, the above created table has a _MM_ for monthly aggregate numbers, _YY_ for yearly aggregate numbers, you get the idea.  My requirement is to create a column that is the sum of 1-9, so the super-dooper graphing UI can use that as a 'sum of the other columns' without having to bundle that into the query SQL.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
You know best but that description sounds a lot like a table that is created "more than once, ever" for specific processing...
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
Jim,

Oracle supports "materialized views".  
Basically you create a view and the result is persisted automatically.

There aren't dead simple to use (but not too hard). Using these might be appropriate for this need.
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Author

Commented:
Understand, but this place recently went from Materialized Views back to tables, even these tables are still prefixed with 'MV_', so the will is not there to create MV's.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
I would strongly suggest you look at virtual columns.

I don't know how much data you are dealing with or the resources on the servers you have BUT....

Check out the example below.

The only physical storage in the new table is the cohort column.  Everything else you need is derived when queried.

3 outcomes from a performance aspect:
1: it will BLOW AWAY the performance of the current design
2: it will bog things down at query time so much the server grinds to a halt
3: No one noticed and they have cupcakes in the break room!!!

If the outcomes are 1 or 3, you will save a TON of storage space!!!

Nothing is free in this world so there is a catch.  It appears virtual columns cannot be used in a CTAS (or at least I couldn't get them to work) so it makes a 1 step process a 3 step process:  Create, insert then commit.

drop table tab1 purge;
create table tab1(cohorts number);

insert into tab1 values(2);
insert into tab1 values(6);
commit;

SELECT 
   pov_coh_Aa01,
   pov_coh_Aa02,
   pov_coh_Aa03,
   pov_coh_Aa04,
   pov_coh_Aa01+ pov_coh_Aa02+ pov_coh_Aa03+ pov_coh_Aa04 pov_total
from (
SELECT 
   case when bitand(coh.cohorts, 2) <> 0 then 1 else 0 end pov_coh_Aa01,
   case when bitand(coh.cohorts, 4) <> 0 then 1 else 0 end pov_coh_Aa02,
   case when bitand(coh.cohorts, 8) <> 0 then 1 else 0 end pov_coh_Aa03,
   case when bitand(coh.cohorts, 16) <> 0 then 1 else 0 end pov_coh_Aa04,
   case when bitand(coh.cohorts, 32) <> 0 then 1 else 0 end pov_coh_Aa05,
   case when bitand(coh.cohorts, 64) <> 0 then 1 else 0 end pov_coh_Aa06,
   case when bitand(coh.cohorts, 128) <> 0 then 1 else 0 end pov_coh_Aa07,
   case when bitand(coh.cohorts, 512) <> 0 then 1 else 0 end pov_coh_Aa08,
   case when bitand(coh.cohorts, 1024) <> 0 then 1 else 0 end pov_coh_Aa09
FROM tab1 coh
)
;



drop table tab1_virt purge;
create table tab1_virt
(
	cohorts number,
   	pov_coh_Aa01	generated always as (case when bitand(cohorts, 2) <> 0 then 1 else 0 end),
   	pov_coh_Aa02	generated always as (case when bitand(cohorts, 4) <> 0 then 1 else 0 end),
   	pov_coh_Aa03	generated always as (case when bitand(cohorts, 8) <> 0 then 1 else 0 end),
   	pov_coh_Aa04	generated always as (case when bitand(cohorts, 16) <> 0 then 1 else 0 end),
   	pov_coh_total	generated always as (
   		case when bitand(cohorts, 2) <> 0 then 1 else 0 end+
   		case when bitand(cohorts, 4) <> 0 then 1 else 0 end+
   		case when bitand(cohorts, 8) <> 0 then 1 else 0 end+
   		case when bitand(cohorts, 16) <> 0 then 1 else 0 end
	)
) 
/

insert into tab1_virt(cohorts) select cohorts from tab1;
commit;

select pov_coh_Aa01, pov_coh_Aa02, pov_coh_Aa03, pov_coh_Aa04, pov_coh_total from tab1_virt;
  	

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial