Oracle 12c Row limits & Invisible Column学习笔记

Oracle 12c 引入了row limits的特性，玩Mysql的人都知道这个，然而Oracle却一直没有这个功能，不过在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> select object_id,owner,object_name from test_lim order by 1
2  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> l
1  select object_id,owner,object_name from test_lim order by 1
2* fetch first 5 rows only
SQL> /
 
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)
而且你还可以查询其中的某几行数据，例如我想查询第100-110 行数据。

SQL> select objecT_id, owner, object_name
2    from test_lim
3   order by 1 offset 110 rows fetch next 10 rows only;
 
OBJECT_ID OWNER      OBJECT_NAME
---------- ---------- ------------------------------
112 SYS        I_COLTYPE1
113 SYS        I_COLTYPE2
114 SYS        SUBCOLTYPE$
115 SYS        I_SUBCOLTYPE1
116 SYS        NTAB$
117 SYS        I_NTAB1
118 SYS        I_NTAB2
119 SYS        I_NTAB3
120 SYS        REFCON$
121 SYS        I_REFCON1
 
10 rows selected.
注意，它这里的offset是根据行号（rownum来的）。如果你这样觉得不明白，这样查询就明白了，如下：

SQL> select rownum,a.objecT_id, a.owner, a.object_name
2    from test_lim a
3   order by 1 offset 110 rows fetch next 10 rows only;
 
ROWNUM  OBJECT_ID OWNER      OBJECT_NAME
---------- ---------- ---------- ------------------------------
111        112 SYS        I_COLTYPE1
112        113 SYS        I_COLTYPE2
113        114 SYS        SUBCOLTYPE$
114        115 SYS        I_SUBCOLTYPE1
115        116 SYS        NTAB$
116        117 SYS        I_NTAB1
117        118 SYS        I_NTAB2
118        119 SYS        I_NTAB3
119        120 SYS        REFCON$
120        121 SYS        I_REFCON1
 
10 rows selected.
除了前面fetch和offset用法之外，还有一个percent选项，如下：

SQL> select count(1) from (
2  select objecT_id, owner, object_name
3    from test_lim a order by 1
4  fetch first 1 percent rows only);
 
COUNT(1)
----------
910
 
SQL> select objecT_id, owner, object_name
2    from test_lim a
3   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.
我们可以返回指定比例的数据，注意，Oracle这里是取整的，而且不是四舍五入的原则。

SQL> set autot traceonly
SQL> l
1  select objecT_id, owner, object_name
2    from test_lim a
3*  order by 1 fetch first 0.01 percent rows only
SQL> /
 
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$_subquer
y$_002"."rowlimit_$$_total"*0.01/100))
可以清楚的看到Oracle的过程，其实是进行了一个复杂的filter操作。
+++++++++ Invisible column +++++++++++++
在Oracle 11g版本中，引入了invisible Index特性，在12c中更进一步，可以让column也不可见，即invisible column特性。

SQL> create table test_visible as select owner,object_id
2  from dba_objects where object_id < 10;
 
Table created.
 
SQL> alter table test_visible modify (owner invisible);
 
Table altered.
SQL> desc test_visible
Name                           Null?    Type
------------------------------ -------- --------------------------------------------
OBJECT_ID                               NUMBER
 
SQL>
SQL> select * from test_visible where rownum < 3;
 
OBJECT_ID
----------
9
8

可以看到，当column被修改为invisible（不可见）之后，你desc都无法查看该column的信息，当然select查询也不会返回该列的数据。

SQL> alter table test_visible modify (owner visible);
 
Table altered.
 
SQL> select * from test_visible where rownum < 3;
 
OBJECT_ID OWNER
---------- ----------
9 SYS
8 SYS
 
SQL> alter table test_visible modify (owner invisible);
 
Table altered.
 
SQL> select owner,table_name,column_name,HIDDEN_COLUMN,IDENTITY_COLUMN
2  from dba_tab_cols where owner='ROGER' and table_name='TEST_VISIBLE';
 
OWNER      TABLE_NAME           COLUMN_NAME          HID IDE
---------- -------------------- -------------------- --- ---
ROGER      TEST_VISIBLE         OWNER                YES NO
ROGER      TEST_VISIBLE         OBJECT_ID            NO  NO
 
SQL> 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
----------
9
8
7
6
5
4
3
2
99999
 
9 rows selected.
 
SQL>
当column被设置为invisible 之后，不代表该列的数据就变化了，我们仍然可以进行insert操作。
这里我比较好奇Oracle是在怎么来实现的，想想也应该是通过修改数据字典col$的某个字典属性来实现，检查发现果然是这样的。
Oracle 12c 版本中col$ 数据字典表的结构和column名称的解释如下：

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
)
大家注意看其中的 property 列的属性，可以发现其中有hidden column的说明，这显然就是invisible的意思。
下面我们可以通过查询来观察下其变化：

SQL> select owner,objecT_id from dba_objects where object_name='TEST_VISIBLE';
 
OWNER       OBJECT_ID
---------- ----------
ROGER           91829
SQL> select obj#,col#,name,intcol#,property from col$ where obj#=91829;
 
OBJ#       COL# NAME                              INTCOL#            PROPERTY
---------- ---------- ------------------------------ ---------- -------------------
91829          0 OWNER                                   1         17179883552
91829          1 OBJECT_ID                               2               14336
 
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                                   0          1          1     400003820
OBJECT_ID                               1          2          2          3800
 
SQL> 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          3800
OBJECT_ID                               1          2          2          3800
 
SQL>

可以看到，我们的猜测是没错的，如果你通dbms_metadata去获取table的定义，其实也能发现对于隐藏列Oracle加了一个invisible关键字：

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 IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
后面有空还会继续研究和分享Oracle 12c的其他内容，这仅仅是个开始！