oracle中行号怎么拿,请高手指点如何取出select结果行号

Rows     Row Source Operation

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

11  VIEW

100   COUNT STOPKEY

100    VIEW

100     SORT GROUP BY STOPKEY

1099008      TABLE ACCESS FULL BIG_TABLE

Lastly, we'll do it your way -- here we don't push the rownum down, the chance

for optimization is gone and you run really slow

select *

from ( select p.*, rownum rnum

from ( select owner, object_name, object_type, count(*)

from big_table

group by owner, object_name, object_type

) p

)

where rnum between 90 and 100

call     count       cpu    elapsed       disk      query    current        rows

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

Parse        1      0.00       0.03          0          0          0           0

Execute      2      0.00       0.00          0          0          0           0

Fetch        2     20.15     112.44      24136      14985        184          11

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

total        5     20.15     112.47      24136      14985        184          11

Misses in library cache during parse: 1

Optimizer goal: CHOOSE

Parsing user id: 216

Rows     Row Source Operation

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

11  VIEW

17172   COUNT

17172    VIEW

17172     SORT GROUP BY

1099008      TABLE ACCESS FULL BIG_TABLE

I guess, at the end of the day, it is up to you.  I can only show you that it

is faster so many times.  In the end -- it is your choice.

In your case, this is what I am guessing:

o hz_parties is a view (recognize it from apps)

o its a view that gets the last row before it can get the first

o the number of rows you can see is not significant (maybe a thousand or so,

something that fits in RAM nicely)

o the rownum optimization in your case doesn't do much -- if you see the tkprof,

you'll be able to quantify what it does for you.

In general I can say this:

you would be doing the wrong thing to use "where rnum between a and b" when you

can push the rownum DOWN into the inner query and achieve PHENOMEMAL performance

gains in general.  But again, that is your choice.

nuff said

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

Performance difference  July 25, 2002

Reviewer:  Ken Chiu  from toronto, ON canada

The 1st query below is more than half faster than the 2nd query, please explain

what happened ?

select b.*

(Select * from A Order by A.Id) b

where rownum<100

select * from

(select b.*,rownum rnum

(Select * from A Order by A.Id) b

where rownum<100)

and rnum >= 50

thanks.

Followup:

half faster... Hmmm.... wonder what that means.

I can say that (after fixing your queries) -- My findings differ from yours.  In

my case, big_table is a 1,000,000 row table and I see:

big_table@ORA920.US.ORACLE.COM> set autotrace traceonly

big_table@ORA920.US.ORACLE.COM> select b.*

2  from (Select * from big_table A Order by A.Id) b

3  where rownum<100

4  /

99 rows selected.

Execution Plan

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

0      SELECT STATEMENT Optimizer=CHOOSE (Cost=15735 Card=99 Bytes=141000000)

1    0   COUNT (STOPKEY)

2    1     VIEW (Cost=15735 Card=1000000 Bytes=141000000)

3    2       TABLE ACCESS (BY INDEX ROWID) OF 'BIG_TABLE' (Cost=15735

Card=1000000 Byte

s=89000000)

4    3         INDEX (FULL SCAN) OF 'BIG_TABLE_PK' (UNIQUE) (Cost=2090

Card=1000000)

Statistics

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

0  recursive calls

0  db block gets

19  consistent gets

0  physical reads

0  redo size

9701  bytes sent via SQL*Net to client

565  bytes received via SQL*Net from client

8  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

99  rows processed

big_table@ORA920.US.ORACLE.COM>

big_table@ORA920.US.ORACLE.COM> select * from

2  (select b.*,rownum rnum

3  from (Select * from big_table A Order by A.Id) b

4  where rownum<100)

5  where rnum >= 50

6  /

50 rows selected.

Execution Plan

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

0      SELECT STATEMENT Optimizer=CHOOSE (Cost=15735 Card=99 Bytes=15246)

1    0   VIEW (Cost=15735 Card=99 Bytes=15246)

2    1     COUNT (STOPKEY)

3    2       VIEW (Cost=15735 Card=1000000 Bytes=141000000)

4    3         TABLE ACCESS (BY INDEX ROWID) OF 'BIG_TABLE' (Cost=15735

Card=1000000 By

tes=89000000)

5    4           INDEX (FULL SCAN) OF 'BIG_TABLE_PK' (UNIQUE) (Cost=2090

Card=1000000)

Statistics

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

0  recursive calls

0  db block gets

13  consistent gets

0  physical reads

0  redo size

5667  bytes sent via SQL*Net to client

532  bytes received via SQL*Net from client

5  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

50  rows processed

big_table@ORA920.US.ORACLE.COM>

big_table@ORA920.US.ORACLE.COM> set autotrace off

big_table@ORA920.US.ORACLE.COM> spool off