This content has been marked as final. Show 9 replies
I will not respond to anyone that uses the word URGENT in a post. Using this word is an attempt to convey to us that your issue is more important than other people's issues and that is disrespectful. It also means that your issue is as important as someone who's house is on fire or who is drowning. Clearly not the case.
After you apologize I, or others, will gladly respond. thank you.
I will however give you one piece of unsolicited advise:
Any table with more than 50 columns should be considered highly suspicious.
255 columns in a table is outrageous.
305 is pure insanity.
And also this:
If we wanted flat files we would still be using COBOL.
I apologize for the word i have used. Many Thanks for the reply.Can you suggest me how to proceed futher in overcoming the problem , since the size is growing daily which equals to 100GB of a single daily partition , we are in pressure to save the storage space cost.
Thanks in Advance
Phani Kumar P
To advise you would require the DDL for the table and the business rules being followed that show what each column is for and its relationship to the other columns in the table.
Generally speaking when I see more than 50 columns in a table, there is the very rare exception, it is because people are thinking horizontally not vertically. Consider the following two tables.
Both can be extended to hold 500 attributes. The first one will add 1000 columns. The second none.
CREATE TABLE bad_design ( row_identifie r NUMBER, attribute1 NUMBER, attribute_val1 VARCHAR2(20), attribute2 NUMBER, attribute_val2 VARCHAR2(20)); CREATE TABLE good_design ( row_identifier NUMBER, attribute_number NUMBER, attribute_value VARCHAR2(20));
Likely what you are experiencing is something like this.
A Boeing 747 contains more than 1.2 million parts. No sane person would ever consider a plane-parts inventory to start with program_id and line_number followed by 1.199 million columns. But that is the thinking that often leads to 305 columns.
If not then you are going to need to either post a lot of information or go back and examine what it is that appears to justify any object having 304 non-relational attributes.
I checked with the application team , and got the confirmation that the table structure cannot be modified right now as it is heading to many dependencies ...invalid .
I want to know if this is the limitation of Oracle 11g with the columns greater than 255 columns cannot achieve good compression ratios.
Phani Kumar P
The Oracle database has no limitation you are going to bump into anytime soon.
Architecture, normalization, and sanity are limits you seem to have bumped into already.
The application team needs to be taken out for retraining.
Fix the tables and build a denormalized view if you must but this appears to be pure lunacy.
Editorial Comment: I love it when application folks, who know nearly nothing about relational databases, are allowed to make bad decisions and then some poor DBA is expected to somehow make it work. Consider looking them straight in the eye and saying the word "NO!"
And if they would like me to explain it to them have them email me.
Compression works at the block level... replacing repeating values within the block with references to that value. With new columns added, I can guess a possible issue: the rows may be sorted differently going into the table, perhaps sorted on one of the new columns as opposed to a previous column.
The way the data is sorted affects the possible repetitions of values within a block... and therefore the degree of compression.
This could be the cause.
According to Note ID 882712.1:
Applies to: Oracle Server - Enterprise Edition - Version: 184.108.40.206 to 220.127.116.11
RESTRICTIONS ASSOCIATED WITH COMPRESSION
1)Compressed tables can only have columns added or dropped if the COMPRESS FOR ALL OPERATIONS option was used.
*2)Compressed tables must not have more than 255 columns.*
3)Compression is not applied to lob segments.
4)Table compression is only valid for heap organized tables, not index organized tables.
5)The compression clause cannot be applied to hash or hash-list partitions. Instead, they must inherit their compression settings from the tablespace, table or partition settings.
6)Table compression cannot be specified for external or clustered tables.
So to answer your question, don't expect an upgrade to 11g to fix compression for tables with more than 255 columns.
Oracle's notes are interesting ... but as Tom Kyte advises ... trust but verify.
Looks like a few more than 255 columns.
SQL> SELECT * FROM v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 18.104.22.168.0 - Production PL/SQL Release 22.214.171.124.0 - Production CORE 126.96.36.199.0 Production TNS for 32-bit Windows: Version 188.8.131.52.0 - Production NLSRTL Version 184.108.40.206.0 - Production SQL> select count(*) from user_tab_cols where table_name = 'XXX_SPLIT'; COUNT(*) ---------- 921 SQL> CREATE TABLE compression_demo 2 COMPRESS FOR ALL OPERATIONS AS 3 SELECT * FROM xxx_split; Table created. SQL> SELECT table_name, compression, compress_for 2 FROM user_tables 3 WHERE table_name LIKE '%DEMO%'; TABLE_NAME COMPRESS COMPRESS_FOR ------------------------------ -------- ------------------ COMPRESSION_DEMO ENABLED FOR ALL OPERATIONS