5 Replies Latest reply: Aug 19, 2013 8:35 PM by Hemant K Chitale RSS

    Partitioning or index organized table. Suggestion required.

    supersen

      Hi Gurus,

       

      We have decided to perfomance increase of table customer which has more than 100 Million records

       

      {code}

      customer_id  number,

      cust_name   varchar,

      Applied_date  date,

      city  varchar(100)

      {code}

       

      This is the customer table structure.

       

      We decided to composite partition the table based on applied date(range) and customer_id( hash).

       

      I am confused to go with index-organized table ( where table and index are stored together)  for better performance.

       

      Please suggest which one i go?  for better performance.

       

      Please reply

       

      Supersen

        • 1. Re: Partitioning or index organized table. Suggestion required.
          Sudharsanan

          In my point of view, we can use both Composite partition and index-organized table.

          considering the table of records having more than 100 Million.

          When we use both the table performance will be higher than considering one.

          Answer to your question is that, the partitioning will be better than indexing

          while considering the largest records.

          • 2. Re: Partitioning or index organized table. Suggestion required.
            dba-developer

            One more thing i would like to add to this while chosing between iot and normal partition table we also need to consider the statistics gathering as in our database we have been adding 5000 partitions daily to partitioned tables which are then analyzed . Which is very important and takes a lot of time.

             

            So my question to experts is : if we gather table stats for a normal partitioned table with cascade=true then it will gather stats for the index also when flase then we need to gather index stats seperately.For IOT how are the stats gathered and are they faster then normal partitioned table.As this is as major factor to be considerd wile choosing the partition type.

            • 3. Re: Partitioning or index organized table. Suggestion required.
              Hemant K Chitale

              >I am confused to go with index-organized table ( where table and index are stored together)  for better performance.

               

              Is it with CUSTOMER_ID as the Primary Key ?

               

              >We decided to composite partition the table based on applied date(range) and customer_id( hash).

              The decision should be driven by

              a. How you insert data

              b. How you query data

              c.  How you plan to maintain (delete or move) data

               

              I presume that applied_date and customer_id will both be incremental -- i.e. every new customer has a higher applied_date and customer_id.

               

              What are queries against this table ?  How is it maintained ?

               

               

              Hemant K Chitale

              • 4. Re: Partitioning or index organized table. Suggestion required.
                supersen

                My answer is

                 

                Customer id is primary key.  we are not inserting any data. Just selecting from the query.

                 

                I would like to know this.

                 

                1) Please tell me it is compulsory to change all the source query , so that it contains parition name in it?

                 

                or simply selecting from the table is enough ?

                 

                Please tell

                 

                Supsen

                • 5. Re: Partitioning or index organized table. Suggestion required.
                  Hemant K Chitale

                  If the query predicates (the WHERE clause) include the Partition Key columns, Oracle can do Partition Pruning -- i.e. identify the target Partition.  Else, it would have to do a Full Table Scan as it wouldn't know which Partition the target row(s) is/are in.

                   

                  For example, if you are partitioning by APPLIED_DATE  but your query is on the table by CITY, Oracle cannot identify the target Partition and has to do a Full Table Scan -- even if you subpartition by CUSTOMER_ID and include CUSTOMER_ID in your query, Oracle cannot identify the Subpartition because it cannot identify the Partition.

                  Hemant K Chitale