Oracle 12c学习系列之—Row limits & Invisible Column
程序员文章站
2022-06-14 07:57:06
...
本站文章除注明转载外，均为本站原创： 转载自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"
而且你还可以查询其中的某几行数据，例如我想查询第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"
可以清楚的看到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 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
可以看到，当column被修改为invisible（不可见）之后，你desc都无法查看该column的信息，当然select查询也不会返回该列的数据。
SQL> alter table test_visible modify (owner visible);
Table altered.
SQL> select * from test_visible where rownum 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的其他内容，这仅仅是个开始！
Related posts:
- about partiton column with date or varchar2？
- Oracle 12c学习系列之—identity column
本站文章除注明转载外，均为本站原创： 转载自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> [...]
上一篇： php格式化json函数示例代码
下一篇： 如其PHP是用英式英语编写的