using the
V$SQL_PLAN_STATISTICS_ALL view1. Full sort / no sort, fetch until last row
Fetch is stopped by the client after last row is retrieved.
SELECT ROWNUM AS rn
, t.*
FROM (SELECT t.*
FROM test_table t
WHERE category_col = :P_CATEGORY
ORDER BY order_col
) t
;
This case cannot be autotraced because of the client activity.
- with category-only index: 4890 logical block reads.
- with concatenated index: 2002 logical block reads.
Look at template.sql to see how this was measured using the V$MYSTAT view.
2. Full sort / numbering, fetch page rows only
SELECT t.*
FROM (
SELECT ROWNUM AS rn
, t.*
FROM (
SELECT t.*
FROM test_table t
WHERE category_col = :P_CATEGORY
ORDER BY order_col
) t
) t
WHERE rn BETWEEN :P_FIRST_ROW AND :P_LAST_ROW
;
5038 logical block reads
3. Top-N query, extended extremal values search
SELECT t.*
FROM (
SELECT ROWNUM AS rn
, t.*
FROM (
SELECT t.*
FROM test_table t
WHERE category_col = :P_CATEGORY
ORDER BY order_col
) t
WHERE ROWNUM <= :P_LAST_ROW
) t
WHERE rn >= :P_FIRST_ROW
;
1006 logical block reads
4. Numbering with analytical functions, fetch relevant rows only,
- another Top-N query form
SELECT t.*
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY order_col) AS rn
, t.*
FROM test_table t
WHERE category_col = 'GGGGGGGGGGGGGGGGGGGG'
) t
WHERE rn BETWEEN 991 AND 1000
;
1008 logical block reads
4/VAR. - Numbering with analytical functions, fetch relevant rows only,
- "PARTITION BY"-clause included -> NOT Top-N
OVER-clause of the analytical function expression in the above query with one of the following:
- ...
OVER (PARTITION BY category_col ORDER BY order_col) - ...
OVER (PARTITION BY 'x' ORDER BY order_col)
5038 logical block reads
5. ROWID index lookup with self join, real "Top-N - Last-M" query
Note: see the article on ROWID issue with IOTs
SELECT i.rn, t.*
FROM (SELECT i.*
FROM (SELECT i.*, ROWNUM AS rn
FROM (
SELECT ROWID AS a_rowid
FROM test_table t
WHERE category_col = :P_CATEGORY
ORDER BY order_col
) i
WHERE ROWNUM <= :P_LAST_ROW
) i
WHERE rn >= :P_FIRST_ROW
) i
, test_table t
WHERE i.a_rowid = t.ROWID
ORDER BY rn
;
16 logical block reads
6. Index based first row value lookup with one page fetch forward
SELECT ROWNUM + :P_FIRST_ROW - 1 AS rn, t.*
FROM (SELECT t.*
FROM test_table t
WHERE category_col = :P_CATEGORY
AND order_col >=
(SELECT order_col
FROM (SELECT order_col, ROWNUM rn
FROM (SELECT order_col
FROM test_table t
WHERE category_col = :P_CATEGORY
ORDER BY order_col
)
WHERE ROWNUM <= :P_FIRST_ROW
)
WHERE rn = :P_FIRST_ROW
) -- first item on page
ORDER BY order_col
) t
WHERE ROWNUM <= :P_PAGE_SIZE
;
19 logical block reads
7. Using a custom domain index
-
Using an operator -
category_page_between- bound to the domain index:SELECT ROWNUM + :P_FIRST_ROW - 1 AS rn , t.* FROM test_table t WHERE category_page('category_col, order_col' , :P_CATEGORY, :P_DIRECTION , :P_PAGE_SIZE, :P_PAGE_NUMBER) > 0 ;
-
Using another operator -
category_page_between- bound to the domain index:SELECT ROWNUM + :P_FIRST_ROW - 1 AS rn , t.* FROM test_table t WHERE category_page_between('category_col, order_col' , :P_CATEGORY, :P_PAGE_SIZE , :P_PAGE_START, :P_PAGE_STOP) > 0 ;
See operator definitions,
helper packaged functions
and/or domain index type specification
on actual operators, parameters and bindings.
a. and b. are very similarly implemented and they are executed in the same way as it can be seen on the execution plans.
16 logical block reads
a. operator -category_page
b. operator -
category_page_between
8.a) Using a table function that returns row numbers and ROWIDs from any table
SELECT i.rn
, t.*
FROM TABLE(category_page_tabfunc_pkg.get_table_page
( 'test_table', 'category_col', :P_CATEGORY
, 'order_col', :P_DIRECTION, :P_PAGE_SIZE
, :P_PAGE_START, :P_PAGE_STOP)
) i
, test_table t
WHERE i.a_rowid = t.ROWID
ORDER BY rn
;
16 logical block reads
8.b) Using a table function that returns entire rows of a specific table
SELECT ROWNUM + :P_FIRST_ROW - 1 AS rn, t.*
FROM (SELECT *
FROM TABLE(get_paged__test_table
( 'category_col', :P_CATEGORY
, 'order_col', :P_DIRECTION, :P_PAGE_SIZE
, :P_PAGE_START, :P_PAGE_STOP)
) t
ORDER BY order_col
) t
;
16 logical block reads
9. Cursor functions
The execution plan and autotrace output is displayed for the following query:
SELECT
category_page_tabfunc_pkg.get_table_page_cursor
( 'test_table', '*', 'category_col', :P_CATEGORY
, 'order_col', :P_DIRECTION, :P_PAGE_SIZE
, :P_PAGE_START, :P_PAGE_STOP)
AS result_set
FROM dual
;
Note:
The function could be executed directly also.
• This is how to call the function in SQL*Plus using a context variable and PL/SQL:
VARIABLE c REFCURSOR;
BEGIN
:c := category_page_tabfunc_pkg.get_table_page_cursor
( 'test_table', '*', 'category_col', :P_CATEGORY
, 'order_col', :P_DIRECTION, :P_PAGE_SIZE
, :P_PAGE_START, :P_PAGE_STOP)
;
END;
/
PRINT c;
• This is how to call the function from Java, a 3GL language:
...
CallableStatement call = connection.prepareCall(
"{ ? = call category_page_tabfunc_pkg.get_table_page_cursor("
+ "'test_table', '<select list>', 'category_col'"
+ ", ?, 'order_col', ?, ?, ?, ?) }");
call.registerOutParameter(1, OracleTypes.CURSOR);
call.setString(2, pCategory);
call.setInt(3, pDirection);
call.setInt(4, pPageSize);
call.setInt(5, pPageStart);
call.setInt(6, pPageStop);
stmt.execute();
ResultSet rs = (ResultSet)stmt.getObject(1);
...
rs.close();
...
16 logical block reads
Note:
The function call is not seen, but the value of the consistent gets statistic testifies that the function has been called.
However I found no way to get this information show up on the autotrace output or on an execution plan as well.
Although I tried nesting the query as a non-merged view and turn on the display of the Projection information.
This resulted in ORA-600 internal error, because of having a REFCURSOR type in the subquery.