Forum Stats

  • 3,824,917 Users
  • 2,260,439 Discussions
  • 7,896,346 Comments

Discussions

How does Max Rows work?

Can you help me understand how max rows works? I usually run queries to get the SQL right and then move the query to another tool like Excel or Power BI. As such, the default max rows of 500 is fine. Occasionally I want to export all the rows. It looks like I have to change max rows to do this? In my testing, the "Fetch and save all" button seems to really be "Fetch and save all up to max rows." If that's the case, I know I'm going to bump up max rows and forget to change it back.

Typically my exports are ~50,000 rows but could be as high a couple million. If I set max rows to 2 million and forget about it, how will it affect query run time and database load for my query development when I just need to see a little data? Will this cause my development queries to run slower and cause more database load? I don't really understand how paging works. Does it download all the data, or only when I page forward? If a high max rows hurts, it would be nice to have a button to fetch and save all that ignores max rows.

Best Answers

  • Christian.Shay -Oracle
    Christian.Shay -Oracle Posts: 2,148 Employee
    edited Mar 21, 2022 3:09PM Answer ✓

    Hi,

    The maxrows setting of -1 means we fetch all rows returned in your query. Otherwise we only return a maximum of whatever you set in the maxrows setting.

    You are correct that "Fetch and save all rows" is a misnomer and it really means "Fetch and save up to maxrows". I hope to get that changed to ignore maxrows, or to prompt about it, in a future release.

    Setting maxrows to -1 or a high number and forgetting about it won't affect you for small queries that return on the order of a few thousand rows. However, for queries somewhat larger than that you may notice a pause before the result is returned. And in the case of a row returning millions of rows it might appear to be a hang. However, we do have a cancel button (the red square on the tab) if you accidentally execute a query that has millions of rows. But it could be a pain if you run a set of scripts that has a query like that buried in it.

    The Page Size setting only affects the display of the data in the grid. We always fetch up to maxrows immediately. Paging has no affect on that. We do not fetch more as you move through the data.

    Hope this helps!

  • Christian.Shay -Oracle
    Christian.Shay -Oracle Posts: 2,148 Employee
    edited Mar 25, 2022 5:05AM Answer ✓

    Thank you for the kind words! 😀

    It is true that the cancel button is not a guarantee that the query will be able to be cancelled (it just sends a message to the database and hopes for the best)

    The problem with changing the button to fetch more than maxrows, is that when you see data in the results window, we have already closed the cursor and moved on. We would have to reexecute the query when you press the button which could end up fetching different data than what you see in the grid.

Answers

  • Christian.Shay -Oracle
    Christian.Shay -Oracle Posts: 2,148 Employee
    edited Mar 21, 2022 3:09PM Answer ✓

    Hi,

    The maxrows setting of -1 means we fetch all rows returned in your query. Otherwise we only return a maximum of whatever you set in the maxrows setting.

    You are correct that "Fetch and save all rows" is a misnomer and it really means "Fetch and save up to maxrows". I hope to get that changed to ignore maxrows, or to prompt about it, in a future release.

    Setting maxrows to -1 or a high number and forgetting about it won't affect you for small queries that return on the order of a few thousand rows. However, for queries somewhat larger than that you may notice a pause before the result is returned. And in the case of a row returning millions of rows it might appear to be a hang. However, we do have a cancel button (the red square on the tab) if you accidentally execute a query that has millions of rows. But it could be a pain if you run a set of scripts that has a query like that buried in it.

    The Page Size setting only affects the display of the data in the grid. We always fetch up to maxrows immediately. Paging has no affect on that. We do not fetch more as you move through the data.

    Hope this helps!

  • User_CF2IC
    User_CF2IC Member Posts: 10 Green Ribbon

    This is a huge help. I would love to see a change on the buttons. It was a little confusing to see "save all rows" being limited by maxrows. A change to the label would help short term, but long term it would be nice to have a button that really fetched all rows. Then you could set maxrows low for query development, and export all rows with the button.

    My brief experience in VS Code and deeper experience in SQL Developer is that the cancel button never helps. My guess is that the SQL has already finished and it is in process of transferring the data. The cancel button doesn't seem to be able to stop the data transfer. The only options that work are to wait or to crash out of the tool.

    By the way, I really appreciate being able to talk directly to an Oracle developer like you! Your support is so much better than what I experience with other tech companies!

  • Christian.Shay -Oracle
    Christian.Shay -Oracle Posts: 2,148 Employee
    edited Mar 25, 2022 5:05AM Answer ✓

    Thank you for the kind words! 😀

    It is true that the cancel button is not a guarantee that the query will be able to be cancelled (it just sends a message to the database and hopes for the best)

    The problem with changing the button to fetch more than maxrows, is that when you see data in the results window, we have already closed the cursor and moved on. We would have to reexecute the query when you press the button which could end up fetching different data than what you see in the grid.

  • User_CF2IC
    User_CF2IC Member Posts: 10 Green Ribbon

    which could end up fetching different data than what you see in the grid.

    For a development tool like VS Code, this isn't a problem. This is a use case where you are fetching a lot of data, so you don't expect it to match. I use the grid to confirm I have the right columns and data. Unless you are aggregating data in the query, you wouldn't even know there was a difference.

  • Ray007
    Ray007 Member Posts: 42 Bronze Badge

    I agree. Re-executing the query to fetch all rows when I click that button would be more correct. Currently, If I want to download all rows, I need to go change my maxrows setting, re-run the query, download the data to file and then remember to change my maxrows setting back.

    I suppose leaving the cursor open is not an option? For example, suppose my maxrows is set to 200. I run a query and 200 rows are pulled into the result sets window. Instead of closing the cursor right away, leave it until I run another query. Then, the download all rows could just keep fetching from the cursor and get all the rows beyond the initial 200 fetched.