How to Move the WMX table JTX_JOB_ATTACHMENTS

359
0
06-07-2022 11:48 AM
MarceloMarques
Esri Regular Contributor
2 0 359

WMX attachments table "JTX_JOB_ATTACHMENTS" can grow very large.
The best practice is to move the table to its own filegroup / tablespace / datafile
to reduce fragmentation and contention, and to improve performance.

PostgreSQL

--create tablespaces

cd D:/postgresqldata/wmx
D:
mkdir wmx_att
mkdir wmx_att_index

DROP TABLESPACE wmx_att;
CREATE TABLESPACE wmx_att OWNER postgres LOCATION 'D:/postgresqldata/wmx/wmx_att';
DROP TABLESPACE wmx_att_index;
CREATE TABLESPACE wmx_att_index OWNER postgres LOCATION 'D:/postgresqldata/wmx/wmx_att_index';

GRANT CREATE ON TABLESPACE wmx_att TO wmx;
GRANT CREATE ON TABLESPACE wmx_att_index TO wmx;

-- move table wmx.jtx_job_attachments

select schemaname, tableowner, tablename, tablespace
from pg_tables
where tableowner = 'wmx'
order by schemaname, tableowner, tablename, tablespace;

select 'alter table '|| tableowner||'.'||tablename||' set tablespace wmx_att;' as SQLTXT
from pg_tables
where tableowner = 'wmx'
order by schemaname, tableowner, tablename, tablespace;

/*
Example:
alter table wmx.jtx_job_attachments set tablespace wmx_att;
*/

--move indexes

select schemaname,tablename, indexname, tablespace
from pg_indexes
where schemaname = 'wmx'
order by schemaname, tablename, indexname, tablespace;

select 'alter index '|| schemaname||'.'||indexname||' set tablespace wmx_att_index;' as SQLTXT
from pg_indexes
where schemaname = 'wmx'
order by schemaname, tablename, indexname, tablespace;

/*
Example:
alter index wmx.r311_sde_rowid_uk set tablespace wmx_att_index;
*/

Oracle

--add tablespaces

DROP TABLESPACE GIS_ATT INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE GIS_ATT_INDEX INCLUDING CONTENTS AND DATAFILES;

--smallfile

CREATE SMALLFILE TABLESPACE "GIS_ATT"
DATAFILE '+DATA' SIZE 10M AUTOEXTEND ON NEXT 1M MAXSIZE 400M
LOGGING EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K
SEGMENT SPACE MANAGEMENT AUTO
DEFAULT COMPRESS FOR OLTP STORAGE ( ENCRYPT ) ENCRYPTION USING 'AES256';

CREATE SMALLFILE TABLESPACE "GIS_ATT_INDEX"
DATAFILE '+DATA' SIZE 10M AUTOEXTEND ON NEXT 1M MAXSIZE 400M
LOGGING EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K
SEGMENT SPACE MANAGEMENT AUTO
DEFAULT COMPRESS FOR OLTP STORAGE ( ENCRYPT ) ENCRYPTION USING 'AES256';

--bigfile

CREATE BIGFILE TABLESPACE "GIS_ATT"
DATAFILE '+DATA' SIZE 10M AUTOEXTEND ON NEXT 1M MAXSIZE 400M
LOGGING EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K
SEGMENT SPACE MANAGEMENT AUTO
DEFAULT COMPRESS FOR OLTP STORAGE ( ENCRYPT ) ENCRYPTION USING 'AES256';

CREATE BIGFILE TABLESPACE "GIS_ATT_INDEX"
DATAFILE '+DATA' SIZE 10M AUTOEXTEND ON NEXT 1M MAXSIZE 400M
LOGGING EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K
SEGMENT SPACE MANAGEMENT AUTO
DEFAULT COMPRESS FOR OLTP STORAGE ( ENCRYPT ) ENCRYPTION USING 'AES256';

--maxsize, resize 

select * FROM V$DATAFILE;
select * from dba_data_files;

note: resize the datafiles to avoid fragmentation

