Oracle Scratchpad

May 22, 2021

255 columns

Filed under: Infrastructure,Oracle — Jonathan Lewis @ 1:19 pm BST May 22,2021

Here’s a little note about column counts that I’ve not mentioned before now in the various articles I’ve written about the side effects of declaring tables with more than 255 columns. The count is about “real” columns and you should ignore virtual columns – but there are some real columns you might not notice unless you look carefully. Here’s a little quiz, starting with a table definition:

rem
rem     Script:         column_count.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Apr 2021
rem     Purpose:        
rem
rem     Last tested 
rem             19.11.0.0
rem 

create type jpl_row as object (n1 number, v1 varchar2(10));
/

create table t1 (
        n1      number, 
        ot      jpl_row, 
        n2      number
)
;

How many “real” columns are there in table t1?

Let’s ask the data dictionary:

select  column_name, column_id, data_type
from    user_tab_columns
where   table_name = 'T1'
order by 
        column_id
/

COLUMN_NAME           COLUMN_ID DATA_TYPE
-------------------- ---------- -------------------------
N1                            1 NUMBER
OT                            2 JPL_ROW
N2                            3 NUMBER

3 rows selected.

It looks like three, just as we declared, except we’re looking at the wrong bit of the data dictionary:

select
        column_name, 
        column_id,
        segment_column_id,
        data_type,
        hidden_column,
        virtual_column
from
        user_tab_cols
where   table_name = 'T1'
order by 
        column_id
/

COLUMN_NAME           COLUMN_ID SEGMENT_COLUMN_ID DATA_TYPE                 HID VIR
-------------------- ---------- ----------------- ------------------------- --- ---
N1                            1                 1 NUMBER                    NO  NO
OT                            2                 2 JPL_ROW                   NO  NO
SYS_NC00003$                  2                 3 NUMBER                    YES NO
SYS_NC00004$                  2                 4 VARCHAR2                  YES NO
N2                            3                 5 NUMBER                    NO  NO

5 rows selected.

The correct answer is five, and they’re all “real” – check the segment_column_id. I was slightly surprised at this because I had been assuming that the two attributes of the jpl_row type would be real but hidden and the jpl_row type itself to be virtual.

Conclusion

When checking the number of columns in a table, make sure you look at the xxx_tab_cols view – not the xxx_tab_columns to get complete definitions of all the columns, real, virtual, and hidden.

Lagniappe

How many “real” columns does the following statement add to the table definition:

alter table t1 add n3 number default 999;

Here’s the result of repeating my query against user_tab_cols:

COLUMN_NAME           COLUMN_ID SEGMENT_COLUMN_ID DATA_TYPE                 HID VIR
-------------------- ---------- ----------------- ------------------------- --- ---
N1                            1                 1 NUMBER                    NO  NO
SYS_NC00003$                  2                 3 NUMBER                    YES NO
OT                            2                 2 JPL_ROW                   NO  NO
SYS_NC00004$                  2                 4 VARCHAR2                  YES NO
N2                            3                 5 NUMBER                    NO  NO
N3                            4                 7 NUMBER                    NO  NO
SYS_NC00006$                                    6 RAW                       YES NO

7 rows selected.

The mechanics of Oracle’s “fast add” of a default column actually adds two column, one hidden, if the column being added is a nullable column. [See comment #1 below from Adam Leszczyński you get just the one “extra” column no matter how many nullable columns with defaults you subsequently add to the table.]

2 Comments »

  1. I think it i worth to mention that the “fast add” reuses the null constraint SYS_NC00006$. Adding another (and another) column like that would just add one more column. The constraint is just a binary mask – used to distinguish NULL (empty value) from no value (and thus default). One bit per column every “fast added” column – and since it a raw type it can cover multiple column. For example you can test the results of:
    alter table t1 add n4 number default 999;

    Comment by Adam Leszczyński — May 22, 2021 @ 10:56 pm BST May 22,2021 | Reply


RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by WordPress.com.