Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Oracle.

Asked: October 05, 2016 - 3:59 am UTC

Last updated: October 06, 2016 - 12:52 am UTC

Version: 11.2.0

Viewed 10K+ times! This question is

You Asked

Hi,

1.I have table with huge data so I have created the index, gathering stats, rebuilding index but it is very slow now I have seen the optimiser plan it is showing create partition index So can you pls help me to know what is partition index and how to create it.

2. I want to create partition tables which create partition daywise automatically so can let me know any help to create this.

Thanks

and Connor said...

1) You havent given us any real info to work with here. Is it slow *when* creating the index, or are you talking about queries ? Or something else ?

2) Check out interval partitions - they do the work for you.

create table T
(  date_col date,
   ...
)
partition by range ( date_col )
interval ( NUMTODSINTERVAL(1,'day') )
( partition p1 values less then ( date '2016-01-01' ))


We will add a new partition of "size" 1 day as new data comes in.


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

oracle oracle, October 05, 2016 - 11:02 am UTC

Thanks for reply,

I am asking we have a huge table and continuously insertion the data So, It is very slow to fatch and insert and update are there and on this table insert and update both are working so lock are also there i discuss with application team they are saying it may happen they can not optimize more that this So, I gather stats rebuild indexes but no affect . So, They want to

1. Create an Partition index.
2. Create a tables partition which creates automatically everyday.

So, i am asking help to let me know in details about this both points.

Thanks

Connor McDonald
October 06, 2016 - 12:52 am UTC

"It is very slow to fatch and insert and update"

Tables arent slow... but *access* to tables might be.

This sounds like sql tuning exercise, which *might* then necessitate physical design changes. But *start* with the SQL first.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.