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.

The Animated GIF

TL/DR ?

I

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.

4 Comments

  1. Grace Wheeler-Poole Reply

    I just tried to update SQL Developer Data Modeler and got 3.1. I have downloaded SDDM several times to it’s own directory (stand alone). The problem there is finding the bin directory to put my tnsnames.ora file under. I downloaded the stand alone with java because otherwise it never liked any of my java.exe files.

    The purpose of this exercise is to create data models of our tables used in PeopleSoft.

    • In your SQL Developer install, find the sqldeveloper.conf file under the bin subdirectory. Open that file and copy the path to the JDK. If it’s a relative path, figure out the full path, then use that same Java install for the standalone copy of the modeler.

      SQLDev should find the TNSNames file w/o having to copy anything over to the bin directory. We should be able to get you up and running in less than 5 minutes. If you want help, please feel free to email me at [email protected]

  2. Grace Wheeler-Poole Reply

    I like youro blog, it has been helpful. I would really like to utilize the “Create a new Subview with neighbors”; but, I have updated the Data Modeler under SQL Developer and can only get Data Modeler version 3.1. Also, since I have updated I can’t drag neighbors any help. Are there switches I need to flip?
    Thanks In Advance

    • Can you try Help > Check for Updates to grab the latest version of SQL Developer Data Modeler (SDDM), which would be 3.3.

      As a temporary workaround, you could get the stand-alone copy of SDDM, which I recommend everyone use if their primary requirement is modeling vs having a query tool that can also generate diagrams.

Write A Comment