Wednesday, October 7, 2015

String Theory (sort of)

Today’s little brain twister was “get the trace file name for the current session”.  

Sure it’s all there in V$DIAG_INFO (since 11) as in:

SQL> select value from v$diag_info where name = 'Default Trace File';



But I want just the last bit, hotsos_ora_9240_HARNESS46_113853.trc.  So after a bit of re-learning on the sting functions of Oracle I can up with this:

SQL> select
  2  substr((
  3  replace((select value from v$diag_info where name = 'Default Trace File'),
  4  (select value from v$diag_info where name = 'Diag Trace'),'')),2) Tracefilename
  5  from dual;



The value from the second inner query with the 'Diag Trace' part gets just the directory path for the file. By replace that with a NULL (the '' part) I have just the name, except it leaves behind a \ or / depending on your OS at the beginning of the string like this:

SQL> select
  2  replace((select value from v$diag_info where name = 'Default Trace File'),
  3  (select value from v$diag_info where name = 'Diag Trace'),'') Tracefilename
  4  from dual;



So the outer substr will chop off that unwanted character and Ta-Da!  I have just the file name.   

Isn’t this SQL stuff the best!?

Monday, August 31, 2015

Singing in the key of primary - Why your primary keys should be artificial

I believe very firmly that a primary key should always be an artificial key.  Really.  Many folks talk about having a “natural key” that can be used as a primary key.  I believe that this can appear to work but will eventually fail.  So let’s take a look at what this is all about.  

First and foremost why do we have a primary key?  Most folks believe it is there so we can select the record quickly.  After all if I have the primary key for a row it will be a unique look up since the primary key by definition is unique and not null (otherwise it’s not a primary key).  That is true, sort of. 

A primary key is there so we can join it to another table.  Really, that’s why they exist.  Not so you and I can select the record directly, it’s there so we can join this table to another and get rows that are related to one and other.  The classic simple example is the good old Oracle tables EMP and DEPT.  In the DEPT table we have the column DEPTNO as its primary key.  In EMP table one of its columns is DEPTNO which points to the record in the DEPT table that the employee works in.  This is the standard primary key to foreign key relationship in a relational database.  This relationship is the very cornerstone that the whole relational model stands on.  Without this nothing else really matters.

So what is a good column to have a primary key on?  Of course out of the gate it has to be unique and not null.  But what else? One other thing that should be true is that they don’t change.  And a problem when you look for a “natural key” is that you can’t guarantee that it wouldn’t change or remain unique.  If the data in the primary key column is generated outside of your database, there is no way you can have a 100% guarantee it’s solid and will stay that way for ever.

Another thing to consider is that it really should be one column, not several.  Think again about why a primary key exists, to be a foreign key in another table.  If it’s one column it’s easy to “push” that to another table.  But what about 2 or 3 columns?  Now it starts to get cumbersome.  Also what about a many to many relationship?  The primary key for both tables is in the resolution table, both sides have 2 columns each, now it’s a 4 column resolution table not 2 columns.  So what?  Multiply that out about a million times and you see it starts to be an issue. And since there is likely an index on the resolution table as well, this will cause the index to approach twice the size.

Sure there are database level features to combat these like compression and partitioning and all that.  But what if we had made better choices to start with?  Then it would be longer before we have to invoke such things, if at all.  For features that require a license fee, this could result in a financial savings. And these features just push the performance and storage problems down the road; they are still there just hidden for the moment.

The primary key should have nothing to do with reality.  If it does, it’s just a matter of time before it changes; the only constant in the universe is change after all.  Once it changes that is where the real problems can come in.  Imaging having to redo all the primary keys in a core table of your application.  Scary thought I know. 

So what should they be?  To me the answer is simple - Every primary key should be an artificial key.  The best key would be generated by a unique random number or string generator.  But that is at least really hard to do so the next best thing is a numeric sequencer.   Also I would purpose that in a really well designed system this key is not displayed to the users at all.  Remember this thing is ultimately for the relationships between tables.  Not really for row selection.  I’m all for other alternate keys like an employee ID that is based on the date of hire, SSNs, product codes, account numbers, or similar columns to be used for select rows.  Those are excellent and might even be unique and not null, well maybe the not null part will work at least.   The unique part might work for a while at least. Remember the argument for using SSNs for a person’s unique ID? Well that might be a problem.

Unique identifiers are very different from primary keys.  They sure can seem to be the same and sometime even have the same attributes (like unique and not null) but one is really for row selection (an identifier) and the other for setting up for foreign keys (the primary key). 

Tuesday, July 14, 2015

PL/SQL INLINE feature, has some limits.

Check out this post about inlining of code, inspired by me.

Excellent stuff.

Just don't call me Sheldon.... :-)

Thursday, June 4, 2015

I'm here to teach, are you here to learn?

I teach for a living.  I’m not a teacher, I see myself as an instructor, but saying “I instruct for a living” sounds rather odd.  The difference mostly is in duration.  I only have a class for a couple of days and I don’t give any tests or grades.  My goal is to impart knowledge to folks so they are better at their jobs.  

And I take my job very seriously.  I spend hours making sure the content is correct and up to date.  (This is really hard with Oracle, since its always changing.)  I spend hours coming up with exercises that will help students understand the concepts being presented in an easy to understand way.   I will stay late to help folks understand something they didn’t get.  I’ll work with them even after class thru email.

I love what I do.  I really do.   And based on the many years of feedback, I’m quite good at it too.

What does bug me is that some students show up to class with an idea that this is some sort of vacation.   While I’m not running a basic training camp (did that in a past life, not really into that any more), what I do have is a really great curriculum that I have poured my life into and I hope you will come with an eagerness to learn.

I’m not here to start class late, take a 2 hour lunch and then for you to cut out over an hour early “just because”.  When you come to my class, be ready to learn.  Ask the tough questions, I don’t know all the answers but if no one asks the questions we’ll never have the opportunity to find the answers.

True I don’t give a test at the end of class, your test is when you get back on the job and can perform your job better.  If you slept thru class then you will fail your test. 

See you in class soon.