Changing dbtune keyword for vector and / or raster data

[MohammadIshfaq]

Dear all,
I was wondering if there is a way for changing dbtune keyword for already created data in ArcSDE. I know storage migration is one way to migrate data from one storage type and keyword to another type and keyword but what if we have same storage type but we want to change just the keyword to cleanup existing data for better performance.

I have ArcSDE 9.3.1 with Oracle RDBMS. Please mention even if it is possible with other versions.

Thanks.

[VinceAngelo]

You can't alter the keyword associated with a raster, and though you can alter a layer's
keyword, it will not change its storage. The layer config keyword stored in LAYERS,
TABLE_REGISTRY, and RASTER_COLUMNS just represents the storage rules at the time
the layer was created.

If you want to reorganize data for better performance using DBTUNE, you'll need to export
the data, change the existing keywords, drop the existing tables, and reload them. The only
exception for this is some of the indexes, those which can be rebuilt by placing a layer in
load-only I/O mode and back into normal I/O, or those manually created by 'sdetable -o
create_index', which can be deleted and rebuilt.

If I have a minor typo in my DBTUNE environment, I'll often edit the keyword to be correct
then REBUILD the indexes maually at the SQL prompt, but if table storage is wrong, I have
to delete and re-load.

- V

[MohammadIshfaq]

Thanks Vangelo for the reply. Your suggestion is correct but not workable for me as I have prolonged versions which I can not reconcile and post because of some business specific needs. However, it answers to some extent that I should accept the solution as it is right now and wait for the time when it is feasible for me to fine tune it.

[VinceAngelo]

Note that I wrote "if you want to reorganize ... using DBTUNE ..."  There is nothing stopping you
from using database tools to reorganize individual poor-performing tables.  Oracle doesn't
support clustered indexes the way other RDBMSes do, so you'd need to look at shutting down
your ArcSDE server, and exporting and reimporting tables with alternate storage (and then
restarting ArcSDE). 

I've frequently done this sort of reorganization when "upgrading" an older server to a new one,
by using 'exp rows=N', then 'imp indexfile=path' and editing the DDL to change the TABLESPACE
name to one with UNIFORM extents more in line with actual utilization.  Of course, there are many
ways to accomplish the same task, including use of CREATE TABLE foo TABLESPACE bar AS SELECT ...
(but be sure to create all the same indexes with the same names and transfer all the triggers, etc).
Using these techniques is of course unsupported by Esri, but there is little risk if you have a full (and
validated) backup before you start and recreate the table exactly.

- V

[MohammadIshfaq]

Thanks Vangelo, it answers my question.