DBAsupport.com Forums - Powered by vBulletin
Page 3 of 3 FirstFirst 123
Results 21 to 28 of 28

Thread: Performance of delete operation

  1. #21
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    Do you have an on delete trigger? If so look at what it is doing. If you are doing alot of DML in an on delete trigger then it would generate alot of redo. I would minimize the DML in any on delete triggers that you have and take a serious look at partitioning. I don't believe that 7.3.4 has partitioning, I think that it was added with Oracle 8. So you may need to upgrade, since Oracle 7.3.4 is being desupported anyway, you should be on at least 8i.



  2. #22
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Heck, if you're upgrading, upgrade to 9.0.1.2.
    Jeff Hunter

  3. #23
    Join Date
    Aug 2000
    Posts
    32

    Unhappy

    Operating System is HP UNIX. We can not upgrade oracle to latest version because our application supports only 7.3. We might upgrade the application in the near future.

    But my question is why not I use another table instead of using paritions. How do I benefit by paritioning in this case?

    And "Alter table . nologging" is not going to help me anyway since I don't have LOB.

    I feel upgrading Oracle to latest version may not solve my problem.

  4. #24
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Since partitions can be thought of as mini-tables, you could do it with many little tables. I think v7 has the concept of partition-views which allows you to create a view that is a union of all the partitions.

    IMHO, partitioning will solve your problem if you upgrade, but what do I know...

    [Edited by marist89 on 11-27-2001 at 03:47 PM]
    Jeff Hunter

  5. #25
    Join Date
    Sep 2000
    Posts
    384
    say your table name as emp
    Rename your original table to emp_prod
    create table emp as select * from ....(give the condition )
    truncate the emp_prod ;
    if you have any triggers/procedure etc have them and recreate them ,,,
    Radhakrishnan.M

  6. #26
    Join Date
    Aug 2000
    Posts
    32

    Smile

    I don't have any triggers on this table.

    Create table....as select * from.... may be this is good idea if it won't generate more log files. I should try this. Thank you very much for all your help.

    Looking forward for more suggestions.

    Thanks again,
    Vimala

  7. #27
    Join Date
    Jul 2000
    Posts
    521
    Vimala,
    How does the data get generated in this table ?
    I have been able to do something similar to this in the past :

    Lets say your table is DAILY_RECORDS.
    Create a "CREATE TABLE..." script. This script should be able to accept a parameter for the table name.
    Every midnight, execute this script to create a new table with some logic (say the date) built in the tale name. eg. DAILY_RECORDS_11272001. This parameter should automatically change everyday.
    After this table (say DAILY_RECORDS_11272001) gets created, create a synonym DAILY_RECORDS that will point to DAILY_RECORDS_11272001.
    Truncate the previous day's table. You may even decide to drop the previous day's table.
    Because there exists a synonym same as your table name, everything else can remain unchanged.

    I hope you have understood the gist of this method. It might not be possible for you to implement it if your application restricts doing it this way.

    [Edited by svk on 11-27-2001 at 07:50 PM]
    svk

  8. #28
    Join Date
    Aug 2000
    Posts
    32

    Thumbs up

    Wonderful idea. I appreciate your help. I should follow your idea. Thank you very much for all your help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width