/*
SELECT 'ALTER DATABASE DATAFILE ' || CHR(39) || file_name || CHR(39) || ' AUTOEXTEND ON MAXSIZE 2G;' AS SQLTXT
FROM DBA_DATA_FILES
WHERE TABLESPACE_NAME LIKE 'GIS_ATT%'
UNION ALL
SELECT 'ALTER DATABASE DATAFILE ' || CHR(39) || file_name || CHR(39) || ' RESIZE 2G;' AS SQLTXT
FROM DBA_DATA_FILES
WHERE TABLESPACE_NAME LIKE 'GIS_ATT%'
ORDER BY SQLTXT;
*/

--Example:

/*
ALTER DATABASE DATAFILE '+DATA/MCS1/ACF1/DATAFILE/gis_att.2464.1048514833' AUTOEXTEND ON MAXSIZE 10G;
ALTER DATABASE DATAFILE '+DATA/MCS1/ACF1/DATAFILE/gis_att.2464.1048514833' RESIZE 10G;
ALTER DATABASE DATAFILE '+DATA/MCS1/ACF1/DATAFILE/gis_att_index.2465.1048514833' AUTOEXTEND ON MAXSIZE 2G;
ALTER DATABASE DATAFILE '+DATA/MCS1/ACF1/DATAFILE/gis_att_index.2465.1048514833' RESIZE 2G;
*/

--quota

ALTER USER "WMX" QUOTA UNLIMITED ON "GIS_ATT";
ALTER USER "WMX" QUOTA UNLIMITED ON "GIS_ATT_INDEX";

--move table

SELECT 'ALTER TABLE WMX.' || TABLE_NAME || ' MOVE TABLESPACE GIS_ATT ONLINE PARALLEL 8;' AS SQLMOVE 
FROM USER_TABLES
WHERE TABLE_NAME = 'JTX_JOB_ATTACHMENTS';

--Example:

/*
ALTER TABLE WMX.JTX_JOB_ATTACHMENTS MOVE TABLESPACE GIS_ATT ONLINE PARALLEL 8;
*/

--move index

SELECT 'ALTER INDEX ' || TABLE_OWNER || '.' || INDEX_NAME || ' REBUILD TABLESPACE GIS_ATT_INDEX ONLINE PARALLEL 8;' AS SQLMOVE 
FROM USER_INDEXES
WHERE TABLE_NAME = 'JTX_JOB_ATTACHMENTS'
ORDER BY INDEX_NAME;

--Example:

/*
ALTER INDEX WMX.R73153_SDE_ROWID_UK REBUILD TABLESPACE GIS_ATT_INDEX ONLINE PARALLEL 8;
ALTER INDEX WMX.SYS_IL0002043364C00005$$ REBUILD TABLESPACE GIS_ATT_INDEX ONLINE PARALLEL 8; --SKIP cannot rebuild - INDEX_TYPE=LOB
*/
SELECT TABLE_OWNER, TABLE_NAME, INDEX_NAME, INDEX_TYPE, TABLESPACE_NAME, STATUS 
FROM USER_INDEXES WHERE INDEX_NAME = 'SYS_IL0002043364C00005$$';

/*
TABLE_OWNER, TABLE_NAME, INDEX_NAME, INDEX_TYPE, TABLESPACE_NAME, STATUS
WMX JTX_JOB_ATTACHMENTS SYS_IL0002043364C00005$$ LOB GIS_BDATA VALID
*/

SQL Server

--add new filegroups and datafiles

USE [wmx]
GO
ALTER DATABASE [wmx] ADD FILEGROUP [WMX_ATT]
GO
ALTER DATABASE [wmx] ADD FILE(NAME = N'wmx_att_01',
FILENAME = N'D:\mssqldata_mcs\wmx\wmx_att_01.NDF',
SIZE = 1, MAXSIZE = 800, FILEGROWTH = 1)
TO FILEGROUP [WMX_ATT]
GO
ALTER DATABASE [wmx] ADD FILEGROUP [WMX_ATT_INDEX]
GO
ALTER DATABASE [wmx] ADD FILE(NAME = N'wmx_att_index_01',
FILENAME = N'D:\mssqldata_mcs\wmx\wmx_att_index_01.NDF',
SIZE = 1, MAXSIZE = 800, FILEGROWTH = 1)
TO FILEGROUP [WMX_ATT_INDEX]
GO

