Hi guys.
Please suggest with the following.
I have a table that contains nearly 35 mill rows and increasing for 3-5 mill rows per month.
This table has a column that is of DATE type and very convinient to partition on it.
We decided to partition the table by this column by month and create local index on this column because the data is very often requested from this table by this column exactly.
Data is loaded thru this column as well. So this must give some win both when loading and when retrieving data.
The problem is the table has nearly 10 other index that are on foreing keys and are used oftenly when joining with otehr tables and these columns arent that convinient for local indexing them.
The table has a primary key as well.
The 99 % of the loaded data is new and only 1% is updated.
So do you guys think we better create local indexes on them(columns that arent date) as well?
OR create global index on them or leave them as they were?
Or leaving them as ordinary will not give any win?
Thanks .

Fadai wrote:
I have a table that contains nearly 35 mill rows and increasing for 3-5 mill rows per month.

We decided to partition the table by this column by month and create local index on this column because the data is very often requested from this table by this column exactly.

The table has a primary key as well.
The 99 % of the loaded data is new and only 1% is updated.
So do you guys think we better create local indexes on them(columns that arent date) as well?
OR create global index on them or leave them as they were?
Or leaving them as ordinary will not give any win?

If you think that partitioning 35M rows will give you useful query benefits due to partition elimination, or for partition maintenance in a data warehouse/DSS environment, then the ideal is to make as many indexes as possible locally partitioned indexes.

BUT - if you have queries that aren't going to do any partition elimination then the trade off is this:
<ul>
a) Global index - you probe the index once, then jump to random blocks in the table
b) Local index - you probe each partition in turn, then jump to random blocks in the table
</ul>

If the number of blocks you jump to in the table is "large" then you may not notice the cost of the extra index probes for the local index. If the number of blocks you jump to in the table is "small" then the number of probes of the local index may be a significant increase in the total cost of the query - and this gets worse the more partitions you have in the table.

There are further, more detailed, considerations, but that's the first step in working out the cost/benefit of local vs. global partitioning.

Note 1 - if you want your primary key index to be a local index, the partitioning column(s) has to be part of the primary key.

Note 2 - bear in mind that you could choose to make some of your foreign key indexes "globally partitioned", so that the sections are smaller and more manageable, you spread the root block activity, and still have a minimum cost access path to the table when partition elikmination is not possible. (This might be more appropriate to an OLTP environment where you are less concerned with partiiton maintenance operations).

Slower compared to what ?
Judging from the rest of your comment I think you are trying to compare the need to maintain an index with the benefits of being able to do a partiition exchange, but I just wanted to make sure you didn't have something else in mind.