Types of Oracle joins

Just as a brush up of Oracle join understanding.

Suppose we are joining two tables in Oracle.

SELECT T1.X, T2.Y
FROM T1 JOIN T2 ON T1.X = T2.X

Assume, T1 is driving table (i.e. more rows than T2)

Nested Loop join
------------------

for each record in T1
find matching record in T2 where T1.X = T2.X
fetch that record into result set

Now if there is no index on column X in T2, Oracle will perform full table scan for each record in T1.

Hash join
---------

Oracle will load create an in-memory index (knowns as hash table) for T2 (i.e. smaller table)

It will still perform similar operation

for each record in T1
find matching record in T2 where T1.X = T2.X
fetch that record into result set

But even if there is no index on T2.X, Oracle will use the hash table which works similar to index!

Clearly this is suitable only when T2 is small otherwise [1] hash table may not fit in memory [2] creation of hash table may involve a long time (then we could have created the index on T2 in first place!)

Sorted Merge join
-------------------

If both tables are very big, hash join is not suitable due to memory/time requirement and nested loop will be slow or will result in full table scan.

The alternative way to speed up performance is to use sorted merge join.

In this case, both tables are sorted by Oracle (using temporary tablespace) by joining key (X in this case).

The advantage is, when joining two tables, Oracle just needs to scan only a small part of the table (e.g. as they are sorted, to find a record in T2 when T1.X = 15 will require Oracle search just from X=10 to X=20 in T2 as it cannot be beyond this range and so on).

The trade off is, it will take some resource (space/time/memory) to sort the tables.

Usually Oracle chooses best method of join based on statistics avaialble. However, user may force using a different type of join using hints like use_hash(T1,T2) etc.

Data Protection Act summary

As applicable in EEA/EU

Personal Information is data which relates to a living individual who can be either:

• Identified directly from that information or
• Identified indirectly from that information combined with any other data that is in the possession of the organisation holding the information.

The law requires that personal information provided to an organisation is,

• Managed fairly and lawfully
• Recorded accurately and kept secure.
• Kept up-to-date
• Used solely for the purposes agreed to.
• Kept no longer than necessary.

An individual has the legal right to:

• ask an organisation if they hold personal information about them
• ask an organisation that does have their personal information:
• what the information recorded is
• what it's used for
• a copy of all their personal information on record
• receive a copy of all their personal information within 40 days

An individual has the legal right to:

• Take action to rectify, block, erase or destroy inaccurate information.
• Take action for compensation if damage is suffered by any contravention of the Act.
• Take any complaint to the Office of the Information Commissioner, an independent body responsible for ensuring that the rules of the Data Protection Act are complied with.

The First Principle

Personal information must be processed fairly and lawfully.

For sensitive information to be processed an organisation must obtain an individual's explicit consent.

Sensitive personal data is information about an individual's,

• racial or ethnic origin
• political opinions
• religious opinions
• trade union membership
• physical or mental health
• sexual life
• alleged or actual legal offences
• legal proceedings or judgements.

The Second Principle

Personal information must be obtained and used for specified and lawful purposes.

We must make sure that an individual knows and understands why we want to use information about them.

We should make it clear to the individual what the information will be used for and who else it may be passed on to.

The Third Principle

Personal information must be adequate, relevant and not excessive.

We should only collect and keep personal information that allows us to do our work.

We should not hold any unnecessary personal details.

The Fourth Principle

Personal information must be accurate and kept up-to-date.

It is important for us to make sure that the personal information we hold is correct, up-to-date, and not misleading.

When collecting this information we should take reasonable steps to make certain that personal details are accurate.

The Fifth Principle

Personal information must be kept for no longer than necessary.

We should only keep on record a person's details for as long as it takes for us to do our work and no longer.

The information should be regularly updated and disposed of securely when we no longer need to use it.

The Sixth Principle

Personal information must be processed in accordance with the rights of the individual.

While we are working with a person's details we should make sure we do not use the information in a way which could cause them damage or distress.

We must also remember that an individual has the legal right to be given a copy of all their personal information recorded.

