This discussion is archived
3 Replies Latest reply: Dec 30, 2008 2:40 PM by Jonathan Lewis RSS

What about nonpartitioning column index when partitioning!?

631129 Newbie
Currently Being Moderated
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 .
  • 1. Re: What about nonpartitioning column index when partitioning!?
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    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).

    Regards
    Jonathan Lewis
    http://jonathanlewis.wordpress.com
    http://www.jlcomp.demon.co.uk

    "Science is more than a body of knowledge; it is a way of thinking" Carl Sagan
  • 2. Re: What about nonpartitioning column index when partitioning!?
    3530 Explorer
    Currently Being Moderated
    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.


    Dilip Patel.
  • 3. Re: What about nonpartitioning column index when partitioning!?
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    Dilip Patel wrote:
    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.
    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.

    Jonathan Lewis
    http://jonathanlewis.wordpress.com
    http://www.jlcomp.demon.co.uk

    "Science is more than a body of knowledge; it is a way of thinking" Carl Sagan

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points