1 2 Previous Next 20 Replies Latest reply: Nov 15, 2012 7:17 AM by francy77 RSS

    partition a table

    francy77
      Hi,
      i should want to know what are the benefit to partition a table, because I follow the step on:

      http://docs.oracle.com/cd/B19306_01/server.102/b14231/tables.htm#i1006754

      the example 1.

      but i cannot demostrate some really benefit.

      For example:

      I have a table AVAN with some index, after that i made AVAN partitioned with the same index, How i can demostrate that after partioned the query executed on partioned table is better?


      If there is a solution of course.
      thanks
      Francesco
        • 1. Re: partition a table
          sb92075
          francy77 wrote:
          Hi,
          i should want to know what are the benefit to partition a table, because I follow the step on:

          http://docs.oracle.com/cd/B19306_01/server.102/b14231/tables.htm#i1006754

          the example 1.

          but i cannot demostrate some really benefit.

          For example:

          I have a table AVAN with some index, after that i made AVAN partitioned with the same index, How i can demostrate that after partioned the query executed on partioned table is better?
          compare EXPLAIN PLAN from before & after
          • 2. Re: partition a table
            AlbertoFaenza
            Hi Francesco,

            there has been a recent discussion on a similar topic: {message:id=10679707}

            Inside that thread you can find several links and information.

            Regards.
            Al
            • 3. Re: partition a table
              Paul  Horth
              What makes you think it would be better?

              It depends on the queries you run against the table.

              Only partition if it would be of actual benefit.
              • 4. Re: partition a table
                rp0428
                >
                i should want to know what are the benefit to partition a table, because I follow the step on:

                http://docs.oracle.com/cd/B19306_01/server.102/b14231/tables.htm#i1006754

                the example 1.

                but i cannot demostrate some really benefit.
                >
                Rule #1 - if it ain't broke, don't fix it.

                If you don't know what benefit you will get you should not have partitioned the table to begin with. So you need to start over
                1. Identify a real problem or issue that you have.
                2. Identify possible solutions to the problem or issue
                3. Select one of the possible solutions for further testing and evaluation

                Paritioning is usually implemented to either improve performance of queries or to make it easier to manage the data (e.g. drop or roll off older data).

                See the VLDB and Partitioning Guide for information about the benefits of partitioning and when to partition
                http://docs.oracle.com/cd/B28359_01/server.111/b32024/partition.htm
                • 5. Re: partition a table
                  francy77
                  Hi, I've runned the explain plan on a partitioned table and on a non-partitioned table (both table contain the same data).
                  The table partioned has only an LOCAL index more, than do not have a partitioned table:


                  For the table partioned:
                  SQL> EXPLAIN PLAN SET STATEMENT_ID='PART'
                    2  FOR
                    3  select * from EVENTI_PM e where e.key_id_evento='50000034' and dt_inserimento = (select min(dt_inserimento) from eventi_pm e2
                   where e.key_id_evento=e2.key_id_evento);
                  
                  Spiegato.
                  See the plan:
                  SQL> SELECT
                    2  CARDINALITY,
                    3  BYTES,
                    4  COST,
                    5  POSITION,
                    6  PARTITION_START,
                    7  PARTITION_STOP,
                    8  PARTITION_ID,
                    9  lpad(' ',level-1)||OPERATION||' '||OPTIONS||' '||object_name as Plan
                   10    FROM PLAN_TABLE a
                   11  CONNECT BY prior id = parent_id
                   12          AND prior statement_id = statement_id
                   13    START WITH id = 0
                   14          AND statement_id = 'PART'
                   15    ORDER BY id;
                  
                  CARDINALITY BYTES COST POSITION PARTITION_START PARTITION_STOP  PARTITION_ID PLAN
                  ----------- ----- ---- -------- --------------- --------------- ------------ ---------------------------------------------
                            1   144    5        5                                              SELECT STATEMENT
                            1   144    5        1                                               NESTED LOOPS
                            1    26    3        1                                                VIEW  VW_SQ_1
                            1    17    3        1                                                 HASH GROUP BY
                            2    34    3        1                                                  FIRST ROW
                            2    34    3        1                                                   INDEX RANGE SCAN (MIN/MAX) EVENTO_PK
                            1   118    2        2 KEY             KEY                        6   PARTITION HASH ITERATOR
                            1   118    2        1 KEY             KEY                        6    TABLE ACCESS BY LOCAL INDEX ROWID EVENTI_P
                                                                                               M
                  
                            1          1        1 KEY             KEY                        6     INDEX UNIQUE SCAN INDICE_LOC
                  
                  Selezionate 9 righe.
                  in which the cost is 5.

                  the same thing for a non-partioned table:
                  SQL> EXPLAIN PLAN SET STATEMENT_ID='NON_PART'
                    2  FOR
                    3  select * from EVENTI_PM2 e where e.key_id_evento='50000034' and dt_inserimento = (select min(dt_inserimento) from eventi_pm e
                  2 where e.key_id_evento=e2.key_id_evento);
                  
                  Spiegato.
                  see the plan
                  SQL> SELECT
                    2  CARDINALITY,
                    3  BYTES,
                    4  COST,
                    5  POSITION,
                    6  PARTITION_START,
                    7  PARTITION_STOP,
                    8  PARTITION_ID,
                    9  lpad(' ',level-1)||OPERATION||' '||OPTIONS||' '||object_name as Plan
                   10    FROM PLAN_TABLE a
                   11  CONNECT BY prior id = parent_id
                   12          AND prior statement_id = statement_id
                   13    START WITH id = 0
                   14          AND statement_id = 'NON_PART'
                   15    ORDER BY id;
                  
                  CARDINALITY BYTES COST POSITION PARTITION_START PARTITION_STOP  PARTITION_ID PLAN
                  ----------- ----- ---- -------- --------------- --------------- ------------ ---------------------------------------------
                            1   144    5        5                                              SELECT STATEMENT
                            1   144    5        1                                               NESTED LOOPS
                            1    26    3        1                                                VIEW  VW_SQ_1
                            1    17    3        1                                                 HASH GROUP BY
                            2    34    3        1                                                  FIRST ROW
                            2    34    3        1                                                   INDEX RANGE SCAN (MIN/MAX) EVENTO_PK
                            1   118    2        2                                                TABLE ACCESS BY INDEX ROWID EVENTI_PM2
                            1          1        1                                                 INDEX RANGE SCAN EVENTO_PK2
                  
                  Selezionate 8 righe.
                  but the cost is the same, why?Even if only one partition was used. How i can demostrate to my self that the query in the partitioned table is better?
                  I'm going to get crazy, partioned a table is not useful at all.Please help me!!!!!!!!!!!!!!!!!!!
                  • 6. Re: partition a table
                    francy77
                    Oracle says that if a table is greater than 2 GB, is preferabily to partition.
                    I've have table bigger than 2 GB
                    • 7. Re: partition a table
                      6363
                      How much data does the table have?
                      What partitioning schema and key did you use?
                      How much data does each partition have?
                      What does a real explain plan look like - select * from table(dbms_output.display);
                      Have the tables been analyzed? Your version of a plan shows cardinality estimates of 1 or 2 rows for everything.
                      Follow the instruction in the FAQ for other information it is useful to provide to investigate performance issues
                      {thread:id=2174552}
                      {message:id=9360003}
                      • 8. Re: partition a table
                        rp0428
                        >
                        i should want to know what are the benefit to partition a table, because I follow the step on:

                        http://docs.oracle.com/cd/B19306_01/server.102/b14231/tables.htm#i1006754

                        the example 1.

                        but i cannot demostrate some really benefit.
                        >
                        If you do not get a benefit from partitioning then you should not partition.
                        • 9. Re: partition a table
                          Paul  Horth
                          francy77 wrote:
                          Oracle says that if a table is greater than 2 GB, is preferabily to partition.
                          I've have table bigger than 2 GB
                          Have you got a link to that?

                          I still say it depends.

                          What problem are you actually trying to solve?
                          • 10. Re: partition a table
                            rp0428
                            >
                            Have you got a link to that?
                            >
                            Can't speak to what link OP was referring to but the one that posters often cite is the doc I referenced: the VLDB and Partitioning Guide
                            http://docs.oracle.com/cd/B28359_01/server.111/b32024/partition.htm
                            >
                            When to Partition a Table
                            Here are some suggestions for when to partition a table:
                            •Tables greater than 2 GB should always be considered as candidates for partitioning.
                            . . .
                            >
                            As you know the use of any specify size has been pretty well discredited a number of times.
                            • 11. Re: partition a table
                              francy77
                              My issue is not related to VLDS because the bigger table i have is 9GB.
                              • 12. Re: partition a table
                                SomeoneElse
                                Who told you that partitioning a table would lead to a guaranteed performance improvement?
                                • 13. Re: partition a table
                                  francy77
                                  Have you got a link to that?
                                  
                                  I still say it depends.
                                  
                                  What problem are you actually trying to solve?
                                  what link do you mean? The problem i'm try to solve is to made a database that has grown more than expected still useful, partitionig the tables bigger.
                                  • 14. Re: partition a table
                                    francy77
                                    Oracle documentation:Partition Pruning and Partition-Wise Joins. You think not?
                                    1 2 Previous Next