Mostly Making oracle easy, mostly:
Search for:
In SQL Developer

Filter All The Things

3 Mins Read

I often spend a lot of time on the ‘cool’ and ‘advanced’ tips and tricks when doing talks for SQL Developer. Today, I’m co-presenting with Helen , and she chose to spotlight a bunch of stuff around filtering.

I love the topic, but I usually short change the subject in my talks.

Today, let’s review all (most??) of the ways SQL Developer can help you get past the clutter.

Connections

You can hide connections. Just organize them using folders. Right click one or more connections, add to a folder, existing or a new one.

Easier to find means I get to work a few seconds faster.
Easier to find means I get to work a few seconds faster.

Grids

Any Grid, Client Side Filtering
You can apply as many of these as you have columns. Clicking on the column header will show you the distinct values for the column that have been fetched down to SQLDev so far.

Clicking on a value will only show rows of that value for that column.
Clicking on a value will only show rows of that value for that column.

You can also use a query predicate to filter the list – this will still employ query side filtering by the way.

This column has a filter and is being sorted.
This column has a filter and is being sorted.

Table Editor, Server Side Filtering
Trick: use ctrl+space in the filter text area, and we’ll pull up the list of columns so you don’t have to type them. Type everything EXCEPT the actual word ‘WHERE.’

Change this, and we'll re-query the table.
Change this, and we’ll re-query the table.

Connection Objects

Did you know you can filter across multiple object types?

two of my connections have global filters applied
two of my connections have global filters applied

These objects all start with 'BEE' so they are being listed.
These objects all start with ‘BEE’ so they are being listed.

Use the Schema Browser? It has client side filtering as well.

No need to wildcard, just type the text you want to filter on.
No need to wildcard, just type the text you want to filter on.

Maybe you just want to filter users/schemas in your system –

You can also filter on the number of objects in a schema, say only show schemas that own > 0 objects.
You can also filter on the number of objects in a schema, say only show schemas that own > 0 objects.

Or maybe you want to now show specific types of objects in the tree.

New for version 4.0, disable the object types you don't use or care about
New for version 4.0, disable the object types you don’t use or care about

Database Copy, Export, and Carts

You can apply single object and global where clause filters.

Getting data out - apply a filter across multiple tables or at the table level or BOTH
Getting data out – apply a filter across multiple tables or at the table level or BOTH

Bonus Tip for the DBAs – Filtering your ASH Reports

There are filtering dialogs all over SQL Developer. You just need to be on the look out for them.

It's my fault, not SQLcl's - promise.
It’s my fault, not SQLcl’s – promise.

The Most Important Filters?

Don’t use SELECT * FROM in your queries, especially in your application code.

Use WHERE and HAVING clauses whenever possible.

Generally speaking, the less data you ask for, the easier it will be to deal with. And you’ll probably get it faster too.

Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

Related Posts

5 Comments

  1. julesdba Reply

    Hi,

    Connection objects filtering – is there a way to save filters setup so there could be more than one filter or to retrieve later.

    Thanks!

  2. Jens Ulrik Jacobsen Reply

    Table Editor, Server Side Filtering

    Trick: use ctrl+space (not ctrl+enter as stated) in the filter text area, and we’ll pull up the list of columns so you don’t have to type them.

  3. Helen Sanders Reply

    Thanks for the Shout-out and allowing me to co-pilot the webinar. You keep teaching us the advanced tips..I’ll keep going with the basics for noobs 😉

  4. Andrey Reply

    Hi Jeff!

    Right now I have about 300 connections stored in 40 folders and both numbers are going to grow in future. Do you have any plans about filtering connection by name or making connection folders tree multilevel?

    Any news about SQL Developer SDK, to make is possible to solve such a case myself?

    Thank you!

    Regards,
    Andrey

Write A Comment