oracle12c分页,ArcSDE10.2.1使用Oracle12c新特性分页
在Oracle 12c推出之后，其中一個(gè)新特性就是分頁(yè)語(yǔ)句。 Easy Top-N and pagination queries ，更易用的Top-N和頁(yè)碼查 詢(xún)提供了類(lèi)似MySQL中l(wèi)imit的語(yǔ)法，Row Limiting Clause 注意：該功能是Oracle12c的新特性，并不是ArcGIS的新特性。 語(yǔ)法介紹 row_limiting_
在Oracle 12c推出之后，其中一個(gè)新特性就是分頁(yè)語(yǔ)句。
Easy Top-N and pagination queries ，更易用的Top-N和頁(yè)碼查
詢(xún)提供了類(lèi)似MySQL中l(wèi)imit的語(yǔ)法，Row Limiting Clause
注意：該功能是Oracle12c的新特性，并不是ArcGIS的新特性。
語(yǔ)法介紹
row_limiting_clause
The row_limiting_clause allows you to limit the rows returned by the query. You can specify an offset, and number of rows or percentage of rows to return. You can use this clause to implement top-N reporting. For consistent results, specify the order_by_clause to ensure a deterministic sort order.
OFFSET
Use this clause to specify the number of rows to skip before row limiting begins. offset must be a number. If you specify a negative number, then offset is treated as 0. If you specify NULL, or a number greater than or equal to the number of rows returned by the query, then 0 rows are returned. If offset includes a fraction, then the fractional portion is truncated. If you do not specify this clause, then offsetis 0 and row limiting begins with the first row.
ROW | ROWSThese keywords can be used interchangeably and are provided for semantic clarity.
FETCH
Use this clause to specify the number of rows or percentage of rows to return. If you do not specify this clause, then all rows are returned, beginning at row offset + 1.
FIRST | NEXTThese keywords can be used interchangeably and are provided for semantic clarity.
rowcount | percent PERCENTUse rowcount to specify the number of rows to return. rowcount must be a number. If you specify a negative number, then rowcount is treated as 0. If rowcount is greater than the number of rows available beginning at row offset + 1, then all available rows are returned. If rowcount includes a fraction, then the fractional portion is truncated. If rowcount is NULL, then 0 rows are returned.
Use percent PERCENT to specify the percentage of the total number of selected rows to return. percent must be a number. If you specify a negative number, then percent is treated as 0. If percent is NULL, then 0 rows are returned.
If you do not specify rowcount or percent PERCENT, then 1 row is returned.
ROW | ROWSThese keywords can be used interchangeably and are provided for semantic clarity.
ONLY | WITH TIESSpecify ONLY to return exactly the specified number of rows or percentage of rows.
Specify WITH TIES to return additional rows with the same sort key as the last row fetched. If you specify WITH TIES, then you must specify theorder_by_clause. If you do not specify the order_by_clause, then no additional rows will be returned.
當(dāng)然，大家最喜歡看的就是實(shí)際的例子，總結(jié)上面就是，在Oracle12c
環(huán)境下，提供了更加方便的分頁(yè)語(yǔ)句，而不再使用Rownum對(duì)象了。
測(cè)試環(huán)境：
ArcSDE10.2.1、Oracle12.1.0.1sde@PDBORCL> select count(*) from point;
COUNT(*)
----------
231774
sde@PDBORCL> select count(*) from poly;
COUNT(*)
----------
13
----------------------------------------------------------------------------------
Blog: http://blog.csdn.net/linghe301
----------------------------------------------------------------------------------
1：獲得前N條記錄的語(yǔ)句
語(yǔ)法： fetch first N rows only;sde@PDBORCL> set autotrace on
sde@PDBORCL> select a.objectid,a.poi_name from poly b,point a where sde.st_within(a.shape,b.shape)=1 order by a.objectid fetch first 10 rows only;
OBJECTID POI_NAME
---------- ----------------------------------------------------------------------
1326 96699994
1790 96700095
6511 96700093
8761 96698253
9650 96698338
10186 96699086
10597 96700092
12400 96698659
12443 96700465
14594 96699995
已選擇10行。
執(zhí)行計(jì)劃
----------------------------------------------------------
Plan hash value: 3842462730
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 76 | 55 (0)| 00:00:01 |
|* 1 | VIEW | | 1 | 76 | 55 (0)| 00:00:01 |
|* 2 | WINDOW SORT PUSHED RANK | | 1 | 417 | 55 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 1 | 417 | 55 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | POLY | 13 | 2964 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID | POINT | 1 | 189 | 55 (0)| 00:00:01 |
|* 6 | DOMAIN INDEX (Sel: .0000043)| A6_IX1 | | | 4 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("from$_subquery$_003"."rowlimit_$$_rownumber"<=10)
2 - filter(ROW_NUMBER() OVER ( ORDER BY "A"."OBJECTID")<=10)
6 - access("SDE"."ST_WITHIN"("A"."SHAPE","B"."SHAPE")=1)
統(tǒng)計(jì)信息
----------------------------------------------------------
2099 recursive calls
0 db block gets
4802 consistent gets
17 physical reads
0 redo size
679 bytes sent via SQL*Net to client
359 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
29 sorts (memory)
0 sorts (disk)
10 rows processed通過(guò)SQL的執(zhí)行計(jì)劃可以看出，系統(tǒng)還是通過(guò)Rownum來(lái)進(jìn)行分頁(yè)
的，是否我可以理解Oracle封裝了一下，做了個(gè)二次開(kāi)發(fā)呢？
----------------------------------------------------------------------------------
Blog: http://blog.csdn.net/linghe301
----------------------------------------------------------------------------------
2：獲得第N條開(kāi)始后的M條的語(yǔ)句
語(yǔ)法：offset N rows fetch next M rows only;sde@PDBORCL> select a.objectid from poly b,point a where sde.st_within(a.shape,b.shape)=1 order by a.objectid offset 11 rows fetch next 10 rows only;
OBJECTID
----------
17833
18358
18710
19544
19719
20030
23028
24001
25139
25257
已選擇10行。
執(zhí)行計(jì)劃
----------------------------------------------------------
Plan hash value: 3842462730
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 55 (0)| 00:00:01 |
|* 1 | VIEW | | 1 | 39 | 55 (0)| 00:00:01 |
|* 2 | WINDOW SORT PUSHED RANK | | 1 | 417 | 55 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 1 | 417 | 55 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | POLY | 13 | 2964 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID | POINT | 1 | 189 | 55 (0)| 00:00:01 |
|* 6 | DOMAIN INDEX (Sel: .0000043)| A6_IX1 | | | 4 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("from$_subquery$_003"."rowlimit_$$_rownumber"<=CASE WHEN (11>=0)
THEN 11 ELSE 0 END +10 AND "from$_subquery$_003"."rowlimit_$$_rownumber">11)
2 - filter(ROW_NUMBER() OVER ( ORDER BY "A"."OBJECTID")<=CASE WHEN (11>=0)
THEN 11 ELSE 0 END +10)
6 - access("SDE"."ST_WITHIN"("A"."SHAPE","B"."SHAPE")=1)
統(tǒng)計(jì)信息
----------------------------------------------------------
647 recursive calls
0 db block gets
2481 consistent gets
0 physical reads
0 redo size
457 bytes sent via SQL*Net to client
359 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
10 rows processed關(guān)于Next的用法分為兩步
第一步：先把過(guò)濾第1到第N-1的數(shù)據(jù)
第二步：然后再獲得第N到M的數(shù)據(jù)
----------------------------------------------------------------------------------
Blog: http://blog.csdn.net/linghe301
----------------------------------------------------------------------------------
3：獲得前百分比N的語(yǔ)句
語(yǔ)法： fetch first N percent rows only;sde@PDBORCL> select a.objectid from poly b,point a where sde.st_within(a.shape,b.shape)=1 order by a.objectid fetch first 10 percent rows only;
OBJECTID
----------
1326
1790
6511
8761
9650
10186
10597
12400
12443
14594
15163
17833
18358
18710
19544
19719
已選擇16行。
執(zhí)行計(jì)劃
----------------------------------------------------------
Plan hash value: 2419008321
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 52 | 55 (0)| 00:00:01 |
|* 1 | VIEW | | 1 | 52 | 55 (0)| 00:00:01 |
| 2 | WINDOW SORT | | 1 | 417 | 55 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 1 | 417 | 55 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | POLY | 13 | 2964 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID | POINT | 1 | 189 | 55 (0)| 00:00:01 |
|* 6 | DOMAIN INDEX (Sel: .0000043)| A6_IX1 | | | 4 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("from$_subquery$_003"."rowlimit_$$_rownumber"<=CEIL("from$_subquery$_
003"."rowlimit_$$_total"*10/100))
6 - access("SDE"."ST_WITHIN"("A"."SHAPE","B"."SHAPE")=1)
統(tǒng)計(jì)信息
----------------------------------------------------------
647 recursive calls
0 db block gets
2481 consistent gets
0 physical reads
0 redo size
577 bytes sent via SQL*Net to client
370 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
16 rows processed
從這些語(yǔ)句可以看到，原來(lái)使用比較麻煩的分頁(yè)SQL寫(xiě)法，現(xiàn)在變得
比較簡(jiǎn)單和方便了。
目前來(lái)說(shuō)，我在ArcGIS10.2.1 for Server進(jìn)行Rest測(cè)試，還不支持該
方法，希望Esri能夠盡快改進(jìn)吧。
----------------------------------------------------------------------------------
Blog: http://blog.csdn.net/linghe301
----------------------------------------------------------------------------------
另外需要說(shuō)明一個(gè)問(wèn)題。
很多情況下都會(huì)出現(xiàn)這種現(xiàn)象，比如在一個(gè)班級(jí)里面的數(shù)據(jù)成績(jī)，從
最高排序有100、99、97、97、94等
那么，第一名就是100，第二名就是99，第三名應(yīng)該是并列兩個(gè)97，
那么在上面的語(yǔ)句中我們看到有一個(gè)ONLY關(guān)鍵字。
如果使用ONLY關(guān)鍵字，那么如果用戶(hù)需要獲得排序后的前三名，那么
只有100、99、97，顯然這個(gè)不符合實(shí)際情況，所以用戶(hù)應(yīng)該使用
WITH TIES關(guān)鍵字，這樣雖然獲得前三名，得到的結(jié)果是真實(shí)的四個(gè)
數(shù)據(jù)。sde@PDBORCL> select objectid,num from aaa;
OBJECTID NUM
---------- ----------
1 1
2 2
3 3
4 3
5 3
6 4
7 4
8 5
已選擇8行。
sde@PDBORCL>
sde@PDBORCL> select objectid,num from aaa order by num fetch first 3 rows only;
OBJECTID NUM
---------- ----------
1 1
2 2
3 3
sde@PDBORCL> select objectid,num from aaa order by num fetch first 3 rows with ties;
OBJECTID NUM
---------- ----------
1 1
2 2
3 3
4 3
5 3
----------------------------------------------------------------------------------
Blog: http://blog.csdn.net/linghe301
----------------------------------------------------------------------------------
PS：該功能目前可以在ArcGIS Desktop中使用，但是不能再
REST中使用。
微信
總結(jié)
以上是生活随笔為你收集整理的oracle12c分页,ArcSDE10.2.1使用Oracle12c新特性分页的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。
- 上一篇： 和借呗一样靠谱的网贷 了解这几家就可以
- 下一篇： oracle计费系统相关问题,用radi