--By setting the datafiles initial size to 1MB, there is no delay in the creation of the FileGroups.
--You can then resize the datafiles to avoid fragmentation.

--set max size

USE [master]
GO
ALTER DATABASE [wmx] MODIFY FILE ( NAME = N'wmx_att_01', MAXSIZE = 10GB )
GO
ALTER DATABASE [wmx] MODIFY FILE ( NAME = N'wmx_att_index_01', MAXSIZE = 2GB )
GO

--resize datafiles

USE [master]
GO
ALTER DATABASE [wmx] MODIFY FILE ( NAME = N'wmx_att_01', SIZE = 10GB )
GO
ALTER DATABASE [wmx] MODIFY FILE ( NAME = N'wmx_att_index_01', SIZE = 2GB )
GO

--Verify storage

EXEC sp_helpdb [wmx]
GO


-- move Table JTX_JOB_ATTACHMENTS

--1. Generate Script for the NonClustered Index

/*
SSMS > Databases > wmx > Tables > wmx.JTX_JOB_ATTACHMENTS > Indexes >
"select the Non-Clustered Index" > Right Click >
"Script Index as" > "Create To ..." > "New Query Editor Window"
*/

--2. make the [WMX_ATT] FILEGROUP the DEFAULT filegroup

USE [wmx]
GO
ALTER DATABASE [wmx] MODIFY FILEGROUP [WMX_ATT] DEFAULT
GO

--3. this SELECT INTO will create the new table in the [WMX_ATT] FILEGROUP

USE [wmx]
GO
DROP TABLE [wmx].[JTX_JOB_ATTACHMENTS_new]
GO
SELECT * INTO [wmx].[JTX_JOB_ATTACHMENTS_NEW] from [wmx].[JTX_JOB_ATTACHMENTS]
GO
/*
--Check if new table was created on the filegroup WMX_ATT
SSMS > Databases > wmx > Tables > select "wmx.JTX_JOB_ATTACHMENTS_NEW" >
Right Click > Properties > Storage
*/

--4. make the original filegroup DEFAULT again

ALTER DATABASE [wmx] MODIFY FILEGROUP [PRIMARY] DEFAULT
GO

--5. rename table

EXEC sp_rename 'wmx.JTX_JOB_ATTACHMENTS', 'JTX_JOB_ATTACHMENTS_OLD'
GO
EXEC sp_rename 'wmx.JTX_JOB_ATTACHMENTS_NEW', 'JTX_JOB_ATTACHMENTS'
GO

--6. use the generated index statement from step 1 to create the same index, with same name, on the new table
-- change the filegroup for index to "ON WMX_ATT_INDEX"

CREATE UNIQUE NONCLUSTERED INDEX R19_SDE_ROWID_UK 
ON wmx.JTX_JOB_ATTACHMENTS ([OBJECTID] ASC)
WITH (FILLFACTOR = 75, PAD_INDEX = OFF,
ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, IGNORE_DUP_KEY = OFF,
STATISTICS_NORECOMPUTE = OFF, DATA_COMPRESSION = NONE,
DROP_EXISTING = OFF) ON WMX_ATT_INDEX;
GO
--NOTE: SET "DROP_EXISTING = OFF" OR IT WILL FAIL TO CREATE THE INDEX IN THE NEW TABLE

--7. drop the old table

DROP TABLE [wmx].[JTX_JOB_ATTACHMENTS_OLD]
GO

 

About the Author
| Marcelo Marques | Principal Product Engineer | Esri | Cloud & Database Administrator | OCP - Oracle Certified Professional | 30 years experience | www.linkedin.com/in/mmarquesbr | I have worked with Esri Technology since 1992 and I have been working with Enterprise Geodatabases since 1997 when the Geodatabase was first released. | " a successful Enterprise ArcGIS application deployment starts with a strong physical database design and best database administration practices" | " I do not fear computers. I fear the lack of them." - Isaac Asimov |