Home » SQL & PL/SQL » SQL & PL/SQL » Query on pagination Query (Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production)
Query on pagination Query [message #631679] Sat, 17 January 2015 00:45 Go to next message
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 Go to previous messageGo to next message
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.
Re: Query on pagination Query [message #631991 is a reply to message #631680] Thu, 22 January 2015 21:44 Go to previous message
gopal.biswal
Messages: 11
Registered: October 2013
Location: Pune
Junior Member
Hi Solomon,
Sorry for delay response.

Thanks for the feedback.

Regards;
Gopal
Previous Topic: Need help on hierarchial query
Next Topic: Help with REGEXP
Goto Forum:
  


Current Time: Mon Mar 18 23:32:32 CDT 2024