Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2003
    Posts
    1

    Post Unanswered: refreash data to table "fastest way"?

    I would like some help about what is the fastest way too refresh data in a table? I'm moving the data from one instance to another and refreshing a table. Would I create a table with a different name, load data into it, create the indexes and rename the table to the production table. sample:

    production table name "employees"
    new production table name "employees_temp"

    1. drop table employees
    2. rename employees_temp to employees
    3. rename indexes for employees table.

    Is this the right way to go ?

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1

    Re: refreash data to table "fastest way"?

    Originally posted by lcoe
    I would like some help about what is the fastest way too refresh data in a table? I'm moving the data from one instance to another and refreshing a table. Would I create a table with a different name, load data into it, create the indexes and rename the table to the production table. sample:

    production table name "employees"
    new production table name "employees_temp"

    1. drop table employees
    2. rename employees_temp to employees
    3. rename indexes for employees table.

    Is this the right way to go ?
    The indexes will become INVALID when the table gets renamed.

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    how about:

    1. drop table employees cascade constraints
    2. import tables=employees rows=y constraints=Y indexes=Y
    3. analyze table

    OR

    1. disable constraints
    2. truncate table
    3. import table
    4. enable constraints
    5. rebuild indexes
    6. analyze table
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  4. #4
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    hi,

    if u have a db link, try:

    drop table
    create table as select from table@db_link
    re-create constraints
    re-create indexes.

    Hope this helps.
    Edwin van Hattem
    OCP DBA / System analyst

  5. #5
    Join Date
    Oct 2003
    Location
    Colorado
    Posts
    4
    There are many options...the biggest question is how long you can have the table unavailable while you are doing the refresh.

    If you can make the source data read only, then a transportable tablespace will be the fastest way to actually move the data and indexes. It does require the two database servers to be the same OS (restriction to be removed in 10G).

    Another traditional approach is to keep two tables EMP1 and EMP2 and you load one while users are accessing the other. Use a public synonym to point to one or the other...a few problems with this approach in SQL statements having to be reparsed afterwards, etc.

    If you have the partitioning option then another approach is to use ALTER TABLE EXCHANGE PARTITION. You may create a partitioned table with only one partition and just exchange it and your load table whenever you have done a new load.

Posting Permissions

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