The Seventh Principle

Personal information must be kept secure.

We need to make sure that personal details held by us are safe from damage, accidental loss, destruction or unlawful access.

We should consider the harm or damage that could be caused through a breach in security and provide appropriate levels of,

• security vetting to ensure the reliability of personnel
• physical security
• technological security

The Eighth Principle

Personal information must be properly protected when transferred overseas.

Personal information should not be transferred outside European Economic Areas to countries that do not ensure adequate levels of data protection for data subjects.

Adequate security measures should be taken to make certain information is not violated either in transit or at its destination.

Areas of Exemption

There are a number of areas of exemption provided for within the Data Protection Act. Although it is not the objective of this course to cover these in any detail, an awareness of them is valuable.

Different levels of exemption apply to different areas. Other examples of areas where exemptions apply are those involving,

• National Security
• Crime and Taxation
• Credit Reference Agencies
• Health, Education and Social Work
• Journalism
• Legal, Professional Privilege
• Public Information, information made available to the public by law.

The McKinsey Way

Recently I read this book. I thought of writing down the salient points so that I do not forget.

Building the solution

• Fact based
• Rigidly structured
• Hypothesis driven

The problem is not always the problem.

Don’t reinvent wheel but every client is unique.

Follow 80/20 rule. Work smarter not harder.

Find key drivers in business.

Know your solution so that you can precisely explain to your client in 30 seconds.

Break the problem into small bits and try to solve each part instead of trying to solve whole thing in one go. But don’t lose focus from big picture.

Say “I don’t know” if necessary.

A little team bonding goes a long way. Make your boss look good.

When you go for an interview, be prepared. During an interview, listen and guide.

Display data with charts.

A good business message will have – brevity, thoroughness and structure.

Maintain confidentially whenever appropriate.

Keep client team on your side. Work around liability client team members.

Engage client in solution process.

Be rigorous about implementation steps of your solution.

A good assistant is a lifeline.

If you want a life, lay down some rules.

Diminishing era of desktop applications

During 1990s, there was a craze for developing desktop applications (i.e. applications like EXE files which are run locally from personal computers).

There was a reason, PC market was just catching up and 95% computers were using Windows! So, once you develop an application, it could potentially run on 95% of computers in the world. Also, broadband internet was not widespread at that time and dial up speed was abysmal at today's (2010) standard.

But after 2005 or so, Windows lost a good deal or market share to Apple and Linux. Broadband became cheaper and speed went up exponentially.

As a result, cloud or internet based applications started to prosper.

Just ask yourself, how many local applications you use in your computer now? Unless you a technical user (engineer, IT professional etc.) chances are you just use File Explorer, Browser and Office suite applications (or may be few games for your kid/s to play).

Then came smart phones! A lot of small apps are now available in phones. Who needs a computer to calculate compound interest anymore? Modern smart phones are as powerful as computers of 5 years back!

So, the reward (not just monetary) for developing desktop applications is diminishing.

The only future desktop applications will be mostly server based like databases, engineering applications etc.

As more and more operating systems will become popular, developers will try to create applications which will be used by many. So, web based apps will be natural choice.

What academic world did not teach us?

It is quite common incident all over the world that students who were considered the best often do not

perform that well later in their lives.

What is the cause of this? There are numerous factors behind it and every individual's situation is unique.

But still there are common pointers which I am going to discuss here.

First, school does not teach us how to choose between multiple options!

In academic world, usually most questions have one correct solution. However, in real world, most situations

have multiple outcomes. Depending on what route you choose, the outcomes can be vastly difference. For

example, you are working in India IT. You are getting a 6 month onsite opportunity (which may or may not

extend) in current company but also you got offer in another company in your town which offers no onsite but

promises 50% more salary. Which one will you choose?

Second, schools do not teach about money!

Yes, we do learn compound interest and cryptic formulas about finance. However, it does not teach us how to

handle money in real life. It does not impart the knowledge of how to invest in stocks and businesses. Nor

it teaches how to startup your own business and what problems you might face (well, you do get this in your

syllabus if you study MBA though). This missing knowledge is very valueable in real life.

