Saturday, April 09, 2011

Partitoning in Oracle

If you are like me, you might be a Developer with moderate knowledge in database. one of the frustrations we face as developers is when we are forced to work with non-indexed tables, which kill application performance.

Well there is a way to get around this in Oracle by using “partitions” , if your DBA has configured one.

partitions are created based on time interval that is set by the DBA’s , usually its 5 0r 10 min window if its a busy table.

when we query this table we would use partition keyword in Oracle to provide partition id to narrow down to the partition. This might need you to be aware of the time stamp when your data was created or updated, as the partition id’s are normally based out of “time stamp”

an example query would look like this – select * from TABLE partition(P200807022035) where field_name=’value’

“P200807022035″ is the partition id which is based on time stamp at 5 min interval ,

P200807022035 – year = 2008, month = 07, day = 02, time = 20:35

No comments: