A customer is using 10.1C on Windows. They have an application from a vendor that will be moving to v11 sometime in the next year. The 10.1c database uses Type II storage areas but still mingles data and indexes in areas.
I would like to re-organize the database and separate the data and indexes. I am looking for best practice in this regard since I do not want to invalidate the expected upgrade to 11 and the presumed delta DF that will get loaded.
How have others managed this when tuning a DB from an active PSC partner?
Thanks, Bob
When dealing with a vendor supplied system it is best to leave their storage areas in place. For the most part you can create new areas and move stuff into the new areas mostly without causing problems.
Incremental df files are probably not going to have AREA info in them unless the vendor is creating a new table, index or LOB. In those cases they are /probably/ going to try to create it in an area that they expect to exist. Which is why you keep their old areas hanging around (I like to make them variable length so that i don't waste disk space on empty areas...). But you cannot assume that -- sometimes they forget and dump new stuff in the schema area. So your best bet is to grep the df for AREA upfront and tweak it before loading. Or you can scan the areas after the fact and look for things that ended up where you don't want them.
Although not the fastest and it will take up lots of bi space, index move does allows managing your indexes in a replication environment. Can you further explain what you mean by not wanting to invalidate the upgrade due to a delta df?
If you reorg prior to the conversion there will be no delta df due to this.
Index rebuild is another approach if not running in a replication environment. It too is slow in 10.1c (the performance enhancements went into 102b) but faster than index move without the affect on the bi.
How big is the database? It might be best to just dump and load as opposed to re-orging it in place.
When dealing with a vendor supplied system it is best to leave their storage areas in place. For the most part you can create new areas and move stuff into the new areas mostly without causing problems.
Incremental df files are probably not going to have AREA info in them unless the vendor is creating a new table, index or LOB. In those cases they are /probably/ going to try to create it in an area that they expect to exist. Which is why you keep their old areas hanging around (I like to make them variable length so that i don't waste disk space on empty areas...). But you cannot assume that -- sometimes they forget and dump new stuff in the schema area. So your best bet is to grep the df for AREA upfront and tweak it before loading. Or you can scan the areas after the fact and look for things that ended up where you don't want them.
Thanks Rich and Tom. The vendor seems to use major product releases to modify the scheme. The install kit that gets delivered applies incremental DFs to add and change tables and indexes. Those reference an area as Tom has surmised. Whatever I do needs to not break the ability of the customer to run the vendor upgrade when the time comes.
I expect that after the upgrade, I will need to TABLEMOVE data and indexes into my new areas.
I am just staring to test this. Since it is on a stand alone machine I can D&L or TABLEMOVE things to compare run times. for both the DBA setup activity as well as normal user execution. I expect to try and benchmark some standard heavy hitting processes to determine a rough idea regarding increased performance. Looking to justify the effort for a few customers I know in a similar circumstance.
I have 2 sample databases. Once is about 10 gig and the other around 35 gig.
Hopefully, with some useful feedback, I can work with the vendor on implementing a more modern database structure. From what I have seen out in the wild, there are great opportunities laying around for this sort of improvement. There are many vendors that still use areas by application function (AP,AR GL...) but have indexes and data mixed.
Bob
From a performance perspective having tables and indexes share areas probably makes little or no difference. The main reason to split them is to simplify maintenance.
Tablemove (and index move) is really, really painfully slow on any non-trivial table. And it uses enormous amounts of bi space. Dump & load is not that hard and is orders of magnitude faster.