Query on pagination Query [message #631679] |
Sat, 17 January 2015 00:45 |
|
gopal.biswal
Messages: 11 Registered: October 2013 Location: Pune
|
Junior Member |
|
|
Hi All,
We are writing a pagination query for which code in JAVA side needs Count of total rows satisfying the where clause and they would be displaying lot size of records as per the user input.
So we are calculating count and row number by using Oracle Analytical function.
The analytic version of COUNT() with ROW_NUMBER() is taking time where as calculating COUNT(*) and ROW_NUMBER() individually in separate queries is much faster.
Our understadning was that analytic version of count with ROW_NUMBER should be faster as Oracle could able to apply both the Analytic functions in single scan.But this is not happening.
Could anybody please through light on this.
Table tb_etd_non_mkts_pos_op_p contains around 1,50,000 records.
We have NON UNIQUE index on REPORTING_TIMESTAMP
SELECT *
FROM (SELECT
--COUNT(*) OVER () CNT,
Row_number()
OVER (
ORDER BY reporting_timestamp) RN,
V.*
FROM tb_etd_non_mkts_pos_op_p V
WHERE reporting_timestamp >= To_date('2015-01-1', 'YYYY-MM-DD')
AND reporting_timestamp <= To_date('2015-01-16', 'YYYY-MM-DD'))
WHERE rn >= '1'
AND rn <= '60';
Above query takes .1 seconds
SELECT *
FROM (SELECT
COUNT(*) OVER () CNT,
Row_number()
OVER (
ORDER BY reporting_timestamp) RN,
V.*
FROM tb_etd_non_mkts_pos_op_p V
WHERE reporting_timestamp >= To_date('2015-01-1', 'YYYY-MM-DD')
AND reporting_timestamp <= To_date('2015-01-16', 'YYYY-MM-DD'))
WHERE rn >= '1'
AND rn <= '60';
Above query takes 8 seconds.
SELECT Count(*)
FROM tb_etd_non_mkts_pos_op_p V
WHERE reporting_timestamp >= To_date('2015-01-1', 'YYYY-MM-DD')
AND reporting_timestamp <= To_date('2015-01-16', 'YYYY-MM-DD');
Above query takes .2 seconds.
|
|
|
Re: Query on pagination Query [message #631680 is a reply to message #631679] |
Sat, 17 January 2015 01:29 |
Solomon Yakobson
Messages: 3267 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Obviously. Compare:
SQL> explain plan for
2 select * from (select ename,row_number() over(order by empno) rn from emp) where rn >= 1 and rn <= 5;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2801941731
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 280 | 2 (0)| 00:00:01 |
|* 1 | VIEW | | 14 | 280 | 2 (0)| 00:00:01 |
|* 2 | WINDOW NOSORT STOPKEY | | 14 | 140 | 2 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| EMP | 14 | 140 | 2 (0)| 00:00:01 |
| 4 | INDEX FULL SCAN | PK_EMP | 14 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">=1 AND "RN"<=5)
2 - filter(ROW_NUMBER() OVER ( ORDER BY "EMPNO")<=5)
17 rows selected.
SQL> explain plan for
2 select * from (select ename,row_number() over(order by empno) rn,count(*) over() cnt from emp) where rn >= 1 and rn <= 5;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4056191239
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 462 | 2 (0)| 00:00:01 |
|* 1 | VIEW | | 14 | 462 | 2 (0)| 00:00:01 |
| 2 | WINDOW BUFFER | | 14 | 140 | 2 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| EMP | 14 | 140 | 2 (0)| 00:00:01 |
| 4 | INDEX FULL SCAN | PK_EMP | 14 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">=1 AND "RN"<=5)
16 rows selected.
SQL>
Notice that STOPKEY in WINDOW NOSORT STOPKEY step for just ROW_NUMBER query? And filter associated with it:
2 - filter(ROW_NUMBER() OVER ( ORDER BY "EMPNO")<=5)
Optimizer is smart enough to realize that even though in-line view asks to calculate ROW_NUMBER for each row main query only needs first 5. So it creates a plan that stops inline view after getting first 5 ROW_NUMBERs. But optimizer is also smart enough to realize that with second query it can't stop after 5 ROW_NUMBERs since there is COUNT which requires to count all rows, not just first 5 rows. That's why second query will be slower. How much slower? It depends on the ratio between total row count and row count you need. So if in your case total row count is 100,000 and you only need first 50 difference can be significant and you already experienced that.
And one more thing - why are you using '1' and '60'? Do you realize they are strings, not numbers?
SY.
|
|
|
|