8 Replies Latest reply: Sep 22, 2013 1:46 PM by EdStevens RSS

    A table design question

    admin1

      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

          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

            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

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

              • 4. Re: A table design question
                sb92075

                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

                  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

                    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

                      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

                        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.