9 Replies Latest reply on Jan 7, 2016 4:45 PM by rp0428

    Three questions on Partitioning

    flying_penguin

      RDBMS version: 11.2.0.4

      OS : RHEL 6.4

      Environment type: OLTP

      ++++++++++++++++++++++++++

       

      We have a big unpartitioned table having 4 Billion records and it is 3TB in size.

       

      We would like to partition this table so that it can be easily maintained. We prefer partitioning on a daily basis.

      We are required by the law to keep the data only for 15 days. So, we will drop these partitions after 15 days.

       

      The existing table uses tablespaceA.

      We would like to range partition (with INTERVAL) by CREATED_DATE column. We would like move the existing data to big base partition called PRE_2016 as shown below.

       

      Rougly, it would look like

       

      create table order_dtl

      (

              order_id                 number,

              billable_flg            not null char(1 char)

              complmntry_flg          not null char(1 char)

              order_quantity_luom     not null varchar2(20)

              .

              .

              .

              created_date            timestamp (6)

      )

      partition by range (created_date)

      interval( numtodsinterval(1,'DAY'))

        PARTITION PRE_2016 VALUES LESS THAN (TO_DATE('31-DEC-2015 23:59:59', 'DD-MON-YYYY HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

      )

      STORE IN (tablespaceA, tablespaceB, tablespaceC);

       

       

      Question1. Any idea how long it will take to do Exchange partitiong using Online Redefinition ? Just a rough estimate would do.

       

      Question2. In the existing table, we have data from the years 2013, 2014 and 2015. When creating the above mentioned partitioned table, Is there a way we could split the base partition for years 2013,2014 and 2015 ?

       

      Question3. After the Online redefinition , I have to manually create the constraints and Indexes . Right ?

       

      Any suggestions/reccomendations are welcome

        • 1. Re: Three questions on Partitioning
          Jonathan Lewis

          You start by saying you're only going to keep 15 days of data, then in question 2 you imply that you want to keep the current 3 years as well.

          If you only need to keep 15 days of data your best strategy should take about 5 minutes to run:

           

          Create an empty interval partitioned table with one partition for (say) data before 6th Jan, with local indexing

          Exchange your current table with the bottom partition

          Wait 15 days

          You will need to work out a few refinements to method to minimise side effects - but "stop time" for the application will be very small.

           

          The biggest problem with your requirement is that Oracle isn't friendly about dropping the partition that is on the boundary between the RANGE bit and the INTERVAL bit - the whole "interval partitioning" thing really needs a lot of work to make it DBA-friendly, it covers a few points and leaves many more that still need site-specific thinking.

           

          Regards

          Jonathan Lewis

          1 person found this helpful
          • 2. Re: Re: Three questions on Partitioning
            flying_penguin

            Thank You Jonathan.

             

            The biggest problem with your requirement is that Oracle isn't friendly about dropping the partition that is on the boundary between the RANGE bit and the INTERVAL bit - the whole "interval partitioning"

             

            But , after the 15th day, I will not have any problem dropping the partitons back from the 13th day and backwards. Right ? Its only the boundary (I assume the 14th day's partiton) I will have trouble with. Right ?

            • 3. Re: Three questions on Partitioning
              Jonathan Lewis

              I'm not quite sure what you have in mind with your comment, but it's easy enough to check whether what you have in mind will work.

              Here are a couple of useful blog posts from Harald van Breederode describing the issues and commenting on strategies to address them.

              http://prutser.wordpress.com/2010/01/11/dropping-interval-partitions/

              https://prutser.wordpress.com/2010/07/26/dropping-interval-partitions-revisited/

               

              Regards

              Jonathan Lewis

              1 person found this helpful
              • 4. Re: Three questions on Partitioning

                 

                Question1. Any idea how long it will take to do Exchange partitiong using Online Redefinition ? Just a rough estimate would do.

                 

                Question2. In the existing table, we have data from the years 2013, 2014 and 2015. When creating the above mentioned partitioned table, Is there a way we could split the base partition for years 2013,2014 and 2015 ?

                 

                Question3. After the Online redefinition , I have to manually create the constraints and Indexes . Right ?

                 

                Any suggestions/reccomendations are welcome

                #1 - you don't use 'Online Redefinition' when you use exchange partition. The exchange will take a few milliseconds.

                 

                #2 - How can the table have data from 2013, 2014 and 2015 if you only keep data for 15 days?

                We are required by the law to keep the data only for 15 days

                The ONLY way you can 'split' data is by physically moving the data. Once that BIG history partition is more than 15 days old just query out the data for 2013 and put it somewhere else.

                 

                If you still want it in the same table you will need to use SPLIT PARTITION.

                 

                #3 - there is no 'redefinition' when you use exchange partition.

                1 person found this helpful
                • 5. Re: Three questions on Partitioning
                  flying_penguin

                  Thank rp0428, Jonathan

                   

                  Question4.

                  For a table which is partitioned daily like the above one, what type of index is better : Local or Global ? This is an OLTP environment

                   

                  Question5

                  So, Online redefinition is needed to only to deal with Indexes and Constraints ? ie. After the partition exchange, if you are going to manually rename the constraints and indexes, then Online redefinition is not needed. Right ?

                  • 6. Re: Re: Three questions on Partitioning
                    flying_penguin

                    For daily partitions, I gather that local indexes are better.

                    Quoting Anurag from

                    https://community.oracle.com/thread/2364044?tstart=0

                     

                    " (when using Global partitions) If you try to drop older partitions then you have to

                    1) Rebuild the entire global indexes.

                    2) Drop the older partitions with update global index clause, which would be much slower.  "

                     

                     

                    What about monthly partitions ?

                    Should I create global or local indexes for monthly partitions?

                    • 7. Re: Three questions on Partitioning
                      Dom Brooks

                      For any partitioning, if you only require local indexes then that is best.

                       

                      But if you need global indexes, then you need global indexes.

                      It is not driven by any particular partitioning scheme but by your data and how you access your data.

                      The drivers are normally either performance (because your queries are not restricted to a single or small number of partitions) or data quality (because your unique/primary constraint does not include the partition key).

                      • 8. Re: Re: Re: Three questions on Partitioning
                        JohnWatson2

                        For daily partitions, I gather that local indexes are better.

                        Better for what? Consider SELECT. You expect to have fifteen partitions. An index search of a non-prefixed local index will therefore have to search fifteen index partitions, which will take fifteen times as long as searching one global index. You may indeed find that the optimizer favours scans. Are you sure you want to partition at all? Partitioning can make sense if you have hundreds or thousands of partitions, but just fifteen? Why bother?

                        • 9. Re: Three questions on Partitioning

                          What about monthly partitions ?

                          Should I create global or local indexes for monthly partitions?

                          RTFM and follow the guidelines in the docs. They tell you, step by step, what the considerations are for choosing the type of index.

                          https://docs.oracle.com/cd/E18283_01/server.112/e16541/partition.htm#i461446

                          Overview of Partitioned Indexes

                          Just like partitioned tables, partitioned indexes improve manageability, availability, performance, and scalability. They can either be partitioned independently (global indexes) or automatically linked to a table's partitioning method (local indexes). In general, you should use global indexes for OLTP applications and local indexes for data warehousing or DSS applications. Also, whenever possible, you should try to use local indexes because they are easier to manage. When deciding what kind of partitioned index to use, you should consider the following guidelines in order:

                          1. If the table partitioning column is a subset of the index keys, use a local index. If this is the case, you are finished. If this is not the case, continue to guideline 2.
                          2. If the index is unique and does not include the partitioning key columns, then use a global index. If this is the case, then you are finished. Otherwise, continue to guideline 3.
                          3. If your priority is manageability, use a local index. If this is the case, you are finished. If this is not the case, continue to guideline 4.
                          4. If the application is an OLTP one and users need quick response times, use a global index. If the application is a DSS one and users are more interested in throughput, use a local index.

                          For more information about partitioned indexes and how to decide which type to use, refer to Chapter 6, "Using Partitioning in a Data Warehouse Environment" and Chapter 7, "Using Partitioning in an Online Transaction Processing Environment".

                          1 person found this helpful