本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger 的Oracle技术博客 本文链接地址: Oracle 12c学习系列之—Row limits Invisible Column Oracle 12c 引入了row limits的特性，玩Mysql的人都知道这个，然而Oracle却一直没有这个功能
本站文章除注明转载外，均为本站原创： 转载自love wife & love life —Roger 的Oracle技术博客
本文链接地址: 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: 5473470
----------------------------------------------------------------------------------------
| 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 = = 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 = 671088 = Column is encrypted */
/* 0x20000000 = 536870912 = Column is encrypted without salt */
/* 0x000800000000 = 34359738368 = default with sequence */
/* 0x001000000000 = 68719476736 = default on null */
/* 0x002000000000 = 1374353472 = 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 21474835
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
后面有空还会继续研究和分享Oracle 12c的其他内容，这仅仅是个开始！
Related posts: