oracle12c有limit函数,Oracle 12c学习系列之—Row limits & Invisible Column

本站文章除注明转载外，均为本站原创： 转载自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: 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的其他内容，这仅仅是个开始！

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中文网，转载请注明出处，感谢您的尊重！如有疑问，请联系admin@php.cn处理