Third, schools do not not teach how to deal with people!

In real life, people often deal with people. This is a very useful skill as whatever be your profession, you

will invariably dealing with lots of people. You need to know how to go well along with team members, your

boss, your spouse, your family, your relatives etc. You need to know how to satisfy some of their egos.

Sadly, this skill is often difficult to achive by experience and in the meantime you may upset many people

which might cost your business, job, relationship etc.

Fourth, schools do not teach you to spot opportunity!

In schools, you only take action when exam comes :) In real life, you need to take action often depending on your circumstances. If you fail to take action when opportunity arises, you lose a chance of going up on career ladder.

What are Indian Values?

I often hear Non-Resident Indians say about they want to teach their children Indian values. But what exactly it is?

Surprisingly enough, searching for Indian values on internet does not provide too many comprehensive pages! So I searched for this answer myself and then concluded that basically following points gives an overall idea of Indian values.

But some of this assertions are no longer true in modern India - which are mimicking (mostly bad) western cultures at a very fast pace!

Ok, let's go thru the values.

• · Attitude towards marriage and family values

Till today (2010), most Indians do not believe in pre-marital sexual relationship. Marriage is still considered sacred and lifelong bonding. The arranged marriage concept is widely followed. Divorce is still rare – though rising at an alarming rate. Live together is gathering momentum. Husbands and wives do fight among them all the time, but that seldom go to splitting stage! Similarly, it is Indian custom to respect parents and elderly. It is still rare to see Indian kids saying their parents “shake your arse elsewhere”. Grown up children (i.e. adults) still regularly communicate with their parents even they live at a far-away place.

• · Thriftiness about money

Indians are typically very cautious about money. We are more of a “savers” than “spenders”. Western people spend first and then save (or borrow more). We save first and then spend. We also invest heavily for future and kids upbringings. We often sacrifice non-essential purchases because we cannot decide if we really need to spend that much. Often this attitude affects NRIs for their whole tenure abroad. Most Indians buy goods on the basis of “value for money”. Though nowadays neo-riches in India spend so much that can put NRIs into embarrassment! Living within one’s own means ensured that it did not follow Europe/Americas credit crunch.

• · Attitude towards law

To a citizen in developed country, law is often an absolute concept. Either you break the law or do not break it. For Indians, it is a linear concept. Breaking a law only little bit is not considered a serious offence. For example, copying software illegally, paying bribe to traffic cops for speeding etc. are taken as part of life and not a big issue. So, a small deviation from absolute baseline of law is not considered as a crime in India.

• · Education

We value education a lot. I remember in my childhood my parents always chasing for good results in school/college and then universities. Because in Indian, you are very unlikely to get a good job unless you have good education. This has a slight negative aspect though – most Indians try to get a good job in multi-national companies rather than setting up their own business. However, the trend is now changing.

• Sacrifice and adjustment

Indians are famous for these! Whatever be the situation, Indians can usually adjust! We can also sacrifice lots of things. For example, still today, many educated women sacrifice their careers after marriages. A lot of Indian professionals living overseas adjust their lifestyle within a very strict budget. However, after leaving abroad for long time, many NRIs lose this value.

Of course, there are lots of negative things about Indian values too. But that will be another blog.

Our Iceberg is Melting - a fable on change management

Today I went to a bookstore and browsed several books in management section.

Then I found a book titled "Our iceberg is melting" which seemed quite thin and I decided, well I can finish it in the store itself.

So, within half an hour, I managed to finish the book off!

It was a fable about penguins in Antarctica. But funny part is, authors managed to highlight how change management can be implemented through this fable.

The fable naturally draws on eight steps for leading change which I have paraphrased below into seven to make them easier to understand:

1. Get peoples' attention.


2. Establish a change-leading team.


3. Agree on the results you want



4. Allow needed changes.


5. Show regular progress.


6. Stay focused.
7. Build new habits that will serve you well after the change is done
8. Make changes stick



This book is by no means a bible on change management, but definitely a good reading on steps how to deal with it.