Oracle 12c学习系列之—Row limits & Invisible Column
本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客
Oracle 12c 引入了row limits的特性，玩Mysql的人都知道这个，然而Oracle却一直没有这个功能，不过在12c中终于实现了。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 |
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 行数据。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
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来的）。如果你这样觉得不明白，这样查询就明白了，如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
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选项，如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
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这里是取整的，而且不是四舍五入的原则。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
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特性。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
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查询也不会返回该列的数据。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 |
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名称的解释如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 |
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的意思。
下面我们可以通过查询来观察下其变化：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
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关键字：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
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的其他内容，这仅仅是个开始！
Leave a Reply
You must be logged in to post a comment.