Forum Stats

  • 3,781,447 Users
  • 2,254,521 Discussions
  • 7,879,706 Comments

Discussions

need example of analytic function single pass

2»

Answers

  • OU_230
    OU_230 Member Posts: 83 Blue Ribbon
    edited Sep 27, 2017 7:05PM

    mathguy,

    Why are you saying 2nd query returned 11 rows?

    Although the 'estimated rows' in explain plan is 11  for both the queries,  the 'rows selected' as well as auto trace stat 'rows processed' is showing 43859.  In addition,  predicate information for both the explain plan has " access("DSCLR_PK">7941071)"

    I have  privilege to create table. 

    " With your current setup it is possible that most of the time goes into selecting the 43k rows to begin with, which will be the same for both queries."

              Are you saying most time is  spent on FETCH rather than EXECUTE?  Wouldn't  the multipass SQL with no analytical function, access  43K rows TWICE so it should be  slower?

This discussion has been closed.