export indexes=n?

Okay, will be migrating my current 734 system to 9i2 here in a couple weeks. We are about finished with our testing and are working on the migration plan. We have done the migration a couple times in the test environment and are looking at ways to speed the export up.

Current argument is to drop the major indexes and then do the export. REbuild the indexes on the new machine. I thought we could just do the export without the indexes. Either way should speed the export up considerably.

My current export is a shade over 3gigs and takes about 1h40m on my system with all users still on it.

As a test, I went to my development box to time out the process. I did a full export that was 1.7gig and took 28mins. I then did the same export adding the indexes=N option.

Both exports took the same time and are of the same size. Why? I thought the indexes=N option should have cut down on export time and dump file size.

Anyone offer any insights?

Thanks

I think that the export with indexes=y just saves the ddl required to create the index. To save the actual data in the index would be redundant. But your plan might make the import faster in any event.

Gandolf is correct, only the DDL is exported for the indexes. Either way you will be building an index so why not let import do it. Import will load the table first than build the indexes, it will also implicitly do an analyze. So I say let import do it.
You may want to specify ...
BUFFER=? (I use 4000000)
COMMIT=Y

in your parfile, this is usefull for large tables since Oracle will now commit at a set interval rather than trying to swallow the whole table.

MH

Okay, I can see how the ddl is save. But to continue the thought, what about if the indexes are dropped? That is my next test. Would that reduce my import time and file size? That index data is stored somewhere.

I am going to turn the statistics off as well. Once we do the migration, we are going to be modifying quite a few of the objects and creating new objects. We plan to do a schema analyze as a last step so stats will be taken care off.

Thanks for the info so far...

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, just make sure you do a full backup so that you have the DDL. You may want to take a full export with

FULL=Y
ROWS=N

This will export all the structure (indexes included) and leave the data behind. It takes about 1 minute to run and is a lot easier to read from than a full .DMP file, just an extra precaution IN ADDITION TO YOUR FULL EXPORT!

How long does the full import take?
Are you using constraints (R.I.) on this box?
It should not be that bad even with indexes and analyzes.

MH

index data are not exported, they are created based on existing data

1.7 gigs is my dev box. My production export is taking 1hour and 45 mins and is a shade over 3Gigs. Now, I know the time issue is because I am exporting a read-consistent version of the database while users are still on. So, on migration day, the export will speed up once I kick all users off.

Full Import is taking about 45mins or so. I am not worried about that. I can't really shave time there. But, if I can cut that export time in half, let say 45mins vice 100, then thats good. I am a 24x7 shop and I am trying to minimize the down time.

I will be doing an export both full and rows=n off my test box before we wipe it and bring in the production data. I am currently doing up a version of my instance on my desktop so I can use TOAD to do a schema comparison after we are done. If I can get my second server up and going by end of month, will be a comparison version on it.

Sorry, not sure what constraints(R.I.) is so can;t tell ya.

Quote:

---

Originally posted by pando
index data are not exported, they are created based on existing data

---

I'm not sure what you mean, the index DDL is indeed exported.

MH

well I said data didnt I, coz the peep seems to insist by dropping the index he can speed up the export which is NOT true at all because index data are NOT exported, when you import you dont import index data, you create the indexes

We already covered that one Pando, you are late to our party.

:D

Quote:

---

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

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

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!!:D

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.

I guess at this point we should start talking about setting SORT_AREA_SIZE and the possibility of parallel index creates.

MH

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.

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

Quote:

---

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.

Quote:

---

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.

Quote:

---

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?

Quote:

---

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....) ;)

Sorry Jeff, as the timestamp indicates I beat you by 2 minutes, you must be slowing down.

Maybe it was my double expresso!!

MH

Quote:

---

Originally posted by DcsoBob
Have I got it now?

---

Yes, it seems you've got it, at least about the export of the indexes. BTW, if you think a bit about what is stored inside indexes, you'll easily understand that physicaly exporting the actual contents of the indexes would be totaly ludicrous. Indexes (at least B*tree, which are the most common ones) contain key values and the ROWIDs of the corresponding table rows. So what would be the point to import those ROWIDS into the new database when literaly each and every row in the imported table has new ROWID, totaly different from the one it had in the old table? If those indexes would indeed be physicaly exported, they would have been in UNUSABLE state after the import. So the only option with indexes after the import is to be created from scratch. And for this nothing else than index definition DDL command has to be exported.

If you realy want to save time with export (although I would be much more concentrated on the time spend during the import, as some other have already suggested), then try to get the optimal setting of BUFFER parameter. Or even better, check if the DIRECT PATH export is possible in your situation - using this option together with proper RECORDLENGTH parameter will definitely spead up the export process more than anything else.

LOL:D :D Marist, I didn't think anyone would remember that thread. The answer is --pretty much. I still do the exports, but not near as many. And with the migration, I will be using RMAN to setup my backup and recover scheme. I will also be implementing Data Guard once I have OEM/OMS and RMAN up and running.

Our vendor is responsible under the contract for doing the migration with me sitting in his backpocket. He and I have already done this migration once for our testing environment. The only reason the vendor is doing the migraiton is because of a redesign to the schema, addition of new tables, alteration of current tables, data conversion, etc. A straight up migration I would have handled myself.

But, I will use the cloning method to build my backup box. But I will use the new box to supply the clone. And probably use RMAN to clone it. Just lots of options to play with. :)

I have pondered the DIRECT=Y method and have tested export speeds. It cuts the export down to 15mins or less. However, my current workload and priorities have not allowed me testing and play time for the import. Cant scarp and rebuild my dev box when we are using it for dev and testing against the current app. Not enough boxes in our shop...sigh.

Jmodic, if I had stopped typing long enough to actually think about the indexes a little more, I would have realized what the index really was and why importing them is useless.

Thanks for all help and input. I love this forum.

Bob

sniff....sniff....

I just love a happy ending.

ROFL...Your killing me....:D :D :D