Slideshare won’t let you update slide decks anymore, so I just uploaded my latest version as a new offering.

I’ll be presenting this topic at UKOUG this week. if you’d like me to present this to your group of the developers and administrators, drop me a note, and we’ll setup a webinar.

A Custom Design Rule – Do I Have Foreign Keys on Columns that are NOT Indexed?

One of the features I’ll be showing in this talk is the Data Modeler. I know that many performance problems start at the very beginning of a project. The data model is bad, incomplete, or just plain wrong.

I know that many people like to make sure that all of their Foreign Key columns are indexed – you can imagine that these columns will end up in your queries quite frequently as a predicate or even in the SELECT. Here’s some nice, practical advice on the subject from Tom.

So I asked our developer to help me with a custom design rule – this allows me to quickly ask my model if I have forgotten to cover this scneario.

After I add the code, I simply open the custom design rules and hit ‘apply,’ and I get my warnings.

The code for this is below…

Here’s the code:

// define function - function parameter doesn't need to be named index
function checkFKforSupportIndex(index){
  //check it's foreign key index, otherwise FK is null
 if(index.isFK()){
     columns = index.getColumns();
     table = index.getTable();
     var sind = getIndex(table,columns);
     return sind != null;
 }
 return true;
}
//getindex that is not used for PK, UK or FK constraint
function getIndex(tab,cols){
 keys = tab.getKeys();
 for (var i = 0; i < keys.length; i++) {
  ind = keys[i];
  if(!(ind.isPK() || ind.isUnique()) && !ind.isFK() && ind.isIndexForColumns(cols)){
     return ind;
   }
 }
 return null;
}
 
// invoke the function
checkFKforSupportIndex(index);
thatjeffsmith
Author

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

17 Comments

  1. Hi Jeff,

    I hope that the connections opened with a net route will still not hang for minutes before disconnecting when the Laptop is moved to a different location or when the laptop is suspended. There should be a way to disconnect quickly these connections.

    Another problem I noticed is when I try to compile a package that is in use.
    If I try to do this when using an unshared connection the editor simply hangs. When I do this when using the “main” connection then the entire interface hangs. I must open another client and kill the session otherwise I have to wait forever.

    The distinction between main session and unshared ones is quite annoying. IMHO everything should be unshared and there should be the possibility to attach a loaded file to any session (btw the session selection should be a bit less automatic because in complex environment it is easy to launch a script on the wrong database).

    And about the formatter, may be the USING statement in a MERGE statement should be put at the beginning on the following line, otherwise If the USING statement is complex it ends outside the editor window

    Thank you

    • e.g. (not tested the query but) if you format this you’ll see the SELECT placed four spaces after the parenthesis after the USING

      MERGE INTO aschema.atable_with_a_long_name aa USING (
      SELECT
      first_col
      ,second_col
      ,third_col
      ,and_so_son
      FROM
      aschema.another_table bb
      WHERE
      trunc(SYSDATE) BETWEEN bb.init_date AND bb.end_date
      )
      ubb ON ( aa.cod_row = bb.cod_row )
      WHEN NOT MATCHED THEN INSERT (
      a
      ,b
      ,c
      ,d ) VALUES (
      ubb.a
      ,ubb.b
      ,ubb.c
      ,ubb.d )

    • thatjeffsmith

      That’s formatter setting, Indentation – I think the default is ‘4’. I set it to 1 and it looks like this now

      MERGE INTO aschema.atable_with_a_long_name aa USING (
                                                           SELECT first_col,
                                                                  second_col,
                                                                  third_col,
                                                                  and_so_son
                                                             FROM aschema.another_table bb
                                                            WHERE trunc(SYSDATE) BETWEEN bb.init_date AND bb.end_date
                                                          )
      ubb ON ( aa.cod_row = bb.cod_row )
      WHEN NOT MATCHED THEN INSERT (
       a,
       b,
       c,
       d ) VALUES (
       ubb.a,
       ubb.b,
       ubb.c,
       ubb.d )
    • Thanks Jeff but IMHO it would be better if the select is moved maybe 4 spaces from the left border. Anyway OK.
      Another problem the formatter has is with labels. If there is a label in the source then, after formatting, the code is broken and does not compile anymore.
      Please try to format the following sample code. Thank you.

      declare
      lim integer := 10;
      ind integer := 1;
      begin
      <>
      dbms_output.put_line(‘ind [‘||ind||’]’);
      ind := ind + 1;
      if ind <= lim then
      goto label_1;
      end if;
      dbms_output.put_line('terminated');
      end;

    • Please notice that the blog software removed the text inside the angular brackets in the code above. Inside there should be the string “label_1” enclosed by another couple of angular brackets.

  2. Since you seems to be the SQLDeveloper magician, and I didn’t find where to ask, will do this here:

    Is there a way to make the Text Editor backgroundcolor the same as the Connection Color?
    I think only the borders colored didn’t draw the necessary attention…

    • Ohh, sad history… πŸ™

      Being the product manager, would you be able to suggest this as further implementation? πŸ˜€

      Thanks for the support! ^^

    • thatjeffsmith

      We’ve looked at it…it starts to make the tool look a bit like a christmas tree…can you think of anything less dramatic that might help?

      I think more people would not like this vs like this, esp as the number of colors you used went up. I can think of only 1 or 2 that might not be offensive (white/black)

    • Ops, when I first asked the question, I had already read the side panel “What It Is I Do Here”.
      But didn’t notice the link “Have a question? Ask me!”…

      Sorry :s

    • Yep, I totally agree that only a few would like this.
      That’s why I thought it only as configuration disabled by default, and whoever want activate…
      (and I would use this only with weak/light colors, to not hurt my eyes)

    • What I think would be less flashy, would be the tabs with the colors as well…

    • Something that I thought right now:

      “The ‘current line’ also could have a option to change the color as the Connection Color.”

      Then I’ve realized what I really think would be nice:

      The menu Tools > Preference > Text Editor > PL/SQL Sintax Colors, have the option “Use Connection Color” as the color of anything. Then, anyone who would like to put this color somewhere, would be able to… πŸ™‚

    • Yay!
      It’s nice to see that I was able to suggest something appreciated.

      Thanks for hearing! πŸ™‚

Write A Comment