DUAL上的等级查询

DUAL上的等级查询

select rownum

from dual

connect by level <= N

minus

select col

from my_table

该查询可以用来找出GAP。

Oracle 9i (9.2.0)

SQL>SELECT rownum from dual connect by level < 100;

 

    ROWNUM

----------

     1

SQL>select * from (SELECT rownum from dual connect by level < 10)

  2  /

 

    ROWNUM

----------

         1

         2

         3

         4

         5

         6

         7

         8

         9

 

9 rows selected.

Oracle 8i (8.1.7)

    SQL>SELECT rownum from dual connect by level < 10;

 

    ROWNUM

----------

         1

SQL>select * from (SELECT rownum from dual connect by level < 10);

ERROR:

ORA-01436: CONNECT BY loop in user data

 

no rows selected

Oracle 10g

SQL> SELECT rownum from dual connect by level < 10;

 

    ROWNUM

----------

         1

         2

         3

         4

         5

         6

         7

         8

         9

 

9 rows selected

 

当然，也可以使用包，如下：

CREATE OR REPLACE

type typ_num is table of number;

/

 

create or replace package numtest is

  function num(p_num in integer)

  return typ_num;

end numtest;

/

 

create or replace package body numtest is

function num(p_num in integer)

return typ_num is

  n typ_num := typ_num();

begin

  n.extend(p_num);

  return(n);

end num;

end numtest;

/

 

select rownum from table(numtest.num(9));

 

    ROWNUM

----------

         1

         2

         3

         4

         5

         6

         7

         8

         9

但是，在执行explain时，我们会发现select from dual时用的资源会更少。