Forum Stats

  • 3,875,479 Users
  • 2,266,926 Discussions
  • 7,912,227 Comments

Discussions

Need suggestion: few records to be updated in millions

User_7X4SW
User_7X4SW Member Posts: 7 Blue Ribbon
edited Aug 30, 2013 2:35AM in SQL & PL/SQL

Hi All,

I need your expert comments over below senerio:

Senerio: i have a table which has 2 column. Person_Id and Country_id. it has millions of records.

1) One persion_id may have multiple country_id but maximum 4.

2) this data is get updated. But around 4000 records are updated only out of millions present in table.

This updation will be done by a procedure which i will create. We will have information about those 4000 records which will need update and will update above table.

Now from performance point of view.. how we should design this table?

1) Should i create partition on this table? if yes.. what type of?

2) Should there be any index..

Please suggest, if there is any other better way.

Thanks alot in advance.

Tagged:

Answers

  • Paul  Horth
    Paul Horth Member Posts: 3,402 Gold Trophy

    How are you identifying those 4000 rows (not records, tables have rows)?

    If it is via the person id, then that's a good candidate for an index.

    From what you've said it doesn't sound like you need partitioning but I don't know what else you intend doing to this table.

    As for the overall design of the table - it depends. You don't give any other information about other tables

    and the relationships between them. You don't give information on what processing you need to do.

  • User_7X4SW
    User_7X4SW Member Posts: 7 Blue Ribbon

    yes records to be updated will be indensified by persion_id.

    will oracle not go for full table scan if i just create an index since this will not be unique index..

    there may be multiple rows for persion_id

  • Paul  Horth
    Paul Horth Member Posts: 3,402 Gold Trophy

    If you create the index and gather statistics, Oracle will choose the best path to access the rows.

    That may be a full table scan or indexed access - depends on the number of rows to be updated and their distribution

    amongst the blocks of the table.

  • User_7X4SW
    User_7X4SW Member Posts: 7 Blue Ribbon

    yes.. records will be indentified by persion_id.

    but since persion_id is not unique, will not oracle go for full scan even if there is an index.

    thats why we thinking of partition.

  • User_7X4SW
    User_7X4SW Member Posts: 7 Blue Ribbon

    we just want to avoid full table scan fro few records out of millions.

  • Hoek
    Hoek Member Posts: 16,087 Gold Crown

    How about: just do it?

    Create an appropriate index and ofcourse Oracle will use it.

    In the time you spent wondering what would happen, you already could have tested this.

    Documentation is clear about this: if you hit only a few rows out of millions, index access will be chosen.

    Partitioning is not useful here.

  • Paul  Horth
    Paul Horth Member Posts: 3,402 Gold Trophy
    00abfbfa-08b4-49e2-98ad-44c04ed2ac37 wrote:
    
    yes.. records will be indentified by persion_id.
    but since persion_id is not unique, will not oracle go for full scan even if there is an index.
    thats why we thinking of partition.
    

    Did you actually read what I wrote?

    The important bit was

    "Oracle will choose the best path to access the rows."

    So, create the index, gather statistics and find out yourself!

This discussion has been closed.