1 2 Previous Next 24 Replies Latest reply: Aug 16, 2011 6:12 AM by Jonathan Lewis Go to original post RSS
      • 15. Re: Local index:prefixed or non-prefixed
        Hemant K Chitale
        Such optimization would be possible only with LIST Partitioning. Since Oracle also allows Composite Partitioning, besides non-List methods, it would be only one of many possible permutations. I don't think Oracle would implement such.

        The Pre-Oracle8 (and still possible where the Partitioning option is not installed) "Partitioned Views" method was/is similar ? Build 4 separate tables and then a Union view on top of them. Allow the optimizer to decide which table (partition) "local" index to scan.


        Hemant K Chitale
        • 16. Re: Local index:prefixed or non-prefixed
          635471
          Hemant K Chitale wrote:
          Such optimization would be possible only with LIST Partitioning.
          Indeed. I suppose that my other hypothetical method would really be what you might call "unique list" partitioning. I also suppose that theoretically list partitioning could be enhanced for automatic creation of new partitions for each new unique value of the partition key column, similar to interval partitioning. Maybe not a common enough requirement, like you say.
          • 17. Re: Local index:prefixed or non-prefixed
            735830
            Jonathan, I have one reason to make local index pre-fixed (but I'm not telling you are not right, just wanna mention interesting example). If you have partition housekeeping based on splitting (to split MAXVALUE partition, for example) and you want to have Fast Splitting (you split partition at value maximal value in partition, one of resulting partiion is empty etc.), prefixed local index will help you. Oracle does not have to find maximum value performing FULL TABLE SCAN on MAXVALUE PARTITION, but it can find maximum from prefixed index by INDEX FULL (MIN/MAX).

            Regards

            Pavol Babel
            • 18. Re: Local index:prefixed or non-prefixed
              Hemant K Chitale
              Yes, it can find the split boundary with the Prefixed Local Index but it still has to do a Full Scan to identify all the rows that go into the new partition. It has to "re-distribute" the rows among the two new partitions. So, why do one Index Scan to know the boundary and then do a Full Partition Scan to "move" the rows ?


              Hemant K Chitale
              • 19. Re: Local index:prefixed or non-prefixed
                Hemant K Chitale
                post quadruplicated !

                Hemant K Chitale

                Edited by: Hemant K Chitale on Mar 1, 2011 2:08 PM
                • 20. Re: Local index:prefixed or non-prefixed
                  Hemant K Chitale
                  post quadruplicated !

                  Hemant K Chitale

                  Edited by: Hemant K Chitale on Mar 1, 2011 2:08 PM
                  • 21. Re: Local index:prefixed or non-prefixed
                    Hemant K Chitale
                    post quadruplicated !

                    Hemant K Chitale

                    Edited by: Hemant K Chitale on Mar 1, 2011 2:08 PM
                    • 22. Re: Local index:prefixed or non-prefixed
                      735830
                      Herman,

                      Oracle does not have to perform full table scan in case one of resulting partitions will be empty. If your partition housekeeping process is smart enough, no rows have to be moved, nor indexes rebuilt.

                      imagine partititioned table T1 with MAXVALUE PARTITION iba tablespace TBS1, partitioned by column ID and local partitioned prefixed index I1. Let me say the maximum value of the column ID is 900 000. If you split MAXVALUE at 1 000 000 into P1 and MAXVALUE , Oracle will

                      1. get the upper boundary of partition MAXVALUE by INDEX FULL (MIN/MAX)
                      2. will just rename old MAXVALUE partition to P1, but no row movement will be needed, no full table scan
                      3. will create new MAXVALUE empty partition
                      4. all indexes will be usable

                      This approach is called FAST PARTITION SPLITTING, I think it was introduced in Oracle 9.2

                      Regards

                      Pavol Babel

                      Edited by: Pavol Babel on Mar 2, 2011 1:22 AM
                      • 23. Re: Local index:prefixed or non-prefixed
                        Hemant K Chitale
                        I know of "Optimizing SPLIT PARTITION". This is when one of the two resulting Partitions is EMPTY.
                        The Optimization here is that GLOBAL Indexes do not get invalidated (i.e. they remain USABLE).



                        However, Oracle has to be able to know that one of the two Partitions -- e..g the upper one -- will be EMPTY. It knows this by reading the rows in the Partitions. This is done by a Full Scan. What you are proposing is using a LOCAL Prefixed Index to identify the MIN or MAX bound and then decide if a Fast Split can be done so as to avoid Row Movement. (However, Oracle may still have to scan both ends of the index).

                        I understand your point. Yet, it could mean some significant code changes.



                        Hemant K Chitale

                        Edited by: Hemant K Chitale on Mar 2, 2011 9:55 AM

                        Edited by: Hemant K Chitale on Mar 2, 2011 10:17 AM
                        • 24. Re: Local index:prefixed or non-prefixed
                          Jonathan Lewis
                          Jonathan Lewis wrote:
                          Uwe Hesse wrote:

                          Generally speaking, a local prefixed index enables partition pruning on the index level for statements that list the index key in ther where clause. A nonprefixed local index doesn't.
                          Uwe,

                          Can you supply a demonstration to show exactly what you mean. I believe that that argument ceased to be true in 8.1.6 or 8.1.7 when the optimizer code caught up with the fact that you could do partition pruning at the index level even if the partitioning column wasn't part of the (local) index. For example:
                          I think it's worth linking this thread forward to an example from C Joshi where a specific execution plan is not available to the optimizer because the index doesn't contain the partitioning column - even though the optimizer could determine that the relevant predicate was redundant after partition pruning: Non-prefixed local indexes and fast full scans

                          (Even in this case, prefixing is not the requirement, but it is a case where a current limitation in the optimizer forces you to add a redundant column to the index; you will find similar examples when you start thinking about index hash joins).


                          Regards
                          Jonathan Lewis
                          1 2 Previous Next