CODEWITHSUNDEEP
oracle-databaseoracle11gstoragevirtual-column
Rammy
Rammy

Reputation: 31

Oracle 11g Replace Physical Columns with Virtual Columns

After googling for a while , I am posting this question here since I was not able to find such a problem posted anywhere.

Our application has a table with 274 columns(No LOB or Long Raw columns) and over a period of 8 years the table started to have chained rows so any full table scan is impacting the performance.

When we dig deeper we found out that approximately 50 columns are not used anywhere in application and so could be dropped right away. But the challenge here is the application has to undergo many code changes to achieve this and we have exposed the underlying data as a service that is being consumed by other applications as well. So we cannot choose the code change as an option for now.

Another option we thought was, whether I can make these 50 columns as Virtual column set to NULL always, then we only we need to make changes to table loading procs and rest all will be as is. But I need experts' advice whether adding virtual columns to the table will not construct chained rows again. Will this solution work for the given problem statement?

Thanks Rammy

Upvotes: 2

Views: 241

Answers (1)

APC
APC

Reputation: 146199

Oracle only allows 255 columns per block. For tables with more than 255 columns it splits rows into multiple blocks. Find out more.

You table has 274 columns so you have chained rows because of the inherent table structure rather than the amount of space the data takes up. Making fifty columns all null won't change that.

So, if you want to eliminate the chained rows you really need to drop the rows. Of course you don't want to change all that application code. So what you can try is:

  • rename the table
  • drop the columns you don't want any more
  • create a view using the original table name and include NULL columns in the view's projection to match the original table structure.

Upvotes: 1

Related Questions