This discussion is archived
10 Replies Latest reply: Jun 30, 2011 11:19 PM by Jonathan Lewis RSS

Partition Index Question Local vs Global

jeff81 Newbie
Currently Being Moderated
I have a table like the example below. As you can see it has the Year and Period (similar to months, 13 periods in a year) in separate columns. I am planning on partitioning by the Year but I am not sure how to organize the indexes. I assume I will make a global index on the Year but should I have a local or global index on the Period? And my other existing indexes I think most will be global. Any tips will be very helpful. Thanks.
create table INVENTORY_TRANSACTION
(
  dbperiod          NUMBER(2) not null,
  dbyear            NUMBER(4) not null,
  dbctr             NUMBER(4) not null,
  dbitem_number     NUMBER(5) not null,
  dbtype            VARCHAR2(2) not null,
  dbreference       NUMBER(7) not null,
  dbseq             NUMBER(4) not null,
  dbdate            DATE,
  dbinvoice         VARCHAR2(17),
  dbqty             NUMBER(9,2),
  dbamt             NUMBER(9,2),
  dbfrom            VARCHAR2(6),
  dbcalc_type       NUMBER(2) not null,
  dbtimestamp       DATE,
  dbbin_item_number NUMBER(5),
  dbbin_warehouse   NUMBER(4),
  dbwhbin           VARCHAR2(5),
  dbinv_cap_id      VARCHAR2(10),
  dbcap_code        NUMBER(3),
  dbfrom_ctr        NUMBER(4),
  dbusername        VARCHAR2(15)
);

alter table INVENTORY_TRANSACTION
  add constraint PK_INVENTORY_TRANSACTION primary key (DBPERIOD, DBYEAR, DBCTR, DBITEM_NUMBER, DBTYPE, DBREFERENCE, DBSEQ);

