6 Replies Latest reply: Jan 29, 2013 8:31 PM by Hemant K Chitale RSS

    What are concequences of Split partition

    dba-india
      I am using 11.2.0.2 database.

      I got ORA-14074 error says that if you have a MAXVALUE already you will need to either drop the partition that encompasses the MAXVALUE or split partition.
      So I want to use split partition option through the below statement,

      alter table
      tablename
      split partition
      partmax at (XXX)
      into
      (partition partXXX, partition partmax);

      Please can somebody tell me the concequences of this statement.
      I am concerned about the existing data in the table will it by any change gets deleted, and what about indexes, do I have to rebuild, etc.

      I have to do this on produciton.
      I cannot test this as I don't have a test environment and moreover the table is having 70 million records and we don't have time/hardware resources to recreate this by export import in another database.

      Please advise on the bold above text.
        • 1. Re: What are concequences of Split partition
          Niket Kumar
          yes,,,you have to rebuild the indexes....
          • 2. Re: What are concequences of Split partition
            JohnWatson
            Use the UPDATE INDEXES clause if you want to keep the undexes usable.
            • 3. Re: What are concequences of Split partition
              rp0428
              >
              Please can somebody tell me the concequences of this statement.
              I am concerned about the existing data in the table will it by any change gets deleted, and what about indexes, do I have to rebuild, etc.
              >
              No - no data will get deleted. As others have said there are options for keeping the indexes up-to-date or you can rebuild them.
              >
              I cannot test this as I don't have a test environment and moreover the table is having 70 million records and we don't have time/hardware resources to recreate this by export import in another database.
              >
              Total RUBBISH!

              That's the lamest excuse I have heard on the forums in a long time. You have several hours to post and thread and then wait to see if there is anyone you don't know and have never worked with that will respond but you don't have a couple of hours to do a simple test?

              Seventy million records is nothing and if you do the test on a PC using a version of Oracle that you can download for free.

              Anyone that doesn't test changes going into their production environment should NOT be working in this field: they should find a new line of work. That is incompetence at its worst.

              The SPLIT PARTTION test doesn't need a lot of data: just some data for each of the two resulting partitions. You can easily create a clone table using CTAS and then manually add the indexes.
              • 4. Re: What are concequences of Split partition
                dba-india
                Thanks to all for the response.
                I was able to do this task, and to your knowledge please note that Indexes were intact and I needn't rebuild them after completing the split partition task.
                And yes I created the table in test environment by expdp/impdp with compression option and tested the split partition task before applying it on production.

                Once again Thanks to all.
                • 5. Re: What are concequences of Split partition
                  Niket Kumar
                  you have used update indexes clause while splitting the partition as suggested by john.. ???
                  • 6. Re: What are concequences of Split partition
                    Hemant K Chitale
                    I cannot test this as I don't have a test environment and moreover the table is having 70 million records and we don't have time/hardware resources to recreate this by export import in another database.
                    If you want to test performance of the SPLIT PARTITION, you would want to create a test table with many million rows.

                    If you want to test the concept of SPLIT PARTITION (and it's impact on indexes etc), you need to create at table with 2 rows in each partition (including the MAXVALUE partition). The SPLIT should be at a value between the two rows in the partition.


                    Hemant K Chitale