Oracle12c Learning Series-Rowlimits & InvisibleColumn

In addition to the reprinted content, this article is original site: reposted from lovewifelovelife-Roger's Oracle technology blog. Link: Oracle12c Learning Series-RowlimitsInvisibleColumnOracle12c introduces rowlimits features, anyone playing with Mysql knows this, but Oracle never has this function.

Except for the reprinted content, this article is original site: reposted from the Oracle technology blog of love wife love life-Roger. Link: oracle 12c Learning Series-Row limits Invisible Column Oracle 12c introduces the row limits feature. Anyone playing Mysql knows this, but Oracle never has this feature.

In addition to the reposted content, this site is original:Reposted from the Oracle technology blog of love wife & love life-Roger

Link:Oracle 12c Learning Series-Row limits & Invisible Column

Oracle 12c introduced the row limits feature, which is known to anyone playing with Mysql. However, Oracle has never had this feature, but it was finally implemented in 12c.

SQL> show con_nameCON_NAME------------------------------PDBORCLSQL> create table test_lim as select * from dba_objects;Table created.SQL> select count(1) from test_lim;COUNT(1)----------90929SQL> col owner for a10SQL> col objecT_name for a30SQL> select object_id,owner,object_name from test_lim order by 12  fetch first 5 rows only;OBJECT_ID OWNER      OBJECT_NAME---------- ---------- ------------------------------2 SYS        C_OBJ#3 SYS        I_OBJ#4 SYS        TAB$5 SYS        CLU$6 SYS        C_TS#SQL> l1  select object_id,owner,object_name from test_lim order by 12* fetch first 5 rows onlySQL> /Execution Plan----------------------------------------------------------Plan hash value: 1929006521---------------------------------------------------------------------------------------------| Id  | Operation                | Name     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |---------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT         |          |     5 |   855 |       |  1290   (1)| 00:00:01 ||*  1 |  VIEW                    |          |     5 |   855 |       |  1290   (1)| 00:00:01 ||*  2 |   WINDOW SORT PUSHED RANK|          | 90929 |  3196K|  4288K|  1290   (1)| 00:00:01 ||   3 |    TABLE ACCESS FULL     | TEST_LIM | 90929 |  3196K|       |   426   (1)| 00:00:01 |---------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=5)2 - filter(ROW_NUMBER() OVER ( ORDER BY "OBJECT_ID")<=5)

In addition, you can query a certain number of rows of data, for example, you want to query 100th-110 rows of data.

SQL> select objecT_id, owner, object_name2    from test_lim3   order by 1 offset 110 rows fetch next 10 rows only;OBJECT_ID OWNER      OBJECT_NAME---------- ---------- ------------------------------112 SYS        I_COLTYPE1113 SYS        I_COLTYPE2114 SYS        SUBCOLTYPE$115 SYS        I_SUBCOLTYPE1116 SYS        NTAB$117 SYS        I_NTAB1118 SYS        I_NTAB2119 SYS        I_NTAB3120 SYS        REFCON$121 SYS        I_REFCON110 rows selected.

Note that the offset here is based on the row number (rownum ). If you do not understand the query, you will understand the query as follows:

SQL> select rownum,a.objecT_id, a.owner, a.object_name2    from test_lim a3   order by 1 offset 110 rows fetch next 10 rows only;ROWNUM  OBJECT_ID OWNER      OBJECT_NAME---------- ---------- ---------- ------------------------------111        112 SYS        I_COLTYPE1112        113 SYS        I_COLTYPE2113        114 SYS        SUBCOLTYPE$114        115 SYS        I_SUBCOLTYPE1115        116 SYS        NTAB$116        117 SYS        I_NTAB1117        118 SYS        I_NTAB2118        119 SYS        I_NTAB3119        120 SYS        REFCON$120        121 SYS        I_REFCON110 rows selected.

In addition to the preceding fetch and offset usage, there is also a percent option, as shown below:

SQL> select count(1) from (2  select objecT_id, owner, object_name3    from test_lim a order by 14  fetch first 1 percent rows only);COUNT(1)----------910SQL> select objecT_id, owner, object_name2    from test_lim a3   order by 1 fetch first 0.01 percent rows only;OBJECT_ID OWNER      OBJECT_NAME---------- ---------- ------------------------------2 SYS        C_OBJ#3 SYS        I_OBJ#4 SYS        TAB$5 SYS        CLU$6 SYS        C_TS#7 SYS        I_TS#8 SYS        C_FILE#_BLOCK#9 SYS        I_FILE#_BLOCK#10 SYS        C_USER#11 SYS        I_USER#10 rows selected.

We can return the data with a specified proportion. Note that Oracle is rounded up and not rounded down.