create index IDX_INVENTORY_TRANSACTION on INVENTORY_TRANSACTION (DBPERIOD, DBYEAR, DBBIN_ITEM_NUMBER, DBBIN_WAREHOUSE, DBWHBIN);
create index IDX_INV_TRANS_CALCTYPE_DBYEAR on INVENTORY_TRANSACTION (DBCALC_TYPE, DBYEAR);
create index IDX_INV_TRANS_DBDATE on INVENTORY_TRANSACTION (DBDATE);
create index IDX_INV_TRANS_ITEMNUMBER on INVENTORY_TRANSACTION (DBITEM_NUMBER);
create index IDX_INV_TRANS_PERIOD_YEAR_DATE on INVENTORY_TRANSACTION (DBPERIOD, DBYEAR, DBDATE);
create index IDX_INV_TRANS_REFERENCE on INVENTORY_TRANSACTION (DBREFERENCE);
create index IDX_INV_TRANS_TYPE_REF_CALC on INVENTORY_TRANSACTION (DBTYPE, DBREFERENCE, DBCALC_TYPE);
  • 1. Re: Partition Index Question Local vs Global
    troll35 Pro
    Currently Being Moderated
    Hello,

    I think you should use a local index on the Period, and you should do the same for all your indexes that are dependent of the year.

    With local indexes, you have partitionned indexes, so if your queries are using the year as search criteria, it will be faster with local indexes.
    With global indexes, you loose the advantages of the partitionned table.

    Local indexes are also easier to manage, as you can rebuild only a index partition, and if you drop a partition of the table, they do not become invalid.

    Hope this will help.

    Regards,
    Sylvie
  • 2. Re: Partition Index Question Local vs Global
    AlleT Explorer
    Currently Being Moderated
    local index where dbyear is part of the index

    global index for the others (or no partitions at all).

    Regards
  • 3. Re: Partition Index Question Local vs Global
    jeff81 Newbie
    Currently Being Moderated
    Thanks for the reply. I think I know what to do now. When I create local indexes that are dependent on the Year I still include the year column in the index right?
  • 4. Re: Partition Index Question Local vs Global
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    jeff81 wrote:
    I have a table like the example below. As you can see it has the Year and Period (similar to months, 13 periods in a year) in separate columns. I am planning on partitioning by the Year but I am not sure how to organize the indexes. I assume I will make a global index on the Year but should I have a local or global index on the Period? And my other existing indexes I think most will be global. Any tips will be very helpful. Thanks.
    What version of Oracle ?
    Is this OLTP, data warehouse, or DSS ?
    How many years will you keep data for ?
    How many rows per year ?
    Do different periods have similar numbers of rows ?
    What type of partitioning are you planning at present ? range, list, interval ?
    Do you not have to deal with queries like "data between year 2010 period 12 and year 2011 period 2" - if so how will they work ?
    Have you considered composite partitioning - if not why not ?
    Do you have a reasonably good idea of the queries that will hit the database ?
    Why have you made the column names harder to read by starting them all with "db" ?
    Is the dbdate going to hold date-only (i.e. midnight for time) values ?
    Can the dbyear be derived from the dbdate by simple arithmetic ?
    Can the dbperiod be derived from the dbdate by simple arithmetic ?
    How many distinct values are there for dbtype, for dbcalc_type ?
    How many rows, roughly, for each value of dbreference ?

    Regards
    Jonathan Lewis
  • 5. Re: Partition Index Question Local vs Global
    jeff81 Newbie
    Currently Being Moderated
    What version of Oracle ?
    11.2.0.1
    Is this OLTP, data warehouse, or DSS ?
    OLTP
    How many years will you keep data for ?
    Currently no plans to age out old data.
    How many rows per year ?
    About 8 million. I know it might not be worth partitioning but we wanted to check.
    Do different periods have similar numbers of rows ?
    Somewhat similar but not guarantied. Around 500,000
    What type of partitioning are you planning at present ? range, list, interval ?
    Range
    Do you not have to deal with queries like "data between year 2010 period 12 and year 2011 period 2" - if so how will they work ?
    This should not happen. And even if they do it will just end up looking at two partitions.
    Have you considered composite partitioning - if not why not ?
    No. Not sure if it would be helpful in my situation. But maybe it would be since a lot of the time we are querying on a single period.
    Do you have a reasonably good idea of the queries that will hit the database ?
    Yeah, mostly on a single period but we also have summaries for a range of periods in a year.
    Why have you made the column names harder to read by starting them all with "db" ?
    This started a long time ago before my time as a DBA. Apparently the reason was because the VB and C++ programers at that time wanted an easy way to identify fields from the database in their code. Now we are all used it and hardly even noticed it.
    Is the dbdate going to hold date-only (i.e. midnight for time) values ?
    Yes this is date only.
    Can the dbyear be derived from the dbdate by simple arithmetic ?
    No
    Can the dbperiod be derived from the dbdate by simple arithmetic ?
    No
    How many distinct values are there for dbtype, for dbcalc_type ?
    dbtype = 15
    dbcalc_type = 6
    How many rows, roughly, for each value of dbreference ?
    at least 2, hardly more than 10
  • 6. Re: Partition Index Question Local vs Global
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    jeff81 wrote:
    What version of Oracle ?
    11.2.0.1
    Is this OLTP, data warehouse, or DSS ?
    OLTP
    How many years will you keep data for ?
    Currently no plans to age out old data.
    How many rows per year ?
    About 8 million. I know it might not be worth partitioning but we wanted to check.
    Unless you've already paid for partitioning it's probably not worth if for just 8M rows per year.
    Do different periods have similar numbers of rows ?
    Somewhat similar but not guarantied. Around 500,000
    What type of partitioning are you planning at present ? range, list, interval ?
    Range
    If you really want to partition, then LIST is probably better - and for your application it sounds like LIST/LIST composite would be ideal ... if you had a volume of data that was worth using partitioning. With LIST/LIST you could get rid of a couple of your indexes, and remove the year/period columns from any index except the primary key. The downside is that if you made all the indexes local then some of your queries would do more work probing all the partitions which would be 13 * year_count probes.

    Do you not have to deal with queries like "data between year 2010 period 12 and year 2011 period 2" - if so how will they work ?
    This should not happen. And even if they do it will just end up looking at two partitions.
    Have you considered composite partitioning - if not why not ?
    No. Not sure if it would be helpful in my situation. But maybe it would be since a lot of the time we are querying on a single period.
    See above
    Do you have a reasonably good idea of the queries that will hit the database ?
    Yeah, mostly on a single period but we also have summaries for a range of periods in a year.
    Why have you made the column names harder to read by starting them all with "db" ?
    This started a long time ago before my time as a DBA. Apparently the reason was because the VB and C++ programers at that time wanted an easy way to identify fields from the database in their code. Now we are all used it and hardly even noticed it.
    Is the dbdate going to hold date-only (i.e. midnight for time) values ?
    Yes this is date only.
    Can the dbyear be derived from the dbdate by simple arithmetic ?
    No
    Can the dbperiod be derived from the dbdate by simple arithmetic ?
    No
    How many distinct values are there for dbtype, for dbcalc_type ?
    dbtype = 15
    dbcalc_type = 6
    Hardly seems worth indexing them, unless they have extremely skewed distributions, and then you might want histograms, or function-based indexes, or virtual columns
    How many rows, roughly, for each value of dbreference ?
    at least 2, hardly more than 10
    That gives you the most interesting performance question: the more partitions/subpartitions, the more index probes you have to do to find just a few rows. year/period composite is very nice for some reasons, year-only is more realistic for 8M rows per year, but the bottom line is that partitioning will be a very expensive option for a system that could manage without.

    Regards
    Jonathan Lewis
  • 7. Re: Partition Index Question Local vs Global
    jeff81 Newbie
    Currently Being Moderated
    Well we already have a license for partitioning so it's not going to cost us anything.

    Our thinking for trying partitioning is that since we typically only look at a period or years worth of data, so if we had partitioning then we can immediately eliminate millions of row and the query would only have to look through a smaller amount of data and our queries would perform faster. We have some queries that calculate some summaries based off a single period and they take a couple seconds, so we thought partitioning could help here. I've looked at the queries and did not see a way to improve them any further
  • 8. Re: Partition Index Question Local vs Global
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    jeff81 wrote:
    Well we already have a license for partitioning so it's not going to cost us anything.
    Assuming your dbperiod codes are the same 13 values for every year, I'd take a good look at using the year/period for list/list composite partitioning (with a subpartition template for the periods - I would assume that that ought to be possible, though I haven't tested it yet) with local indexing.

    This would give you very small partitions, and a downside that some queries may have to visit every index.

    If you do this then looking at your indexes:
    alter table INVENTORY_TRANSACTION
      add constraint PK_INVENTORY_TRANSACTION primary key (DBPERIOD, DBYEAR, DBCTR, DBITEM_NUMBER, DBTYPE, DBREFERENCE, DBSEQ);
     
    create index IDX_INVENTORY_TRANSACTION on INVENTORY_TRANSACTION (DBPERIOD, DBYEAR, DBBIN_ITEM_NUMBER, DBBIN_WAREHOUSE, DBWHBIN);
    create index IDX_INV_TRANS_CALCTYPE_DBYEAR on INVENTORY_TRANSACTION (DBCALC_TYPE, DBYEAR); 
    create index IDX_INV_TRANS_DBDATE on INVENTORY_TRANSACTION (DBDATE);
    create index IDX_INV_TRANS_ITEMNUMBER on INVENTORY_TRANSACTION (DBITEM_NUMBER);
    create index IDX_INV_TRANS_PERIOD_YEAR_DATE on INVENTORY_TRANSACTION (DBPERIOD, DBYEAR, DBDATE); 
    create index IDX_INV_TRANS_REFERENCE on INVENTORY_TRANSACTION (DBREFERENCE);
    create index IDX_INV_TRANS_TYPE_REF_CALC on INVENTORY_TRANSACTION (DBTYPE, DBREFERENCE, DBCALC_TYPE);
    (DBCALC_TYPE, DBYEAR) would probably be redundant because there are so few distinct values for cbcalc_type.

    (DBPERIOD, DBYEAR, DBDATE) would be redundant because the correct period and year would be identified by partition elimination and you already have (dbdate) as a separate index.

    (DBTYPE, DBREFRENCE, DBCALC_TYPE) probably won't be used for queries on DBTYPE alone because there are so few distinct value for dbtype - so you might as well make it (DBREFERENCE, DBTYPE, DBCALC_TYPE) - which makes (DBREFERENCE) redundant.

    (DBPERIOD, DBYEAR, DBBIN_ITEM_NUMBER, DBBIN_WAREHOUSE, DBWHBIN) doesn't need the leading period and year because you identify the first two columns through partition elimination

    (DBPERIOD, DBYEAR, DBCTR, DBITEM_NUMBER, DBTYPE, DBREFERENCE, DBSEQ) has to include period and year because they are part of the primary key - but the columns don't have to be the leading columns of the index: so you might think about a different index order. If DBCTR is highly repetitive (small number of distinct value) then the combination of the first three will give terrific index compression; on the other hand if that is the case then starting (dbitem_number, dbctr,...) would make the index on (dbitem_number) redundant.

    Regards
    Jonathan Lewis
  • 9. Re: Partition Index Question Local vs Global
    jeff81 Newbie
    Currently Being Moderated
    Thanks Jonathon for your replies. It sounds like you think partitioning will not be helpful based on only adding 8 million rows per year. Do you think it could actually hurt?
  • 10. Re: Partition Index Question Local vs Global
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    jeff81 wrote:
    Thanks Jonathon for your replies. It sounds like you think partitioning will not be helpful based on only adding 8 million rows per year. Do you think it could actually hurt?
    I've just given you a few suggestions on how to partition your data, pointed out that this makes several of your indexes smaller or redundant, and highlighted a possible downside - how do you manage to turn this into: " partitioning will not be helpful" ? It's your data, your system, your knowledge of the application --- your choice.

    As far as hurting - I already given you one example of why it might hurt.

    I have no sympathy with the gun lobby, but in the case of database design there is a lot to be said for the mantra : +"features don't hurt the system, people hurt the system"+.

    Regards
    Jonathan Lewis

Legend

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