ThatJeffSmith

Won’t You Be My Neighbor? Selecting Related Objects in Oracle SQL Developer Data Modeler

I loved Fred Rogers. He helped raise me. Nicest guy ever?

So what does he have to do with Oracle SQL Developer Data Modeler? One word, ‘neighbors.’

I got a good question from a new user yesterday:

Is there a quick way to add all the tables (to a subview) directly related to a specific table?

First, you know what a SubView is, yes? It’s what we call a ‘sub model’ – or a subset of model objects. They allow you to work with smaller versions of your model.

So let’s say you have several hundred tables in your relational model, and you want to create a SubView based on a driving table. Maybe that table is the basis of your STAR schema or it’s a fact table. This is very easy to do in just about 5 clicks.

1. Select Your Driving Table

Click #1 – find your table in the diagram and select it. If you need help finding your table in the diagram itself, you can auto-navigate to it by right-clicking on it in the model tree, and selecting ‘Go to Diagram.’

2. Right Click

Click #2 – use your mouse on the driving table to get to the context menu.

3. Select ‘Select Neighbors’

Clicks #3 & #4 – this does exactly what it sounds like.

Let's select our neighbors

Let’s select our neighbors

Now we have to tell SQL Developer what direction we want to go, and how many levels of relationships to navigate.

The defaults will select all directly related tables in either direction

The defaults will select all directly related tables in either direction

So that’s either clicks 4 or 4-5 or 4-6 based on if take the defaults or tweak the options here. I’ll say just #4 :)

4. Right Click Again on any of the ‘Selected’ Tables

That’s click #5

My neighbors are now auto-selected and the context menu will apply to all of the desired tables

My neighbors are now auto-selected and the context menu will apply to all of the desired tables

5. Select ‘Create SubView from selected’

Presto-changeo! We now have our new SubView. And maybe that took more than 5 clicks, but it was really, really close. And if you have lots of objects to copy over, then this will definitively save you some time. So let’s go to the land of Make-Believe and check out our new SubView:

Our new subview, automatically created by selecting driving table and its neighbors

Our new subview, automatically created by selecting driving table and its neighbors

There’s Another Way and Fewer Clicks!

You knew there must be a faster way, right?

Creating the new SubView from the diagram takes a few more clicks, but you get to see visually what’s actually going to be moved. The downside – more clicks and the resulting SubView diagram doesn’t re-position the objects to the upper left hand corner of the diagram – they’ll occupy the same coordinates they do in the ‘master’ model diagram.

If you want truly fewer clicks, trust SQL Developer to select the ‘right’ neighbors, and want the SubView diagram to be put into the ‘top’ corner where it’s easier to get to, then right-mouse on your driving table in the tree.

New SubView from neighbors all in one click :)

New SubView from neighbors all in one click :)

The other ‘difference’ – neighbors will be determined by actual Foreign Key relationships – not just by model diagram links. If you’re in a data warehouse or another environment sans Foreign Keys, this can make a big difference, so pick your poison carefully.

Don’t Forget to Upgrade Your Version of Oracle SQL Developer Data Modeler!

Version 3.3 has been out for a few weeks now. We just made an update available to Oracle SQL Developer users too. If you’re using version 3.2, just use the Help > Check for Updates to grab the v3.3 extension of the data modeler.