Jan 16, 2023 2:04:00 PM by Gary Jerep
A continuing blog series about finding more of those “hidden” gems in Toad’s graphical interface.
“We do not follow maps to buried treasure, and ‘X’ never—ever—marks the spot. 70% of all archeology is done in the library.”
— Indiana Jones (from “Indiana Jones and the Last Crusade”)
If I Only Knew Then…
It wasn’t long ago that a customer (we’ll call him Brad) was sharing his screen with me to discuss how he’s using Toad for Oracle. During our session, I watched Brad execute a query in the Toad Editor. Moments later, he realized he had forgotten to include a few columns. So he modified the query and re-ran it. Not long afterward, he ran several more (similar) queries back-to-back. At some point, I became aware that each of Brad’s queries to this point were retrieving different sets of columns, but all from the same table.
I asked Brad, "Why don’t you simply use the table’s data grid tab in the Schema Browser to view any/all of your data columns?” Brad explained that the table has over 300 columns, but he only needs to grab a few of them at a time. "I don’t have time to wade through hundreds of columns to find the ones I need.”
OK, hundreds of columns. Scrolling nightmare. I get it, so we moved on. Ah, but I wish I knew then what I know now. There is a better way. So, Brad (and those of us like him), if you’re reading, let’s talk about the “column chooser” icon.
… What I Know Now: the Column Chooser
The Column Chooser looks like this (depending on your desktop color scheme) and appears in the upper left-hand corner of just about any data grid you encounter in Toad for Oracle (e.g. look to the immediate left of the first column header displayed in the grid.) This gem of an icon is embedded in dozens of grid displays in Toad—I stopped counting after seeing it in over 30 grids, and there’s way more!—so it’s not a rare jewel. It's in plain sight! However, not many Toad users are aware of its existence because it appears so unobtrusively as part of the data grid frame.
The tool bar buttons bearing this icon image do what its tool tip says. Pressing this icon lists all columns that could be displayed and allows you to checkbox only the columns you want displayed in the data grid. Check out the screen snap below.
Why is this icon important?
The value of this tool bar button is obvious. It’s a fast convenient way to
Especially useful for tables with hundreds of columns like Brad’s. But there’s more convenience behind its face value.
Veteran Toad users should know that Toad does its filtering/sorting/grouping/aggregations/column selections/etc. on its data grids in memory as much as possible. So when Brad runs his full query once (and only once), then check-boxes a different set of columns, the Column Chooser grabs the data for his desired columns from Toad memory, not from the database server again. Convenient and quick for Brad, yeah, but Brad’s DBAs ARE HAPPY TOO: they won’t be seeing the same/similar queries eating up unnecessary data block reads on the database server.
But Wait, There’s More!
You can see from the previous screen snap that there’s a click-box to sort the column list alphabetically. OK, could be a quicker way to identify your columns of interest.
Oh, and there’s no need to keep re-choosing your fave columns every time you view table data grids in the Schema Browser. Toad remembers your last column selection for each of the tables you visit in the Schema Browser if your Oracle session remains connected in Toad. Super nice.
And yes, the Column Chooser works even in the data grids for database views and synonyms.
But (of course) there’s more. The screen snap below shows that right-clicking on the column list portion of the selection panel uncovers these “hidden” options:
If you like finding treasured artifacts in Toad that make your life easier, then stay tuned for future posts in my “Raiders” series. If you’ve encountered a gem in Toad that has impacted the quality of your work life, we encourage you to share your experience by replying to this post or by starting a discussion.
If you think your colleagues would benefit from Gary's productivity blog, share it now on social media with the buttons located at the top of this blog post. Thanks!
Interested in other Toad solution insights like this one? How about real-world accounts of how Toad has rocked someone’s world? Read Gary’s other blog posts.
Now that you’ve got the data I want, what’s the best way to export it?
Want to use a visual query builder to bring back your data?
Why an Oracle ACE loves the Toad Editor
Database admin/development doesn't have to be hard.
Tags: Toad for Oracle Data Grid Column Chooser
Written by Gary Jerep
As a Software Consultant within Quest, Gary Jerep has over 20 years of experience assisting DBAs, Developers and Analysts with relational database needs. Prior experience includes systems design and analysis, operations research, Business Intelligence, and end-user experience monitoring.