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 ASSELECT 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_Aa09FROM SomeTable LEFT OUTER JOIN SomeOtherTableLEFT OUTER JOIN YetAnotherTable cohLEFT OUTER JOIN YouGetTheIdea

CREATE TABLE TARGET PCTFREE 0 NOLOGGING ASSELECT 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_sumfrom (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_Aa09FROM SomeTable LEFT OUTER JOIN SomeOtherTableLEFT OUTER JOIN YetAnotherTable cohLEFT OUTER JOIN YouGetTheIdea)

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.

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!

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!

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.

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.

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_totalfrom (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_Aa09FROM 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;

IT issues often require a personalized solution. With Ask the Experts™, submit your questions to our certified professionals and receive unlimited, customized solutions that work for you.

## Premium Content

You need an Expert Office subscription to comment.Start Free Trial