6 Replies Latest reply: Feb 19, 2013 12:41 PM by Omega3 RSS

    Two 'blunt' questions on Table Partitioning ?

    Omega3
      Version: 10.2, 11.2

      I wasn't fortunate enough to work in Partitioning in my DBA career. So , I have 2 basic questions.

      Question1.
      What is the most common form of partitioning you have come across ?

      Question2.
      Generally, would you prefer creating a global index or a local index for a partitioned table ?
      I mean: Global Index for particular type of partitioning or a particular column data type...etc
        • 1. Re: Two 'blunt' questions on Table Partitioning ?
          sb92075
          Omega3 wrote:
          Version: 10.2, 11.2

          I wasn't fortunate enough to work in Partitioning in my DBA career. So , I have 2 basic questions.

          Question1.
          What is the most common form of partitioning you have come across ?
          non-partitioned tables

          >
          Question2.
          Generally, would you prefer creating a global index or a local index for a partitioned table ?
          I mean: Global Index for particular type of partitioning or a particular column data type...etc
          It depends.
          If one was ALWAYS better than the other, then the other should NOT exist.
          • 2. Re: Two 'blunt' questions on Table Partitioning ?
            vlethakula
            It depends, I have seen till now RANGE PARTITIONING (partitioned by date)

            http://richard-e-hall.blogspot.com/2007/08/global-vs-local-indexes.html
            • 3. Re: Two 'blunt' questions on Table Partitioning ?
              Rob_J
              Hi,

              1. Most common approach I've seen is range partitioning on date. Very common because it means you can drop old data very easily with little overhead when it expires without having to delete it slowly and resource intensively.

              2. I would create locally if I could. This would avoid the need to update the indexes as partition maintenance was going on (dropping, adding, etc). If you need to do partition work and you are going to invalidate the global index look at using the UPDATE GLOBAL INDEXES clause.

              There is some more information about [url http://www.ora00600.com/articles/oracle-partitioning.html]Partitioning you can have a read through and I would definitely recommend reading the documenation.

              Rob
              • 4. Re: Two 'blunt' questions on Table Partitioning ?
                rp0428
                >
                I wasn't fortunate enough to work in Partitioning in my DBA career. So , I have 2 basic questions.

                Question1.
                What is the most common form of partitioning you have come across ?

                Question2.
                Generally, would you prefer creating a global index or a local index for a partitioned table ?
                I mean: Global Index for particular type of partitioning or a particular column data type...etc
                >
                Sounds like interview questions to me. What answers to you give to those questions?

                If you are new to partitioning I suggest you review the entire VLDB and Partitioning Guide
                http://docs.oracle.com/cd/B28359_01/server.111/b32024/toc.htm

                That doc covers all aspects of partitioning including the different types of partitioning and types of indexes and when to use each of them.

                Partitioning should only be used when it solves a demonstrated problem that can't be solved using traditional techniques.

                Common forms are RANGE, LIST, HASH, and composites of those. REF partitioning is also used as needed.

                The type of index to use depends on the data and the reason partitioning was used to begin with. Global indexes can degrade the performance of partition maintenance.
                • 5. Re: Two 'blunt' questions on Table Partitioning ?
                  Jonathan Lewis
                  Omega3 wrote:
                  Version: 10.2, 11.2

                  I wasn't fortunate enough to work in Partitioning in my DBA career. So , I have 2 basic questions.

                  Question1.
                  What is the most common form of partitioning you have come across ?
                  Mostly range by time
                  Some list
                  Some range/list or range/hash composite (ranged by time, again)
                  Question2.
                  Generally, would you prefer creating a global index or a local index for a partitioned table ?
                  I mean: Global Index for particular type of partitioning or a particular column data type...etc
                  Mostly local, with a minimum number of global (usually for uniqueness) on D/W
                  More likely to be global with some local for OLTP
                  Some globally hash partitioned indexes on non-partitioned tables

                  Regards
                  Jonathan Lewis
                  • 6. Re: Two 'blunt' questions on Table Partitioning ?
                    Omega3
                    Thank you Rob, Jonathan, rp0428

                    sb92075,


                    Question1.
                    What is the most common form of partitioning you have come across ?

                    non-partitioned tables

                    Question2.
                    Generally, would you prefer creating a global index or a local index for a partitioned table ?
                    I mean: Global Index for particular type of partitioning or a particular column data type...etc

                    It depends.
                    If one was ALWAYS better than the other, then the other should NOT exist.



                    You are a star.
                    You are like the opposite of Billy Verreyne, Frank Kulash, Hemant Chitale, Jonathan Lewis, rp0428, EdStevens, Levi periera, sebastian solbach, Uwe Hesse, Aman, BluShadow, Solomon Yakobsson ..etc

                    Are you from Israel by any chance ?

                    Just don't waste the disk space dude especially when we are going to face the dollar collapse/Depression