7 Replies Latest reply: Feb 11, 2013 5:11 PM by sb92075 RSS

    PARTITIONS

    mbp
      Hi,

      I have three questions about partitions:

      *1.* I have users querying a sales datamart for months and years, my quiestion is what is better regarding to performance and load data in sales datamart, from the follow options:

      first one: create partitions by month ?
      second one: create partitions by year with suparttions by month ?

      *2.* Can I create a local index for every suppartition or I just can to create local index for partitions or it is no necessary to specify partition or subparttions names in local index when I include "key partition" inside index. By example:

      If I have a partition by date column c2...

      create index sales_idx on sales
      (c1, c2) local

      is it enough for attaching partition table and local index ??

      *3.* Interval partitions is a good option for simplifying partitions management because Oracle manages partitions automaticly and I do not worry about partitions names, so how can I do it, when I have Partitions by "year" and subpartitions by "month" ?

      I will appreciate your assistance

      thanks
        • 1. Re: PARTITIONS
          sb92075
          987144 wrote:
          Hi,

          I have three questions about partitions:

          *1.* I have users querying a sales datamart for months and years, my quiestion is what is better regarding to performance and load data in sales datamart, from the follow options:
          quantify better

          which metric measures "better"?

          is higher value better or worse than alternative?

          How do I ask a question on the forums?
          SQL and PL/SQL FAQ
          • 2. Re: Partitions
            mbp
            thank you for your recomendations

            Regarding to first question I mean: with partition or partition and subpartition what decrease more answer time in OBI and load time in ETL packages, because I have performance problems. some queries takes 15 minutes or more. nowdays tables are nonpartitioned.

            thanks
            • 3. Re: Partitions
              sb92075
              987144 wrote:
              thank you for your recomendations

              Regarding to first question I mean: with partition or partition and subpartition what decrease more answer time in OBI and load time in ETL packages, because I have performance problems. some queries takes 15 minutes or more. nowdays tables are nonpartitioned.

              thanks
              post both EXPLAIN PLAN for slow & fast same SQL
              • 4. Re: PARTITIONS
                rp0428
                >
                I have three questions about partitions:

                1. I have users querying a sales datamart for months and years, my quiestion is what is better regarding to performance and load data in sales datamart, from the follow options:

                first one: create partitions by month ?
                second one: create partitions by year with suparttions by month ?
                >
                No difference in performance or data loads. Data in partitioned/subpartitioned tables is stored in segments that correspond to the finest granularity. So each segment for either of those options represents ONE month of data.

                The only difference between the two methods is that partitioning by year allows you to refer to a 'year' at a time: a partition refers to a whole year of data. The data itself is still stored in separate segments, one for each month.
                >
                2. Can I create a local index for every suppartition or I just can to create local index for partitions or it is no necessary to specify partition or subparttions names in local index when I include "key partition" inside index. By example:

                If I have a partition by date column c2...

                create index sales_idx on sales
                (c1, c2) local

                is it enough for attaching partition table and local index ??
                >
                You create an index on the table, not the partition or subpartition. Your second statement is correct.


                See the VLDB and Partitioning doc
                http://docs.oracle.com/cd/B28359_01/server.111/b32024/part_avail.htm#sthref434
                >
                Local Partitioned Indexes
                In a local index, all keys in a particular index partition refer only to rows stored in a single underlying table partition. A local index is created by specifying the LOCAL attribute.

                Oracle constructs the local index so that it is equipartitioned with the underlying table. Oracle partitions the index on the same columns as the underlying table, creates the same number of partitions or subpartitions, and gives them the same partition bounds as corresponding partitions of the underlying table.

                Oracle also maintains the index partitioning automatically when partitions in the underlying table are added, dropped, merged, or split, or when hash partitions or subpartitions are added or coalesced. This ensures that the index remains equipartitioned with the table.

                A local index can be created UNIQUE if the partitioning columns form a subset of the index columns. This restriction guarantees that rows with identical index keys always map into the same partition, where uniqueness violations can be detected.
                >
                Oracle will equi-partition the index. Your example that includes the partition key as a non-leading column in the index will create a 'non-prefixed index'. See that section in the same doc
                >
                Local Prefixed Indexes
                A local index is prefixed if it is partitioned on a left prefix of the index columns. For example, if the sales table and its local index sales_ix are partitioned on the week_num column, then index sales_ix is local prefixed if it is defined on the columns (week_num, xaction_num). On the other hand, if index sales_ix is defined on column product_num then it is not prefixed.

                Local prefixed indexes can be unique or nonunique.
                >
                As for your third question:
                >
                3. Interval partitions is a good option for simplifying partitions management because Oracle manages partitions automaticly and I do not worry about partitions names, so how can I do it, when I have Partitions by "year" and subpartitions by "month" ?
                >
                As said above you don't really need a subpartitioned table but if that is what you want then just use a virtual column for the month and use RANGE-LIST partitioning. Here is a sample
                DROP TABLE PART_YEAR_MONTH_INTERVAL
                
                CREATE TABLE PART_YEAR_MONTH_INTERVAL
                (
                  MWF_ORG            VARCHAR2(1),
                  MWF_SEQ            VARCHAR2(12),
                  MWF_DATE           DATE,
                  MWF_MONTH          VARCHAR2(2) GENERATED ALWAYS AS (TO_CHAR(MWF_DATE, 'MM')) VIRTUAL
                )
                PARTITION BY RANGE (MWF_DATE)
                INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
                  SUBPARTITION BY LIST (MWF_MONTH)
                    SUBPARTITION TEMPLATE
                    (
                         SUBPARTITION JAN_2012  VALUES ('01'),
                         SUBPARTITION FEB_2012  VALUES ('02'),
                         SUBPARTITION MAR_2012  VALUES ('03'),
                         SUBPARTITION APR_2012  VALUES ('04'),
                         SUBPARTITION MAY_2012  VALUES ('05'),
                         SUBPARTITION JUN_2012  VALUES ('06'),
                         SUBPARTITION JUL_2012  VALUES ('07'),
                         SUBPARTITION AUG_2012  VALUES ('08'),
                         SUBPARTITION SEP_2012  VALUES ('09'),
                         SUBPARTITION OCT_2012  VALUES ('10'),
                         SUBPARTITION NOV_2012  VALUES ('11'),
                         SUBPARTITION DEC_2012  VALUES ('12')
                    )
                (
                    PARTITION PRE_2012 VALUES LESS THAN (TO_DATE('01-01-2012', 'DD-MM-YYYY'))
                )
                • 5. Re: PARTITIONS
                  mbp
                  Thank you so much for your help !!!

                  I have last question: when is it recommended using a different tablespace for every partition ?

                  thanks aggain for your help
                  • 6. Re: PARTITIONS
                    rp0428
                    >
                    I have last question: when is it recommended using a different tablespace for every partition ?
                    >
                    I know of no such recommendation. Put all partitions in the same tablespace or use multple tablespaces; it is up to you.

                    It is primarily about tablespace or partition maintenance. If you want to (choose one: drop, take offline, make readonly) one or multple partitions you can do it at the tablespace level or the partition level.
                    • 7. Re: PARTITIONS
                      sb92075
                      mbp wrote:
                      Thank you so much for your help !!!

                      I have last question: when is it recommended using a different tablespace for every partition ?
                      WHY?
                      Who is silly enough to make such a weird recommendation?