12 Replies Latest reply on Aug 16, 2019 12:12 PM by Jonathan Lewis

    Can we keep partition on a column that changes value

    $a!|

      Hi All,

       

      I have a table with status column (Active, skipped, completed, failed) etc. It is huge table(above million and half records) and I want to keep a partition on the status column. Now is it wise to keep the partition on the status column since the value changes from one status to another before finally settling to completed.(I have enabled row movement)

      Will this have an impact positively on performance or negatively.

      The status change is one directional (from active to completed) and happens quickly.

       

      Kindly provide your comments.

        • 1. Re: Can we keep partition on a column that changes value
          $a!|

          Edit: The partition is by list

          • 2. Re: Can we keep partition on a column that changes value
            AndrewSayer

            $a!| wrote:

             

            Hi All,

             

            I have a table with status column (Active, skipped, completed, failed) etc. It is huge table(above million and half records) and I want to keep a partition on the status column. Now is it wise to keep the partition on the status column since the value changes from one status to another before finally settling to completed.(I have enabled row movement)

            Will this have an impact positively on performance or negatively.

            The status change is one directional (from active to completed) and happens quickly.

             

            Kindly provide your comments.

            There will be a performance impact, each time the status changes the row will have to move partitions so every Index will also need to be updated to know the new location of the row.

             

            It’s up to you whether that performance impact is worth it, what exactly do you want to get out of this partitioning scheme? What is it helping with that a single index wouldn’t?

            • 3. Re: Can we keep partition on a column that changes value
              $a!|

              Hi Andrew,

               

              Thanks for the reply.

              Will the index gets updated automatically or has it to be done manually?

               

              The table is mainly used for reporting and most of the data belongs to either of Active or Completed. Hence we thought a partition might help.(Please correct me if I am wrong)

              • 4. Re: Can we keep partition on a column that changes value
                AndrewSayer

                $a!| wrote:

                 

                Hi Andrew,

                 

                Thanks for the reply.

                Will the index gets updated automatically or has it to be done manually?

                 

                The table is mainly used for reporting and most of the data belongs to either of Active or Completed. Hence we thought a partition might help.(Please correct me if I am wrong)

                DML will always update the indexes that are useable.

                You will have to enable row movement on the table first to allow updates that move a rows partition.

                 

                I don’t see why it would help really, benefits of the partitioning on that column means you can do a full table scan on just that data or an index range scan that only touches that data. But, youre unlikely to want to read all of the rows of a particular status so you’d end up using an index on (status, other_column) anyway. If you wanted every row of a low cardinality status then an index would be fine here anyway. Of course, the overhead of updating indexes when a row moves partition is going to be similar to the overhead of updating an index when you update a column it uses.

                1 person found this helpful
                • 5. Re: Can we keep partition on a column that changes value
                  Jonathan Lewis

                  Andrew has hit the high points of cost and utility, but there is a variant on the theme that might be worthwhile. As a general rule I'd expect to see most data ending up as "completed", so a strategy that has a single (list) partition for "completed" and a default partition for everything else might give you a small partition for the interesting data and a single big cost for moving data to the big, boring, partition.  This also means you can use partial indexing so that some of the indexes you create for accessing the interesting data can exist only for interesting data and not for the big boring data set - cutting down on the transfer costs. (Historically a similar effect could be achieved by using function-based indexes that used a case/decode on the status to produce mostly null entries - but that required a change to the application code.).

                   

                  Last time I looked, an update that moved a row wouldl

                   

                  • update in place
                  • delete the row from one partition
                  • insert the row in the other partitions

                  so pretty expensive

                   

                  Regars

                  Jonathan Lewis

                  1 person found this helpful
                  • 6. Re: Can we keep partition on a column that changes value
                    BEDE

                    I think you'd better not partition by a column that changes, and that " happens quickly", as you say.

                    Better use another table that will hold only records for Active, rows that will later on be deleted when the status changes. I expect that, at any given moment, you will have a few records with status active compared to the number of records with the status completed. That table should have the primary key columns of the table that holds all the data plus the status column, where the status will be Active or some other status that is bound to change later on, but not records with status which will not change, such as: completed, failed, skipped - I imagine that those are final statuses and won't change.

                    • 7. Re: Can we keep partition on a column that changes value
                      $a!|

                      A general Question.. Is there any use of partitions if our queries don't have the partitioned key column as filter in where clause?

                      • 8. Re: Can we keep partition on a column that changes value
                        MansiRaval

                        I think no, if the partition column is not there in where clause, it will scan through all partitions.

                        • 9. Re: Can we keep partition on a column that changes value
                          Jonathan Lewis

                          $a!| wrote:

                           

                          A general Question.. Is there any use of partitions if our queries don't have the partitioned key column as filter in where clause?

                           

                          In general the answer would be no. Moreover to get the benefits of partition elimination the predicate against the partition key column(s) should use literal values, not bind variables.  At run time the Oracle will check the bind values and pick the correct partitions if the values allow elimination, but at optimisation time Oracle would pick a plan based on the table-level statistics - which tends to be fine for hash partitioning, but can be a disaster for the otherwise cunning use of list partitions.

                           

                          There are a few special cases where you still get some performance benefits from partitioning even when you don't include filter predicates on the partitioning key columns explicitly, but it's best to assume the worst and be grateful for the examples where it works.

                           

                          Regards

                          Jonathan Lewis

                          • 10. Re: Can we keep partition on a column that changes value
                            $a!|

                            But a table with million rows can have tens of different queries upon it. If a partition made on a one column is useful only if the column is in the predicate, there is a good chance that many number of queries will not actually satisfy the condition. In that case, for those queries would the performance not degrade?

                            • 11. Re: Can we keep partition on a column that changes value
                              AndrewSayer

                              $a!| wrote:

                               

                              But a table with million rows can have tens of different queries upon it. If a partition made on a one column is useful only if the column is in the predicate, there is a good chance that many number of queries will not actually satisfy the condition. In that case, for those queries would the performance not degrade?

                              They can degrade, they can improve.

                              It depends on how the access changes due to splitting up the table

                               

                              Some examples;

                              If you were still accessing on a global index and the data it finds has a similar clustering after the partitioning then you’ll probably see no change. Likewise, the partitioning could lead to better or worse clustering due to the grouping by partition.

                              If you were accessing on a local index then you would have to do the index reads multiple times if you don’t filter out a partition, but whether that has a large impact will depend on the size of the index, the amount that you would search in the index and the number of partitions.

                               

                              It‘s one big balancing act, you must benchmark your real application and see if it benefits more than it suffers (weighting the priority of its processes appropriately) 

                              • 12. Re: Can we keep partition on a column that changes value
                                Jonathan Lewis

                                $a!| wrote:

                                 

                                But a table with million rows can have tens of different queries upon it. If a partition made on a one column is useful only if the column is in the predicate, there is a good chance that many number of queries will not actually satisfy the condition. In that case, for those queries would the performance not degrade?

                                 

                                That's a bit like saying "If I create an index on one column the table may be subject to lots of queries that don't use that column....."  With indexing, queries that can't make any use of that index don't get slower when you create it. (Although parse times might increase by a tiny, usually imperceptible, amount), but the performance of DML (inserts, updates, deletes) will tend to degrade.  With partitioning some queries will get slower if you partition the table and recreate every existing index as locally partitioned, some queries may even pick completely different execution plans, As with all things Oracle you need to understand the pros and cons of the technology, do some specific testing, and then hope that you don't hit an odd side effect that you didn't consider when you were testing.

                                 

                                There's an old presentation (2008) of mine on the NoCOUG site that's worth reviewing - it's still highly relevant up to slide 28, but the technology has move on somewhat since then so slides 29 onwards are not to be trusted.


                                Regards

                                Jonathan Lewis