Tuesday, November 18, 2008

KYD Factor -It keeps coming back again and again

I have faced many times in oracle projects trying to do one thing esp when i am asked to
maintain, enhance or test the performance of backend code written in oracle sql,plsql:
        "The KYD -Know Your Data Factor" - Understand your data in tables as well as structures involved
Sometime back David Aldridge mentioned this as one of the points in writing good sqls,
Probably i would like to keep a copy of this whitepaper from Dan Tow by my side always..

How much time one saves by just understanding this in first place, rather than beating around the bush trying to refactor sqls
which may or may not give you the optimal solution in the long run.

(Quoted directly from above Dan Tow link:
Code What You Know

To understand the database design well enough to write functionally-correct code likely to perform well from the start, you should be able to answer a series of questions with confidence:

  • What set of entities does each table represent?
  • What is the complete primary key to each table?
  • What set of entities does each view represent?
  • What is the virtual primary key of each view?
  • Roughly how many rows in production will there be in each table or view? 
It is surprising how often owners of broken code cannot answer these very basic questions, but it is hardly a surprise that the result, without this understanding, is broken code!"