export indexes=n? - Page 2
DBAsupport.com Forums - Powered by vBulletin
Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 24

Thread: export indexes=n?

  1. #11
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Originally posted by Mr.Hanky
    You can drop them, sure. But frankly 1.7 is a small database so why bother? I don't think you will save a lot of time
    Did you? I thought you would save ZERO time

  2. #12
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    The trouble with export/import of indexes is that the creation on import is done in a serial fashion, which can be very sow and leave idle resources on the target daatbase.

    If you export including indexes, then import with indexfile=y, you can get the DDL for the indexes from the dump file and work out a way of submitting multiple index create statements at the same time
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #13
    Join Date
    Feb 2001
    Posts
    99
    So, am I to gather then from all of your comments that even dropping the indexes completely saves no time either? I understand that indexes are built at import time based on the ddl. However, I guess I need to dig more into the storage of indexes. I was of the understanding that when you create an index, that index is stored somewhere. Thus, that is data in and of itself that gets exported. True?

    For example, I have a auditing table that my app uses to track user actions. It has about 15million rows in it and 9 columns. 5 of those 9 are indexed. Thats a ton of index data that is being stored in a tablespace we have set aside just for storage of this table's indexes.

    Does that data get exported? Or is it ignored at export? That is what I am going to test next. I am going to drop those indexes on that table in my test box and do an export.

    Thanks for all the great feedback. The learning just never stops!!

  4. #14
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,999
    Just because something is physically stored in the database does not mean it will be stored within an export file in the same manner. The export file stores all of the ddl commands to create all of the objects in the database and the data stored in a binary form. The exception is that when you export as system you are not exporting things owned by sys. Which is probably a good thing. You are importing into a database that has a data dictionary.

    So if you really want to save time on the import. Do an import with indexes=n. and have all of the ddl you need for creating the indexes. If you could have a shell script or two that will submit more than one create index at a time then the creation of the indexes might go faster depending on your hardware and operating system as well as the init parameters of the target database. However, if you have good hardware and all of the relevant server os patches this should go quickly. If it doesn't you can always buy more ram.

  5. #15
    Join Date
    Jan 2001
    Posts
    3,131
    I guess at this point we should start talking about setting SORT_AREA_SIZE and the possibility of parallel index creates.

    MH
    I remember when this place was cool.

  6. #16
    Join Date
    Feb 2001
    Posts
    99
    Nope, my hardware is not an issue. Plenty of ram, cpu, etc to do the job. Import is not my real area of concern.

    My goal is to minimize the export time of the database. I don't want to be sitting around for 90 mins waiting for the export if I could have got it done in 45 by manipulating the export. Our vendor, who designed both the app and the database, has told me that he plans to drop a dozen of the largest indexes in the system before exporting. He claims this will reduce the export time of the database by a fair amount. I am attempting to prove/disprove this area.

    Now, I understand that within the export file the DDL is seperate from the data. This allows you the option of Rows=N so that you can just rebuild the objects themselves. But, with regards to the index itself, what I gather from your inputs is that even though that index is occupying physical space within my database, it is not exported. Only the index definintion (DDL) gets exported. Thus, on import, the index is created.

    Have I got it now? If all this is true, then there appears to be little that I can do to speed up the full export other than having all users log off so that Oracle has the machines full CPU and other resources to do the export.

  7. #17
    Join Date
    Jan 2001
    Posts
    3,131
    By George I think he's got it.

    I think the only parameter that will speed up your export is DIRECT, but you have to decide if you want to use it or not. I hope you bet the guy and win something nice.

    Ps. ROWS=N will speed it up too

    MH
    I remember when this place was cool.

  8. #18
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by DcsoBob
    Nope, my hardware is not an issue. Plenty of ram, cpu, etc to do the job. Import is not my real area of concern.

    My goal is to minimize the export time of the database. I don't want to be sitting around for 90 mins waiting for the export if I could have got it done in 45 by manipulating the export. Our vendor, who designed both the app and the database, has told me that he plans to drop a dozen of the largest indexes in the system before exporting. He claims this will reduce the export time of the database by a fair amount. I am attempting to prove/disprove this area.

    As you've already experienced, and people have already responed, dropping indexes before an export will not speed up the export process significantly. When Oracle exports your database, it exports the data along with the table, but not with the index. The only thing the .dmp file knows is how to rebuild the index. (Which, by the way, it sometimes does wrong.)

    Your consultant may be suggesting to drop the indexes before the export to help speed up the import. Maybe he is planning on rebuilding indexes in parallel once the data is already in the database.


    Now, I understand that within the export file the DDL is seperate from the data. This allows you the option of Rows=N so that you can just rebuild the objects themselves. But, with regards to the index itself, what I gather from your inputs is that even though that index is occupying physical space within my database, it is not exported. Only the index definintion (DDL) gets exported. Thus, on import, the index is created.

    By george, I think he's got it.


    Have I got it now? If all this is true, then there appears to be little that I can do to speed up the full export other than having all users log off so that Oracle has the machines full CPU and other resources to do the export.
    Have you played around with:
    1. BUFFER size (which there is a bug in big buffers)
    2. DIRECT=Y
    3. Placing your .dmp file on stripped disk
    4. Parallel exports by schema

    Personally, I think export/import for migration purposes is a waste of time. I think you should clone your database from the old box to the new box & use ODMA to migrate it. It will be 50 times faster and you won't have to worry about silly options like COMPRESS=N, CONSISTENT=Y, FOO=bar, etc.

    And you've stopped using export/import for backup, right?
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  9. #19
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by Mr.Hanky
    By George I think he's got it.
    Now I'm really scared. I'm thinking like Mr.H... (or is Mr.H thinking like me....)
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  10. #20
    Join Date
    Jan 2001
    Posts
    3,131
    Sorry Jeff, as the timestamp indicates I beat you by 2 minutes, you must be slowing down.

    Maybe it was my double expresso!!

    MH
    I remember when this place was cool.

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