点击下载
本文文档

当前位置：首页 - 科技 - 知识百科 - 正文

ArcSDE10.2.1使用Oracle12c新特性分页

来源：动视网责编：小采时间：2020-11-09 07:17:58

ArcSDE10.2.1使用Oracle12c新特性分页

ArcSDE10.2.1使用Oracle12c新特性分页:在Oracle 12c推出之后，其中一个新特性就是分页语句。 Easy Top-N and pagination queries ，更易用的Top-N和页码查询提供了类似MySQL中limit的语法，Row Limiting Clause 注意：该功能是Oracle12c的新特性，并不是ArcGIS的新特性。语法介

推荐度：

点击下载本文 文档为doc格式

导读ArcSDE10.2.1使用Oracle12c新特性分页:在Oracle 12c推出之后，其中一个新特性就是分页语句。 Easy Top-N and pagination queries ，更易用的Top-N和页码查询提供了类似MySQL中limit的语法，Row Limiting Clause 注意：该功能是Oracle12c的新特性，并不是ArcGIS的新特性。语法介

在Oracle 12c推出之后，其中一个新特性就是分页语句。 Easy Top-N and pagination queries ，更易用的Top-N和页码查询提供了类似MySQL中limit的语法，Row Limiting Clause 注意：该功能是Oracle12c的新特性，并不是ArcGIS的新特性。语法介绍 row_limiting_

在Oracle 12c推出之后，其中一个新特性就是分页语句。

Easy Top-N and pagination queries ，更易用的Top-N和页码查

询提供了类似MySQL中limit的语法，Row Limiting Clause

注意：该功能是Oracle12c的新特性，并不是ArcGIS的新特性。

语法介绍

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 | ROWS These 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 | NEXT These keywords can be used interchangeably and are provided for semantic clarity.

rowcount | percent PERCENT Use 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 | ROWS These keywords can be used interchangeably and are provided for semantic clarity.

ONLY | WITH TIES Specify 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.

当然，大家最喜欢看的就是实际的例子，总结上面就是，在Oracle12c

环境下，提供了更加方便的分页语句，而不再使用Rownum对象了。

测试环境：

ArcSDE10.2.1、Oracle12.1.0.1

sde@PDBORCL> select count(*) from point;

 COUNT(*)
----------
 231774

sde@PDBORCL> select count(*) from poly;

 COUNT(*)
----------
 13

----------------------------------------------------------------------------------

Blog: http://blog.csdn.net/linghe301

----------------------------------------------------------------------------------

1：获得前N条记录的语句

语法： 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行。


执行计划
----------------------------------------------------------
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)


统计信息
----------------------------------------------------------
 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

通过SQL的执行计划可以看出，系统还是通过Rownum来进行分页

的，是否我可以理解Oracle封装了一下，做了个二次开发呢？

----------------------------------------------------------------------------------

Blog: http://blog.csdn.net/linghe301

----------------------------------------------------------------------------------

2：获得第N条开始后的M条的语句

语法：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行。


执行计划
----------------------------------------------------------
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)


统计信息
----------------------------------------------------------
 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

关于Next的用法分为两步

第一步：先把过滤第1到第N-1的数据

第二步：然后再获得第N到M的数据

----------------------------------------------------------------------------------

Blog: http://blog.csdn.net/linghe301

----------------------------------------------------------------------------------

3：获得前百分比N的语句

语法： 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行。


执行计划
----------------------------------------------------------
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)


统计信息
----------------------------------------------------------
 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

从这些语句可以看到，原来使用比较麻烦的分页SQL写法，现在变得

比较简单和方便了。

目前来说，我在ArcGIS10.2.1 for Server进行Rest测试，还不支持该

方法，希望Esri能够尽快改进吧。

----------------------------------------------------------------------------------

Blog: http://blog.csdn.net/linghe301

----------------------------------------------------------------------------------

另外需要说明一个问题。

很多情况下都会出现这种现象，比如在一个班级里面的数据成绩，从

最高排序有100、99、97、97、94等

那么，第一名就是100，第二名就是99，第三名应该是并列两个97，

那么在上面的语句中我们看到有一个ONLY关键字。

如果使用ONLY关键字，那么如果用户需要获得排序后的前三名，那么

只有100、99、97，显然这个不符合实际情况，所以用户应该使用

WITH TIES关键字，这样虽然获得前三名，得到的结果是真实的四个

数据。

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中使用。