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.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.
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?
Dilip Patel wrote:Slower compared to what ?
Just small point to add is.
Global Index on partitioned table makes insert process slower (in case of huge inserts). Workaround would be drop global index, insert records and create global index. Fetching rows will be fast from global indexes if it across many partitions.
Locan Indexes advantage is, you can adapt exchange partition mechanism to load data to partitioned tables.