All of the Tuning Features in SQL Developer (2018)

thatjeffsmith SQL Developer 0 Comments

Tell Others About This Story:

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);

Related Posts

Tell Others About This Story:

Leave a Reply

Your email address will not be published. Required fields are marked *