1 2 3 Previous Next 39 Replies Latest reply on Mar 2, 2020 6:07 PM by Joerg.Sobottka

    Table partitioning

    3125743

      Hi,

       

      We have database on Oracle 12.2.0.1.0, in the DB design phase.

       

      I have 2 questions:

       

      1. Just saw the documentation that says starting from Oracle 12.2 a non partitooned table into a partioned one online. Is this completely online activity without any impact to the database? Let me know the drawbacks /issues is any with this.

       

      2. If we decide to go with Monthly partitions to start with and later need to change to daily partitions, can this be done online? If yes please provide some useful links with examples.

       

       

      Thank you.

        • 1. Re: Table partitioning
          Sunny kichloo

          Below mentioned are the responses to your queries:

           

          1. Just saw the documentation that says starting from Oracle 12.2 a non partitooned table into a partioned one online. Is this completely online activity without any impact to the database? Let me know the drawbacks /issues is any with this.

           

          --Yes it can be done online without much impact to database.Performance overhead may come sometime depends on size of table that is to be partitioned but there is no need to take table offline:

           

          https://oracle-base.com/articles/12c/online-conversion-of-a-non-partitioned-table-to-a-partitioned-table-12cr2#restricti…

           

          2. If we decide to go with Monthly partitions to start with and later need to change to daily partitions, can this be done online? If yes please provide some useful links with examples

          --IF you want to just convert your monthly partition to daily partition for future data you can achieve it by using NUMTODSINTERVAL

          https://docs.oracle.com/database/121/VLDBG/GUID-497B9404-C945-4665-B9B6-C67385785BD3.htm

           

          IF you want to convert already existing monthly partitioned data to daily partition data you have to use SPLIT PARTITION clause:

          https://oracle-base.com/articles/12c/online-split-partition-and-subpartition-12cr2

          • 2. Re: Table partitioning
            3125743

            Thanks for the response.

             

            I modified one of the non partitioned tables as below:

             

            ALTER TABLE TEST MODIFY

            PARTITION BY RANGE (INSERT_DATE)

            INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))

            (

            PARTITION P01_2020 values less than (TO_DATE('01-FEB-2020','DD-MON_YYYY')),

            PARTITION P02_2020 values less than (TO_DATE('01-MAR-2020','DD-MON_YYYY')),

            PARTITION P03_2020 values less than (TO_DATE('01-APR-2020','DD-MON_YYYY')),

            PARTITION P04_2020 values less than (TO_DATE('01-MAY-2020','DD-MON_YYYY')),

            PARTITION P05_2020 values less than (TO_DATE('01-JUN-2020','DD-MON_YYYY')),

            PARTITION P06_2020 values less than (TO_DATE('01-JUL-2020','DD-MON_YYYY')),

            PARTITION P07_2020 values less than (TO_DATE('01-AUG-2020','DD-MON_YYYY')),

            PARTITION P08_2020 values less than (TO_DATE('01-SEP-2020','DD-MON_YYYY')),

            PARTITION P09_2020 values less than (TO_DATE('01-OCT-2020','DD-MON_YYYY')),

            PARTITION P10_2020 values less than (TO_DATE('01-NOV-2020','DD-MON_YYYY')),

            PARTITION P11_2020 values less than (TO_DATE('01-DEC-2020','DD-MON_YYYY')),

            PARTITION P12_2020 values less than (TO_DATE('01-JAN-2021','DD-MON_YYYY')),

            PARTITION P01_2021 values less than (TO_DATE('01-FEB-2021','DD-MON_YYYY')),

            PARTITION P02_2021 values less than (TO_DATE('01-MAR-2021','DD-MON_YYYY')),

            PARTITION P03_2021 values less than (TO_DATE('01-APR-2021','DD-MON_YYYY')),

            PARTITION P04_2021 values less than (TO_DATE('01-MAY-2021','DD-MON_YYYY')),

            PARTITION P05_2021 values less than (TO_DATE('01-JUN-2021','DD-MON_YYYY')),

            PARTITION P06_2021 values less than (TO_DATE('01-JUL-2021','DD-MON_YYYY')),

            PARTITION P07_2021 values less than (TO_DATE('01-AUG-2021','DD-MON_YYYY')),

            PARTITION P08_2021 values less than (TO_DATE('01-SEP-2021','DD-MON_YYYY')),

            PARTITION P09_2021 values less than (TO_DATE('01-OCT-2021','DD-MON_YYYY')),

            PARTITION P10_2021 values less than (TO_DATE('01-NOV-2021','DD-MON_YYYY')),

            PARTITION P11_2021 values less than (TO_DATE('01-DEC-2021','DD-MON_YYYY')),

            PARTITION P12_2021 values less than (TO_DATE('01-JAN-2022','DD-MON_YYYY'))

            );

             

            Then I inserted 4 new rows  with one of the rows having INSERT_DATE as 7th Feb 2020  and  rest 2 rows with insert date for year 2022 and 2024.

             

            Below is the o/p after gather stats for the table:

             

            PARTITION_NAME                                                                                                                     NUM_ROWS

            -------------------------------------------------------------------------------------------------------------------------------- ----------

            P01_2020                                                                                                                             708230

            P01_2021                                                                                                                                  0

            P02_2020                                                                                                                                  1

            P02_2021                                                                                                                                  0

            P03_2020                                                                                                                                  0

            P03_2021                                                                                                                                  0

            P04_2020                                                                                                                                  0

            P04_2021                                                                                                                                  0

            P05_2020                                                                                                                                  0

            P05_2021                                                                                                                                  0

            P06_2020                                                                                                                                  0

            P06_2021                                                                                                                                  0

            P07_2020                                                                                                                                  0

            P07_2021                                                                                                                                  0

            P08_2020                                                                                                                                  0

            P08_2021                                                                                                                                  0

            P09_2020                                                                                                                                  0

            P09_2021                                                                                                                                  0

            P10_2020                                                                                                                                  0

            P10_2021                                                                                                                                  0

            P11_2020                                                                                                                                  0

            P11_2021                                                                                                                                  0

            P12_2020                                                                                                                                  0

            P12_2021                                                                                                                                  0

            SYS_P6514                                                                                                                               3

             

            except for the Feb 2020 record, all the other records are residing in a single system generated partition SYS_P6514. Are they not supposed to be in separate partitions as they belong to different months and years?

             

            I also executed below command and added to new records to see if new day partition is getting created. but I am not able to see that as well. Please advise.

             

            ALTER TABLE TEST SET INTERVAL (NUMTODSINTERVAL(1,'DAY'));

            • 3. Re: Table partitioning
              Sunny kichloo

              Can you share your insert query that you have used?

              • 4. Re: Table partitioning
                3125743

                INSERT INTO TEST(INSERT_DATE) VALUES('7-FEB-2020');

                INSERT INTO TEST(INSERT_DATE) VALUES('20-JAN-2022');

                INSERT INTO TEST(INSERT_DATE) VALUES('20-JAN-2024');

                INSERT INTO TEST(INSERT_DATE) VALUES('21-JAN-2024');

                INSERT INTO TEST(INSERT_DATE) VALUES('23-JAN-2024');--to check if daily partitions are being added

                • 5. Re: Table partitioning
                  AndrewSayer

                  3125743 wrote:

                   

                  Hi,

                   

                  We have database on Oracle 12.2.0.1.0, in the DB design phase.

                   

                  I have 2 questions:

                   

                  1. Just saw the documentation that says starting from Oracle 12.2 a non partitooned table into a partioned one online. Is this completely online activity without any impact to the database? Let me know the drawbacks /issues is any with this.

                   

                  2. If we decide to go with Monthly partitions to start with and later need to change to daily partitions, can this be done online? If yes please provide some useful links with examples.

                   

                   

                  Thank you.

                  The first rule of partitioning is to make sure you’re doing it for a reason. Partitioning by a date is great if you’re going to use that date to drop data later, and you mainly use local partitions and the amount of data you drop at once is considerably large. It is generally not a go faster button. It will make your normal queries slower if they now have to use local indexes without knowing what partition they need to use (and generally applications should not care when a row was inserted).

                   

                  We see terrible advise like partition your table when it becomes huge with wide ranges of what counts as huge. If you haven’t designed your queries to take advantage of the partitioning then they will suffer or you are still using mainly global indexes and you won’t benefit from any of the main partitioning benefits anyway.

                   

                  Before you take a step further, make sure this is something that will actually help you. Partitioning for performance can be done, but it needs to consider what your application actually filters with.

                  • 6. Re: Table partitioning
                    3125743

                    Ours is  BI application data warehouse , so data would be queried mostly on date range and retained and purged after fixed no of years.

                     

                    We are planning to have monthly interval partitions on insert date column for all fact tables with local indexes.

                     

                    Please advise if there is anything generally wrong you foresee with above strategy. Thank you.

                    • 7. Re: Table partitioning
                      AndrewSayer

                      3125743 wrote:

                       

                      Ours is BI application data warehouse , so data would be queried mostly on date range and retained and purged after fixed no of years.

                       

                      We are planning to have monthly interval partitions on insert date column for all fact tables with local indexes.

                       

                      Please advise if there is anything generally wrong you foresee with above strategy. Thank you.

                      Queries that filter on the partition key as a range and the range only crosses a small number of partitions, should be fine.

                      Everything else may be slower.

                      • 8. Re: Table partitioning
                        Jonathan Lewis

                        If you've actually done exactly what you said - without any typing errors - then you are right to be surprised to see only one system generated partition. You should have see two, one to hold the Jan 2022 row, and one to hold the Jan 2024 rows (with the Feb 2020 going into the pre-existing p02_2020 partition.

                         

                        What does the result set look like if you now query

                        select insert_date from test where insert_date >= to_date('01-Feb-2010','dd-mon-yyyy');

                         

                        When you're reporting data about partitions you may find it easier to read if you include an "order by partition_position" clause; and if you want to check which partition(s) have been added you can select the high_value from user_tab_partitions.

                         

                        Regards

                        Jonathan Lewis

                        • 9. Re: Table partitioning
                          3125743

                          Thanks. Could you please elaborate on this please ?

                           

                          Everything else may be slower.

                          • 10. Re: Table partitioning
                            3125743

                            Thank you. I have copied from the screen and no typos.

                             

                            Below query returns all the rows in the table. and that is expected as all the records have insert_date post Feb 2010.

                             

                            select insert_date from test where insert_date >= to_date('01-Feb-2010','dd-mon-yyyy');

                             

                            INSERT_DATE

                            ---------------------

                            18/10/19

                            18/10/19

                            18/10/19

                            18/10/19

                            18/10/19

                            18/10/19

                            07/02/20

                            20/01/22

                            23/01/24

                            20/01/24

                            21/01/24

                             

                             

                            708235 rows selected.

                             

                             

                            SQL>  select count(1) from TEST;

                             

                             

                              COUNT(1)

                            ----------

                                708235

                             

                             

                            SQL>

                            • 11. Re: Table partitioning
                              Jonathan Lewis

                              I missed the "2" - that should have been "01-Feb-2020".

                               

                              What do you see if you execute:

                              select  * from test partition for (to_date('20-Jan-2022'));

                               

                              What do you get for

                               

                              set linesize 132

                              set trimspool on

                              column high_value format a80

                              select partition_name, high_value from user_tab_partitions where table_name = 'TEST';

                               

                              What was the exact comment you used to gather stats ?

                               

                              Regards

                              Jonathan Lewis

                              • 12. Re: Table partitioning
                                3125743

                                1. First query gives error:

                                 

                                                               *

                                ERROR at line 1:

                                ORA-14763: Unable to resolve FOR VALUES clause to a partition number

                                 

                                2. Below is the o/p for second sql:

                                 

                                PARTITION_NAME  HIGH_VALUE

                                --------------- --------------------------------------------------------------------------------

                                P01_2020        TO_DATE(' 2020-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

                                P01_2021        TO_DATE(' 2021-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

                                P02_2020        TO_DATE(' 2020-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

                                P02_2021        TO_DATE(' 2021-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

                                P03_2020        TO_DATE(' 2020-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

                                P03_2021        TO_DATE(' 2021-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

                                P04_2020        TO_DATE(' 2020-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

                                P04_2021        TO_DATE(' 2021-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

                                P05_2020        TO_DATE(' 2020-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

                                P05_2021        TO_DATE(' 2021-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

                                P06_2020        TO_DATE(' 2020-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

                                P06_2021        TO_DATE(' 2021-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

                                P07_2020        TO_DATE(' 2020-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

                                P07_2021        TO_DATE(' 2021-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

                                P08_2020        TO_DATE(' 2020-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

                                P08_2021        TO_DATE(' 2021-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

                                P09_2020        TO_DATE(' 2020-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

                                P09_2021        TO_DATE(' 2021-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

                                P10_2020        TO_DATE(' 2020-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

                                P10_2021        TO_DATE(' 2021-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

                                P11_2020        TO_DATE(' 2020-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

                                P11_2021        TO_DATE(' 2021-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

                                P12_2020        TO_DATE(' 2021-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

                                P12_2021        TO_DATE(' 2022-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

                                SYS_P6514       TO_DATE(' 2024-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

                                 

                                 

                                25 rows selected.

                                 

                                3. Below was used to gather stats:

                                 

                                EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA_NAME','TEST');

                                • 13. Re: Table partitioning
                                  AndrewSayer

                                  3125743 wrote:

                                   

                                  Thanks. Could you please elaborate on this please ?

                                   

                                  Everything else may be slower.

                                  A selective table filter that does not filter on partition key that used to use an index would have just done one index range scan. With your partitioned table with N partitions, if your index is now local it will have to do N index range scans.

                                   

                                  Where the table was previously joined to with a nested loop, it would have done D index range scans (Where D is the number of rows in the driving table) now it will do D*N. This multiplier has the classic output of already slow things being worse hit.

                                  • 14. Re: Table partitioning
                                    3125743

                                    Thank you. I just noticed that most of the reports are based on Aggregate tables and the predicate does not use insert_date but TXN_DT which is the transaction date. In such scenario is it advisable to have the partition_key as insert_date or TX_date?Is it a good idea to have partition_key as insert date and create bitmap index on TXN_DATE? would it make sense?  pls advise.

                                    1 2 3 Previous Next