5 Replies Latest reply: Oct 28, 2011 1:51 AM by Billy~Verreynne RSS

    How to delete the data from partition table

    877826
      Hi all,

      Am very new to partition concepts in oracle..
      here my question is how to delete the data from partition table.
      is the below query will work ?

      delete from table1 partition (P_2008_1212)

      we have define range partition ...

      or help me how to delete the data from partition table.

      Thanks
      Sree
        • 1. Re: How to delete the data from partition table
          sb92075
          Handle:      874823
          Status Level:      Newbie
          Registered:      Jul 25, 2011
          Total Posts:      39
          Total Questions:      24 (19 unresolved)
          so many questions & so few answers!
          • 2. Re: How to delete the data from partition table
            877826
            Hi Sb,


            Any problem with that ... if so please excuse me ..
            • 3. Re: How to delete the data from partition table
              JustinCave
              Do you want to delete all the data from the partition? If so, perhaps you don't want to delete at all-- perhaps you want to either drop or truncate the partition?

              Justin
              • 4. Re: How to delete the data from partition table
                Vite DBA
                Hi Sree,

                one of the main principles behind partitioning is that you can treat a partitioned table exactly the same as a non-partitioned table from a DML point of view. The user and the application doesn't have to be aware that they are dealing with a partitioned object. So the delete command that you use against a non-partitioned table will work against a partitioned table.

                Perhaps a visit to the documentation would help.

                http://download.oracle.com/docs/cd/E11882_01/server.112/e25789/schemaob.htm#CFAGCECI

                Andre
                • 5. Re: How to delete the data from partition table
                  Billy~Verreynne
                  874823 wrote:

                  delete from table1 partition (P_2008_1212)
                  This approach is wrong - as Andre pointed, this is not how partition tables should be used.

                  Oracle supports different structures for data and indexes. A table can be a hash table or index organised table. It can have B+tree index. It can have bitmap indexes. It can be partitioned. Etc.

                  How the table implements its structure is a physical design consideration.

                  Application code should only deal with the logical data structure. How that data structure is physically implemented has no bearing on application. Does your application need to know what the indexes are and the names of the indexes,in order to use a table? Obviously not. So why then does your application need to know that the table is partitioned?

                  When your application code starts referring directly to physical partitions, it needs to know HOW the table is partitioned. It needs to know WHAT partitions to use. It needs to know the names of the partitions. Etc.

                  And why? All this means is increased complexity in application code as this code now needs to know and understand the physical data structure. This app code is now more complex, has more moving parts, will have more bugs, and will be more complex to maintain.

                  Oracle can take an app SQL and it can determine (based on the predicates of the SQL), which partitions to use and not use for executing that SQL. All done totally transparently. The app does not need to know that the table is even partitioned.

                  This is a crucial concept to understand and get right.