Discussions
Categories
- 196.8K All Categories
- 2.2K Data
- 238 Big Data Appliance
- 1.9K Data Science
- 450.2K Databases
- 221.7K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 550 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 544 SQLcl
- 4K SQL Developer Data Modeler
- 187K SQL & PL/SQL
- 21.3K SQL Developer
- 295.8K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.5K Development Tools
- 107 DevOps
- 3.1K QA/Testing
- 646K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 154 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 18 Java Essentials
- 160 Java 8 Questions
- 86K Java Programming
- 80 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 437 LiveLabs
- 38 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 171 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 232 Portuguese
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
-
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!
-
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
-
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!
-
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!
-
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.
-
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.
-
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.