Regardless of your role or responsibility, if you work in the database, you’re going to spend a lot of time looking at data. This is usually what will push someone from the command-line to a GUI – the spreadsheet look and feel for browsing tables or query results. Since you’re going to be spending so much time in these grids, let’s make sure you know the basics.
I know you’re not writing very many SELECT * queries, but some of your friends might be. If you want to filter out the data client side, then the grids can help with that. For this blog I’ll be using my iTunes data I showed you how to import in an earlier post.
Filtering Columns
We can hide columns in the data grid display. So you can query for everything up front and decide to trim out the white noise later. I really wish you’d write better SELECTs, but I have to personally admit to being guilty of this. So when I forget about the awesome Query Builder interface that lets me after-the-fact click into columns to be written in the SELECT, I can still remove columns from the results panel in the worksheet.
Right-mouse-click on an column header will give me this context menu
And choosing ‘Columns…’ gives me this
Hiding columns does two things for me. One, it makes the data easier to browser. And B, it affects what is sent to PDF, HTML, XLS, etc. when you do an export. So you could say the Grids are WYSIWYG.
Let’s test that. All I want in my PDF is the Song title, album name, and time.
I’m also going to re-order the columns (using drag and drop on the column headers) and sorting by song NAME (double-clicking on the column header.)
Here is the PDF created
Ok, that’s half the battle. Now let’s look at filtering the DATA (or rows) we want shown in the grids.
Filtering Rows
Again, I can’t stress enough that we should be doing the bulk of our filtering SERVER side. This means writing good WHERE or HAVING clauses in our queries. Let the big beefy servers do that work. Less network traffic, less memory and CPU required on your machine, yada, yada, yada…
Ok, but if the data already happens to be in SQL Developer, let’s look at how to filter by values in a particular cell.
You can use the same column header right-mouse-click and choose ‘Filter Column’, or you can LEFT click on the column header. Either way you’ll get a filter popup that looks like this:
I can click into a value and it will auto-filter on that value. This is pretty trivial – MOST of the time. When is it not trivial? When you want to filter on a GUID, a really hard to type field, or when working with DATE. Being able to click into a date and filter on that can be easier than writing a TO_CHAR or TO_DATE in your WHERE clause filters. I say it’s worth learning, but until you do, feel free to use the SQL Developer filters 🙂
Instead of clicking, you can also enter any valid WHERE clause. You don’t have to enter the WHERE or the column name, you can just enter the qualifier.
1 – the Funnel icon appears in any column that’s being filtered
2 – open the filter, then click on ‘Remove’ to clear the filter
You can also mouse-right-click on the column headers again and choose ‘Remove All Filters.’ Note that this will only remove your filters and not bring back any columns that have been hidden.
If you have added a bunch of filters and you can’t quite remember what the filter exactly was on a particular column, you can simply mouse-over the funnel in the column header
This is all great, but I don’t even know where my data is!
That’s OK, the grids have a Search mechanism. It’s hot wired to Ctrl+F, the standard ‘Find’ shortcut. Just make sure the focus is on the grid, else you’ll launch a worksheet editor search/replace dialog.
You’ll notice that I checked the ‘Persist Highlight’ option. This means if I close SQL Developer and come back to this data in a few weeks, the highlighting will remain! You can add as many of these as you would like. They can be toggled on or off or removed permanently. I learned this particular ‘trick’ just a few weeks ago at one of our SQL Developer Dev Days workshops.
If you want to attend one of these events, the next one is being held in Denver in coordination with RMOUG. It’s free for anyone that can get there (the workshop, not the RMOUG event itself.)
20 Comments
How do we filter in SQL Developer VS code, the Column filter option is missing
For the moment, add a WHERE clause to your queries.
The filters are there in the table/view editors/data panel.
Thanks for the tip – very useful the “filtering rows” feature.
This is one of the very first tips/blog posts I ever wrote on SQL Developer. I’m glad it helped you, some 12 years later 🙂
I want the filter above the columns in data tab to stick. If I leave the table or close Oracle SQL Developer I want to go back to that table and still have the query there. It should stay until I remove it. I have to go back and forth a lot and it is a lot of work to save query in editor. Especailly since I can not have multiple schema browsers open at once.
why do you need multiple schema browsers? you can open a table or many tables, as many as you want at a time
Thank you Jeff! I did not think I could have multiple tables open. I googled how to do it and found your link explaining how to do so with the red pin.
Is there a way to make a table filter stick? At work we are automatically logged out if no activity in 15 minutes. Or many times I log out at the end of the day and the next time I log in I do not want to type in the same filter again and again. Would also be nice to have commented off lines in the filter. I may not need it today but will tomorrow. Thank you!
Does this help?
Yes is helps!!!!!! I work with this all day almost every day. Our employer is removing Toad from our machines so we have to learn Oracle SQL Server.
I really appreciate you taking the time to help me. I will pass the tip on to my co-workers.
Hi Jeff,
Is there is “reset” for the column hiding ? Best explained with an example:
– my table has columns a,b,c, …z
– I do “select *” then (say) hide columns ‘a’, ‘b’, ‘c’, and ‘j’ through ‘p’.
– Now I want all my columns back again
If I click “<<" to return them to the list, they are added back at the bottom (which makes sense).
But can I get them back into 'a' through 'z' ?
Cheers,
Connor
You can manually move them up or down, or you could right-click, delete persisted settings
Wow, superb blog format! How lengthy have you ever been bloggng for?
you make running a blog look easy. Thee total glance of your site is wonderful, let alone the content!
After I filtered and sorted the results many times is it possible to generate a select statement from the results based on the filters and sorts?
Hi Jeff,
Great article.
I am wondering if i have a schema of 300 tables but when i am working on a query and i know the 3 or 4 tables that i want to work with. I would like to hide all other tables to avoid clutter.
The reason is quite simple, i want to study the PK/FK relationships and the column names that i ma interested in.
Wondering if you could provide me a solution to hide the other tables temporarily from the GUI.
Any chance your 3 or so tables have a common column name? You can use the ‘Find DB Object’ search results as a navigator
http://www.thatjeffsmith.com/archive/2012/09/search-and-browse-database-objects-with-oracle-sql-developer/
My problem is finding the common column names , which is why i am trying to look at the column definitions of only those tables near by each other as opposed to scrolling up & down in the Oracle sql developer.
Thank You Jeff.
That answers my questions. Thanks for your time.
Regards,
Kiran
Great! Let us know if you have any questions going forward!
Hi Thanks for your explanation !!!
I’m using SQL Developer to connect to Oracle 11g database which is remote from my local laptop.
I have few questions. 🙂
Q) when I execute “select * from ” in SQL Developer, it shows the data in the grid (first 20 rows.) and when again I scroll down till the end, it is taking some few seconds to display another set of 20 rows and the grid is filled up with 20+20=40 rows and again I scroll down till the end it is taking some few seconds and it gives another set of 20 rows in few seconds.. and so on …..
so WHAT is happening here???
is it not fetching all the rows of the table when I say “select * from <table_name" in one shot ??
or is it working on the scroll bar event to fetch set of rows [ex: 20] and cache in the client machine since I'm connecting to remote DB ??
But when I scroll back to TOP then it has all the fetched records i.e multiples of 20 ..
that means it is caching the retrieved rows in the client RAM… AM I Correct ??
Please Clarify my DOUBTS?
Thanks Much for your Tutorial and Help on SQL DEVELOPER.
Thanks Again.
Kiran
Couple of questions here Kiran, so I’ll do my best to explain the mechanism of running queries and working with results in the grids.
After a query is executed, Oracle tells the client that their data is available. It’s up to the client to then request the data.
In SQL*Plus you will see all of the data come at once.
In SQL Developer if you execute a statement as a script using the F5 command, it will emulate the behavior of SQL*Plus – all rows will come back in one batch. The results will be auto-fetched in batches until everything is displayed. There’s a limit (optional) to only show 5000 lines in a script. If you want to see more rows, then look at the Worksheet options.
Now, for the grids.
When you execute, we will run the query and do a single fetch.
It sounds like you have your ‘Sql Array Fetch SIZE (Max. 500) set to 20. So results are retrieved in groups of 20. This is an extremely low number. I recommend you bump that up to at least 100 and possibly the max of 500.
As the rows are retrieved, they will be held in memory – yes. It’s important to close grids in the worksheet and the table/view editors when you’re done working with the data. Else that memory will never be released.
The grid contents will always be cached locally, whether the db is remote or on the same machine. Just about every client tool I’ve ever worked with follows after a similar fashion. Run > Fetch > Memory (Grid).
Thanks for your time Kiran. If this doesn’t answer your questions, feel free to contact me directly at
[email protected]