This discussion is archived
8 Replies Latest reply: Sep 22, 2013 11:46 AM by EdStevens RSS

A table design question

admin1 Newbie
Currently Being Moderated

Suppose I have a big table with 10 million rows. It essentially stores entities and their statuses. On any day a status could potentially change.

 

How should the table be designed so 1. it can give quick retrial to get status of a given date. 2. allows quick update (either a few entry or mass update).

  • 1. Re: A table design question
    sb92075 Guru
    Currently Being Moderated

    admin1 wrote:

     

    Suppose I have a big table with 10 million rows. It essentially stores entities and their statuses. On any day a status could potentially change.

     

    How should the table be designed so 1. it can give quick retrial to get status of a given date. 2. allows quick update (either a few entry or mass update).

    Do you know SQL?

    Please post CREATE TABLE statement for the table.

     

     

     

    How do I ask a question on the forums?

    https://forums.oracle.com/message/9362002#9362002

  • 2. Re: A table design question
    EdStevens Guru
    Currently Being Moderated

    admin1 wrote:

     

    Suppose I have a big table with 10 million rows. It essentially stores entities and their statuses.

    That describes just about every table in every rdbms on the planet. 

     

    On any day a status could potentially change.

     

    That describes a great majority of tables on the planet.

     

    How should the table be designed so 1. it can give quick retrial to get status of a given date. 2. allows quick update (either a few entry or mass update).

    For (1) you would need to design so that you have a separate row for every change of status, with each row including the date (a DATE column) that the given status became effective.   Maybe you need two tables.  Just design to Third Normal Form.  start here Database normalization - Wikipedia, the free encyclopedia

     

    For (2), again, design to Third Normal Form.

  • 3. Re: A table design question
    admin1 Newbie
    Currently Being Moderated

    I disagree that this applies to most tables as it has 10 million rows.

  • 4. Re: A table design question
    sb92075 Guru
    Currently Being Moderated

    Please post CREATE TABLE statement for the table.

     

    How do I ask a question on the forums?

    https://forums.oracle.com/message/9362002#9362002

  • 5. Re: A table design question
    JohnWatson Guru
    Currently Being Moderated

    admin1 wrote:

     

    I disagree that this applies to most tables as it has 10 million rows.

    For  table as small as this, I wouldn't have thought that you need worry about design. Just a normal heap structure with appropriate indexes.

  • 6. Re: A table design question
    rp0428 Guru
    Currently Being Moderated

    admin1 wrote:

     

    Suppose I have a big table with 10 million rows. It essentially stores entities and their statuses. On any day a status could potentially change.

     

    How should the table be designed so 1. it can give quick retrial to get status of a given date. 2. allows quick update (either a few entry or mass update).

    What you are referring to is a type 2 slowly changing dimension.

     

    So your first step would be to review ALL of the sections pertaining to that in this doc

    Oracle® Warehouse Builder Data Modeling, ETL, and Data Quality Guide

    http://docs.oracle.com/cd/E18283_01/owb.112/e10935/dim_objects.htm#BEIHFEAG

    Overview of Slowly Changing Dimensions

    A Slowly Changing Dimension (SCD) is a dimension that stores and manages both current and historical data over time in a data warehouse. In data warehousing, there are three commonly recognized types of SCDs. describes the types of SCDs, as described in Table 3-1.

     

    Table 3-1 Types of Slowly Changing Dimensions

    TypeDescription

    Type 1

    Stores only one version of the dimension record. When a change is made, the record is overwritten and no historic data is stored.

    Type 2

    Stores multiple versions of the same dimension record. When the dimension record is modified, new versions are created while the old ones are retained.

    Type 3

    Stores one version of the dimension record. This record stores the previous value and current value of selected attributes.

     

    Your second step would be to use Oracle' Warehouse Builder tool to build the dimension table automatically for you as discussed in that same doc.,

    http://docs.oracle.com/cd/E18283_01/owb.112/e10935/dim_objects.htm#BEIHFEAG

    Creating Slowly Changing Dimensions

    You can create an SCD either using the Create Dimension Wizard or the Dimension Editor.

  • 7. Re: A table design question
    fleboho Newbie
    Currently Being Moderated

    I also agree with the other guys.

     

    WIth the provided information, go with indexing the proper fields.

     

    For what purpose is this particular table?

    At what rate is it growing?

    Is it growing at all?

  • 8. Re: A table design question
    EdStevens Guru
    Currently Being Moderated

    admin1 wrote:

     

    I disagree that this applies to most tables as it has 10 million rows.

     

    I was referring to "It essentially stores entities and their statuses"

     

    Tracking "entities" is what tables are for ...

    And what is it that one would track about "entities"?  Why, the status of various properties of that entity, of course.

     

    And in today's world, 10 million rows is not really considered an "issue".

     

    So I stand by my first response ... design to Third Normal Form.  Or present a well-thought out case as to why you should not design to 3NF.

Legend

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