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

Advertisements

Leave a comment

Filed under DB

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s