SQL> set autot traceonlySQL> l1  select objecT_id, owner, object_name2    from test_lim a3*  order by 1 fetch first 0.01 percent rows onlySQL> /10 rows selected.Execution Plan----------------------------------------------------------Plan hash value: 547893470----------------------------------------------------------------------------------------| Id  | Operation           | Name     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |----------------------------------------------------------------------------------------|   0 | SELECT STATEMENT    |          | 90929 |    15M|       |  1290   (1)| 00:00:01 ||*  1 |  VIEW               |          | 90929 |    15M|       |  1290   (1)| 00:00:01 ||   2 |   WINDOW SORT       |          | 90929 |  3196K|  4288K|  1290   (1)| 00:00:01 ||   3 |    TABLE ACCESS FULL| TEST_LIM | 90929 |  3196K|       |   426   (1)| 00:00:01 |----------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=CEIL("from$_subquery$_002"."rowlimit_$$_total"*0.01/100))

We can clearly see that the Oracle process is actually a complicated filter operation.

++Invisible column++

In Oracle 11g version, the invisible Index feature is introduced. Further in 12c, the column feature is invisible, that is, the invisible column feature.

SQL> create table test_visible as select owner,object_id2  from dba_objects where object_id < 10;Table created.SQL> alter table test_visible modify (owner invisible);Table altered.SQL> desc test_visibleName                           Null?    Type------------------------------ -------- --------------------------------------------OBJECT_ID                               NUMBERSQL>SQL> select * from test_visible where rownum < 3;OBJECT_ID----------98

As you can see, when the column is modified to invisible (invisible), you desc cannot view the column information. Of course, the select query will not return the data of the column.

SQL> alter table test_visible modify (owner visible);Table altered.SQL> select * from test_visible where rownum < 3;OBJECT_ID OWNER---------- ----------9 SYS8 SYSSQL> alter table test_visible modify (owner invisible);Table altered.SQL> select owner,table_name,column_name,HIDDEN_COLUMN,IDENTITY_COLUMN2  from dba_tab_cols where owner='ROGER' and table_name='TEST_VISIBLE';OWNER      TABLE_NAME           COLUMN_NAME          HID IDE---------- -------------------- -------------------- --- ---ROGER      TEST_VISIBLE         OWNER                YES NOROGER      TEST_VISIBLE         OBJECT_ID            NO  NOSQL> insert into test_visible(objecT_id,owner) values(99999,'killdb.com');1 row created.SQL> commit;Commit complete.SQL> select * from test_visible;OBJECT_ID----------98765432999999 rows selected.SQL>

When column is set to invisible, the data in this column does not change. We can still perform the insert operation.

Here I'm curious about how Oracle is implemented. I think it should also be done by modifying a dictionary attribute of the data dictionary col $. Check that this is indeed the case.

The structure and column name of the col $ data dictionary table in Oracle 12c are described as follows:

