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

(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
LVL 67
Jim HornSQL Server Data DudeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) 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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim HornSQL Server Data DudeAuthor Commented:
Works for me, and avoids calculating twice.  Thanks.
slightwv (䄆 Netminder) 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!
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Jim HornSQL Server Data DudeAuthor 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.
slightwv (䄆 Netminder) 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 AdvisorCommented:
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 DudeAuthor 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.
slightwv (䄆 Netminder) 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

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.