Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2004

    Unanswered: Huge Database, queries refrmed, indexes made, cant purge data, whats next?

    Hi ppl,
    a new person to this site, I am Rajesh Motwani, ppl call me MOTS, i have a database containging huge data of across 7 lakh records in more than 10 transactional table, we cannot purge them in different schemas, i am not an administrator, but i tried many approaches of forming a query, adding indexes, since data is huge, cant add more indexes, nor am an dba, would like to know if u guys have such scenerio, and 10 transactional tables are equi-joined what time it takes for you if u have to show results to the browser, what is ur approach
    Thanks In Advance,

  2. #2
    Join Date
    Sep 2003
    Virginia, USA

    If you want to delete all data from a table, you have three options.
    1. the DELETE command.
    2. the TRUNCATE command.
    3. the DROP TABLE command.

    Delete allows you to be selective, and delete some or all rows. The downside is it causes Oracle to write a lot of data to the redo log and undo (rollback) segments. If there are indexes, then the redo and undo must be updated for each deleted index record as well, making it slow and very space-consuming. With many rows, the delete may fail with insufficient space to write the undo data. Some people don't like to use Delete to delete all rows, because Oracle will not move the table's high water mark as it does when you truncate a table.

    Truncate is much faster and consumes much less space. However, there are two issues. First, it is not selective - it will delete all rows from the table. Second, it will not work if there are foreign key constraints, or for a few other reasons. I therefore keep a script of the commands to drop constraints, truncate the tables, and then recreate all of my constraints.

    Drop Table is like truncate, but it also removes information about the table from the Oracle Data Dictionary. You would need to be more careful using Drop Table than using either Delete or Truncate.
    Author, Oracle Database 10g: From Nuts to Soup

  3. #3
    Join Date
    Apr 2002
    California, USA
    When deleting many rows in a table, your best option is to create a temporary table _TMP with CTAS with the good records only. Then rename the original table to _OLD and the _TMP to th eoriginal table name.

    This will result in triggers, views, procedures, functions reffering to the original table to be invalidated, but you can prepare the compilation scripts before. I did that recently on 450 million rows table and the compilation of 73 triggers and 15 stored procedures, took 2 minutes.


    clio_usa - OCP 8/8i/9i DBA

  4. #4
    Join Date
    Jul 2003
    another option:
    you can export with a where clause. drop all tables with cascade constraints, then
    import your filtered data.

    I find this very handy when dealing with large amounts of data spread over many tables. This probably would not be too efficient if you didn't have a common column among all your tables.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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