Speed of pagination.

Hi,

My purpose is to make pagination for a table that selects from a large database.  I need to jump to any page number (if the page size is 99 and I want to display the 111th page, I need to select as fast as possible the rows between10989 and 11088).

I used %VID BETWEEN :from and :to for the select query.  Now, the %VID is available only if I select either from another nested select, or from a view.  %VID does not work if I select directly from a table.

I want to understand whether %VID works fast and in combination with some index.

If I do

    select %VID, COL from (select COL from sometable) ,

I would expect %VID to work fast in case COL is the main key or has some index in `sometable`.  I start from the idea that the nested select will collect only a single column data that is an indexed column. 

I saw discussions on the forums that %VID is slow but the persons who answered there did not explain why it is slow.  I would expect, without a sophisticated optimizer, if %VID is compared with a variable that is indexed,  the pagination with %VID  to work fast.

Is the ROW_NUMBER a faster solution than %VID and if so, why it is faster ?

Kind regards