Here is a good link on Explain plans and on displaying them.

http://optimizermagic.blogspot.com/2008/02/displaying-and-reading-execution-plans.html

I was reading this today and thought it was interesting.

Karen explains why having min and max in the same query impact performance and how to tackle the same.

Here is a very interesting scenario.

I have an employee table called EMP.

And I have a DEPT table.

Now, I write a query to find out all the employees who are part of any department. (DEPT_ID is properly updated and found in DEPT table)

Now, I want to know if any employee is not part of any department.

What??? No results? I know that employee with EMP_ID 9 doesn’t belong to any department. Where did he go?

Is Oracle drunk? Is it giving me wrong results? Wait let me check with another query.

Continue Reading »

I am a big fan of Vincent McBurney. Today I was reading this post.

I agree completely with his view on Denormalization. (Scroll to the bottom of the article).

At times, I have had difficulties in recalling if normalization is splitting or joining. And it does make a lot more sense to just call them ’split’ and ‘merge’ instead of ‘normalize’ and ‘denormalize’.

Just another instance where plain common sense could have been helped everyone.

Hi,
In Oracle databases, what is this high water mark? What impact does it have on the full table scan?

Well, HWM is an indication that segregates used and unused blocks. Above the high water mark you find the unused blocks and below it the used blocks.

Used blocks can be
1. Free - unoccupied
2. Used - occupied

As the data grows in a table, the high water mark starts moving upward. The only way to bring it down is to rebuild or truncate the table.

And a full table scan reads from the beginning of the table to the high water mark.

When you insert data into a table and then delete it later, the high water mark doesnt move down. So when you insert a million records into a table, the HWM moves up. A full table scan will scan as if there are a million rows. When you delete 0.9 million records from it, the high water mark doesnt come down. A full table scan will still scan as if there are a million records.

The ideal way to handle this is by repacking the table

alter table {TABLE_NAME}
move;

And keep 2 more things in mind.
1. Indexes size will remain same. That has to be repacked.
2. Indexes will be invalidated.

So, rebuild the indexes to fix these 2 issues.

Truncate:
1. Moves the high water mark down to beginning.
2. Doesnt need commit. It is a DDL command.
3. No condition needed. Removes all rows.
4. Extents are deallocated. (If you specify the reuse storage clause, then the extents are not deallocated)
5. No rollback.

Delete:
1. Doesnt change the high water mark.
2. Needs commit. It’s a DML command.
3. Can specify a condition and remove specific rows.
4. Extents are not deallocated.
5. Changes can be rolled back.

Hi,
I am a huge Tom Kyte fan!

Here is what he has to say about SQL tuning. Great thoughts.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:24189818401006

Oracle indexes do not contain entries that point to rows with null values on all the indexed columns.

Therefore any query that has a column IS NULL or IS NOT NULL will not use the index.

Hi,
Pls have a look at this site. Contains useful content.

http://people.aapt.net.au/roxsco/tuning/

Prem

Hi,
Have you used hints in a query involving multiple tables along with alias names for them?

Try hint on a query like this.

SELECT /*+index(IX_ILX_FCT_AR_6)*/A.AGING_BUCKET_KEY, B.AGING_BUCKET_KEY
FROM ILX_FCT_AR A, ILX_FCT_AR_LN B
WHERE A.INV_ID = B.INV_ID
AND A.RECORD_SOURCE_SYSTEM_NAME = B.RECORD_SOURCE_SYSTEM_NAME
AND A.SOURCE_RECORD_TYPE_NAME = B.SOURCE_RECORD_TYPE_NAME
AND A.AGING_BUCKET_KEY=’Z’
AND A.AGING_BUCKET_KEY<> B.AGING_BUCKET_KEY

Get the explain plan now.

Index is still not used???

Yes, because we have to specify the alias too! So here is the right hint to force the usage of the index.

SELECT /*+index(A IX_ILX_FCT_AR_6)*/A.AGING_BUCKET_KEY, B.AGING_BUCKET_KEY
FROM ILX_FCT_AR A, ILX_FCT_AR_LN B
WHERE A.INV_ID = B.INV_ID
AND A.RECORD_SOURCE_SYSTEM_NAME = B.RECORD_SOURCE_SYSTEM_NAME
AND A.SOURCE_RECORD_TYPE_NAME = B.SOURCE_RECORD_TYPE_NAME
AND A.AGING_BUCKET_KEY=’Z’
AND A.AGING_BUCKET_KEY<> B.AGING_BUCKET_KEY