create table col$                                            /* column table */( obj#          number not null,             /* object number of base object */col#          number not null,                 /* column number as created */segcol#       number not null,                 /* column number in segment */segcollength  number not null,             /* length of the segment column */offset        number not null,                         /* offset of column */name          varchar2("M_IDEN") not null,               /* name of column */type#         number  not null,                     /* data type of column *//* for ADT column, type# = DTYADT */length        number  not null,               /* length of column in bytes */fixedstorage  number  not null,   /* flags: 0x01 = fixed, 0x02 = read-only */precision#    number,                                         /* precision */scale         number,                                             /* scale */null$         number not null,                     /* 0 = NULLs permitted, *//* > 0 = no NULLs permitted  */deflength     number,              /* default value expression text length */default$      long,                       /* default value expression text *//** If a table T(c1, addr, c2) contains an ADT column addr which is stored* exploded, the table will be internally stored as*              T(c1, addr, C0003$, C0004$, C0005$, c2)* Of these, only c1, addr and c2 are user visible columns. Thus, the* user visible column numbers for (c1, addr, C0003$, C0004$, C0005$, c2)* will be 1,2,0,0,0,3. And the corresponding internal column numbers will* be 1,2,3,4,5,6.** Some dictionary tables like icol$, ccol$ need to contain intcol# so* that we can have indexes and constraints on ADT attributes. Also, these* tables also need to contain col# to maintain backward compatibility.* Most of these tables will need to be accessed by col#, intcol# so* indexes are created on them based on (obj#, col#) and (obj#, intcol#).* Indexes based on col# have to be non-unique if ADT attributes might* appear in the table. Indexes based on intcol# can be unique.*/intcol#       number not null,                   /* internal column number */property      number not null,           /* column properties (bit flags): *//* 0x0001 =       1 = ADT attribute column                   *//* 0x0002 =       2 = OID column                             *//* 0x0004 =       4 = nested table column                    *//* 0x0008 =       8 = virtual column                         *//* 0x0010 =      16 = nested table's SETID$ column           *//* 0x0020 =      32 = hidden column                          *//* 0x0040 =      64 = primary-key based OID column           *//* 0x0080 =     128 = column is stored in a lob              *//* 0x0100 =     256 = system-generated column                *//* 0x0200 =     512 = rowinfo column of typed table/view     *//* 0x0400 =    1024 = nested table columns setid             *//* 0x0800 =    2048 = column not insertable                  *//* 0x1000 =    4096 = column not updatable                   *//* 0x2000 =    8192 = column not deletable                   *//* 0x4000 =   16384 = dropped column                         *//* 0x8000 =   32768 = unused column - data still in row      *//* 0x00010000 =   65536 = virtual column                         *//* 0x00020000 =  131072 = place DESCEND operator on top          *//* 0x00040000 =  262144 = virtual column is NLS dependent        *//* 0x00080000 =  524288 = ref column (present as oid col)        *//* 0x00100000 = 1048576 = hidden snapshot base table column      *//* 0x00200000 = 2097152 = attribute column of a user-defined ref *//* 0x00400000 = 4194304 = export hidden column,RLS on hidden col *//* 0x00800000 = 8388608 = string column measured in characters   *//* 0x01000000 = 16777216 = virtual column expression specified    *//* 0x02000000 = 33554432 = typeid column                          *//* 0x04000000 = 67108864 = Column is encrypted                    *//* 0x20000000 = 536870912 = Column is encrypted without salt       *//* 0x000800000000 = 34359738368 = default with sequence                *//* 0x001000000000 = 68719476736 = default on null                      *//* 0x002000000000 = 137438953472 = generated always identity column    *//* 0x004000000000 = 274877906944 = generated by default identity col   *//* 0x080000000000 = 8796093022208 = Column is sensitive                *//* The spares may be used as the column's NLS character set,* the number of distinct column values, and the column's domain.*//* the universal character set id maintained by NLS group */charsetid     number,                              /* NLS character set id *//** charsetform*/charsetform   number,/* 1 = implicit: for CHAR, VARCHAR2, CLOB w/o a specified set *//* 2 = nchar: for NCHAR, NCHAR VARYING, NCLOB *//* 3 = explicit: for CHAR, etc. with "CHARACTER SET ..." clause *//* 4 = flexible: for PL/SQL "flexible" parameters */evaledition#  number,                                /* evaluation edition */unusablebefore#    number,                      /* unusable before edition */unusablebeginning# number,              /* unusable beginning with edition */spare1        number,                      /* fractional seconds precision */spare2        number,                  /* interval leading field precision */spare3        number,            /* maximum number of characters in string */spare4        varchar2(1000),          /* NLS settings for this expression */spare5        varchar2(1000),spare6        date,spare7        number,spare8        number)

When you look at the properties of the property column, you can see that there is a description of the hidden column, which clearly means invisible.
Next we can observe the changes through queries:

SQL> select owner,objecT_id from dba_objects where object_name='TEST_VISIBLE';OWNER       OBJECT_ID---------- ----------ROGER           91829SQL> select obj#,col#,name,intcol#,property from col$ where obj#=91829;OBJ#       COL# NAME                              INTCOL#            PROPERTY---------- ---------- ------------------------------ ---------- -------------------91829          0 OWNER                                   1         1717988355291829          1 OBJECT_ID                               2               14336SQL> SELECT name, col#, intcol#, segcol#, TO_CHAR(property, 'XXXXXXXXXXXX')2    FROM sys.col$3   WHERE obj# = (SELECT obj# FROM sys.obj$ WHERE name = 'TEST_VISIBLE');NAME                                 COL#    INTCOL#    SEGCOL# TO_CHAR(PROPE------------------------------ ---------- ---------- ---------- -------------OWNER                                   0          1          1     400003820OBJECT_ID                               1          2          2          3800SQL> alter table test_visible modify (owner VISIBLE);Table altered.SQL> SELECT name, col#, intcol#, segcol#, TO_CHAR(property, 'XXXXXXXXXXXX')2    FROM sys.col$3   WHERE obj# = (SELECT obj# FROM sys.obj$ WHERE name = 'TEST_VISIBLE');NAME                                 COL#    INTCOL#    SEGCOL# TO_CHAR(PROPE------------------------------ ---------- ---------- ---------- -------------OWNER                                   2          1          1          3800OBJECT_ID                               1          2          2          3800SQL>

We can see that our guess is correct. If you use dbms_metadata to obtain the table definition, you can also find that an invisible keyword is added to the Hidden Column Oracle:

SQL> select dbms_metadata.get_ddl('TABLE','TEST_VISIBLE') from dual;DBMS_METADATA.GET_DDL('TABLE','TEST_VISIBLE')--------------------------------------------------------------------------------CREATE TABLE "ROGER"."TEST_VISIBLE"(    "OWNER" VARCHAR2(128) INVISIBLE,"OBJECT_ID" NUMBER) SEGMENT CREATION IMMEDIATEPCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255NOCOMPRESS LOGGINGSTORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)TABLESPACE "USERS"

I will continue to study and share other content about Oracle 12c later. This is just the beginning!

Related posts:

1. About partiton column with date or varchar2?
2. Oracle 12c Learning Series-identity column

Except for the reprinted content, this article is original site: reposted from the Oracle technology blog of love wife & love life-Roger. Link: oracle 12c-Row limits & Invisible Column Oracle 12c introduces the feature of row limits. Anyone playing Mysql knows this, but Oracle never has this feature, but it was finally implemented in 12c. SQL> show con_name CON_NAME ---------------------------- pdborcl SQL> create table test_lim as select * from dba_objects; Table created. SQL> select count (1) from test_lim; COUNT (1) ---------- 90929 SQL> col owner for a10 SQL> col objecT_name for a30 SQL> [...]