1 2 Previous Next 23 Replies Latest reply: Dec 16, 2012 2:28 PM by 973995 RSS

    Can I use truncate and where clause in the same statement?

    973995
      Hello guys,

      Can I use truncate with where clause in the same statement.


      For ex:

      TRUNCATE TABLE my_table WHERE id < 500;

      If not, what you recommend me in order to decrase the High Water Mark level?


      By the way
      select * from v$version;
      Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
      PL/SQL Release 11.2.0.1.0 - Production
      "CORE     11.2.0.1.0     Production"
      TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
      NLSRTL Version 11.2.0.1.0 - Production
      >

      Thanks a lot.

      Edited by: 970992 on 13.Ara.2012 11:14
        • 1. Re: Can I use truncate and where clause in the same statement?
          LPS
          Truncate is DDL commant and there is no where clause in SQL.
          You need go for the DELETE statement

          DELETE FROM my_table WHERE id < 500;
          commit;
          • 2. Re: Can I use truncate and where clause in the same statement?
            Frank Kulash
            Hi,
            970992 wrote:
            Hello guys,

            Can I use truncate with where clause in the same statement.


            For ex:

            TRUNCATE TABLE my_table WHERE id < 500;
            No, TRUNCATE TABLE means remove all of the rows. It has no options for getting rid of some rows and leaving others; that's what DELETE is for.
            If the table is partitioned, then you can drop certain partitions and leave others alone. This is much faster than DELETE.

            If you want to remove a large number of rows, then
            CREATE TABLE new_my_table
            AS
            SELECT  *
            FROM    my_table
            WHERE   NVL (id, 501) >= 500
            ;
            might be an option. After creating the new table, drop the old one and re-name the new one. Note that this does not copy indexes, constraints, triggers, or privileges.
            • 3. Re: Can I use truncate and where clause in the same statement?
              rp0428
              Whenever you post provide your 4 digit Oracle version (result of SELECT * FROM V$VERSION)
              >
              Can I use truncate with where clause in the same statement.

              For ex:

              TRUNCATE TABLE my_table WHERE id < 500;
              >
              No - and deleting those records and then doing a SHRINK won't necessarily help either. The record with ID=1 might be the record currently at the high water mark so deleting 'WHERE id < 500' wouldn't let the mark be moved at all.
              >
              If not, what you recommend me in order to decrase the High Water Mark level?
              >
              Using the SHRINK clause of the ALTER TABLE command; that is what it is designed for.
              alter table "STEVE"."ORDERS_NEW" shrink space;
              That example is from the 'Reclaiming Wasted Space' chapter of the DBA Guide
              http://docs.oracle.com/cd/B28359_01/server.111/b28310/schema003.htm
              >
              Objects with fragmented free space can result in much wasted space, and can impact database performance. The preferred way to defragment and reclaim this space is to perform an online segment shrink. This process consolidates fragmented free space below the high water mark and compacts the segment. After compaction, the high water mark is moved, resulting in new free space above the high water mark. That space above the high water mark is then deallocated. The segment remains available for queries and DML during most of the operation, and no extra disk space need be allocated.
              • 4. Re: Can I use truncate and where clause in the same statement?
                Stew Ashton
                If the operation can be done offline, how about ALTER TABLE xxxxx MOVE ?
                • 5. Re: Can I use truncate and where clause in the same statement?
                  rp0428
                  >
                  If the operation can be done offline, how about ALTER TABLE xxxxx MOVE ?
                  >
                  I don't have an operation to be done? ;)

                  Perhaps you should reply to OP and suggest that?
                  • 6. Re: Can I use truncate and where clause in the same statement?
                    973995
                    >
                    Using the SHRINK clause of the ALTER TABLE command; that is what it is designed for.
                    alter table "STEVE"."ORDERS_NEW" shrink space;
                    I have read an article that it should be written
                    alter table table_name shrink space compact;
                    before,
                    alter table table_name shrink space;
                    and then,
                    analyze table table_name compute statistics;
                    Is that true? And is it safe?




                    By the way
                    select * from v$version;
                    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
                    PL/SQL Release 11.2.0.1.0 - Production
                    "CORE     11.2.0.1.0     Production"
                    TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
                    NLSRTL Version 11.2.0.1.0 - Production
                    >

                    Edited by: 970992 on 13.Ara.2012 11:15
                    • 7. Re: Can I use truncate and where clause in the same statement?
                      John Spencer
                      970992 wrote:
                      >
                      Using the SHRINK clause of the ALTER TABLE command; that is what it is designed for.
                      alter table "STEVE"."ORDERS_NEW" shrink space;
                      I have read an article that it should be written
                      alter table table_name shrink space compact;
                      before,
                      alter table table_name shrink space;
                      and then,
                      analyze table table_name compute statistics;
                      Is that true? And is it safe?




                      By the way
                      select * from v$version;
                      Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
                      PL/SQL Release 11.2.0.1.0 - Production
                      "CORE     11.2.0.1.0     Production"
                      TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
                      NLSRTL Version 11.2.0.1.0 - Production
                      >

                      Edited by: 970992 on 13.Ara.2012 11:15
                      It really depends on what you want to do, and whether you expect the table to grow again. The command
                      alter table table_name shrink space compact;
                      simply moves all of the data towards the low water mark end of the segment but does not actually move the high water mark.

                      The command
                      alter table table_name shrink space;
                      will move all of the data to the low water mark end and release the space above the high water mark back to the tablespace as free space.

                      If you do the compact first, the shrink space has less work to do, but the net effect would be the same as if you just did shrink space. Effectively it is the choice between two (possibly) shorter operations or one longer operation. In my experience, the time to compact seems to depend on how fragemented the table is, and where and how big the various blcoks of used and unused blocks are.

                      John
                      • 8. Re: Can I use truncate and where clause in the same statement?
                        973995
                        >

                        If you do the compact first, the shrink space has less work to do, but the net effect would be the same as if you just did shrink space. Effectively it is the choice between two (possibly) shorter operations or one longer operation. In my experience, the time to compact seems to depend on how fragemented the table is, and where and how big the various blcoks of used and unused blocks are.

                        >

                        It has been said that it is very harmful to use shrink if your table is being inserted or deleting rows frequently, is that true? Because, I add and delete thousands of rows to my table everyday (However I do not update anything) Do you think should I use shrink?


                        Regards
                        Cahrlie
                        • 9. Re: Can I use truncate and where clause in the same statement?
                          JustinCave
                          If you are doing a few thousand inserts and deletes a day (which is a pretty minimal and infrequent amount of DML), why do you believe that there is unused space in the table that warrants you doing anything? Why do you believe that the high water mark of the table is too high?

                          Justin
                          • 10. Re: Can I use truncate and where clause in the same statement?
                            973995
                            >
                            If you are doing a few thousand inserts and deletes a day (which is a pretty minimal and infrequent amount of DML), why do you believe that there is unused space in the table that warrants you doing anything? Why do you believe that the high water mark of the table is too high?

                            Justin
                            >

                            Because, I manipulate 30 different tables per day. So, a few thousands * 30 tables = make many changes just for per day. That's my concern. I hope, I express myself. What do you recommend?

                            Regards
                            Charlie
                            • 11. Re: Can I use truncate and where clause in the same statement?
                              JustinCave
                              Doing DML on a table does not cause it to waste space regardless of the amount of DML. In the vast majority of cases, there is never a need to reset a table's high water mark or to shrink a table. There are rare cases where that is necessary but that is, well, rare. Do you have any evidence that your table has wasted space that could be reclaimed by shrinking it? Do you have some problem that you believe shrinking the table will solve?

                              Doing a few thousand rows of DML on a few dozen tables every day is still pretty minimal. When people are generally talking about large loads, we're talking about tens or hundreds of millions of rows (or more) in hundreds of tables. The size of a load, though, doesn't have a lot to do with wasted space unless you're doing something like direct-path loads for performance reasons while you are doing a bunch of single-row deletes since direct-path loads cannot use space below the high water mark. If you are doing conventional path loads, which you almost certainly are if you're just inserting a few thousand rows over the course of a day, it seems unlikely that there is anything to shrink.

                              Justin
                              • 12. Re: Can I use truncate and where clause in the same statement?
                                973995
                                >
                                Doing DML on a table does not cause it to waste space regardless of the amount of DML. In the vast majority of cases, there is never a need to reset a table's high water mark or to shrink a table. There are rare cases where that is necessary but that is, well, rare. Do you have any evidence that your table has wasted space that could be reclaimed by shrinking it? Do you have some problem that you believe shrinking the table will solve?

                                Doing a few thousand rows of DML on a few dozen tables every day is still pretty minimal. When people are generally talking about large loads, we're talking about tens or hundreds of millions of rows (or more) in hundreds of tables. The size of a load, though, doesn't have a lot to do with wasted space unless you're doing something like direct-path loads for performance reasons while you are doing a bunch of single-row deletes since direct-path loads cannot use space below the high water mark. If you are doing conventional path loads, which you almost certainly are if you're just inserting a few thousand rows over the course of a day, it seems unlikely that there is anything to shrink.

                                Justin
                                >

                                Justin,

                                The reason that I want to shrink the tables is I get the following errors nowadays.

                                ORA-01654 unable to extend index

                                ORA-01653 unable to extend table

                                *Cause:    Failed to allocate an extent of the required number of blocks for
                                an index segment in the tablespace indicated.
                                *Action:   Use ALTER TABLESPACE ADD DATAFILE statement to add one or more
                                files to the tablespace indicated.


                                So, doesn't it mean that I got problems with high water mark?

                                Regards
                                Charlie
                                • 13. Re: Can I use truncate and where clause in the same statement?
                                  JustinCave
                                  That appears to indicate that you have run out of space in your tablespace. Unless you are confident that the overall size of your table should not have been increasing (say, you know that you are deleting more data every day than you are inserting), the most likely explanation is that your tables have simply grown over time and that your tablespace has run out of space to accomodate that growth. You most likely need to add a new data file, increase the size of an existing data file, or set the data files to autoextend.

                                  Justin
                                  • 14. Re: Can I use truncate and where clause in the same statement?
                                    973995
                                    >

                                    (say, you know that you are deleting more data every day than you are inserting)

                                    >



                                    Visa versa, I am inserting more data than I am deleting. Therefore, the tables are getting bigger.
                                    1 2 Previous Next