These tables can be created in advance using the SQL below (formatted for Oracle in the example below) for 9.3.1; you should do some research to see if the same concepts apply in later versions of SDE. However, notice that I am creating the tables as Global Temp Tables for Oracle. Esri doesn't do a good job at clearing these tables out when users make large selection sets and then log out. Global Temp Tables are tables in Oracle that get cleared out automatically by the database when the user disconnects his or her session. We do this and it's worked very well to help reduce the size of the database overall. There may be a SQL Server equivalent but I'm not sure of the syntax.
CREATE GLOBAL TEMPORARY TABLE USER_NAME.SDE_LOGFILE_DATA
(
logfile_data_id NUMBER(*,0) NOT NULL ENABLE,
sde_row_id NUMBER(*,0) NOT NULL ENABLE
)
ON COMMIT PRESERVE ROWS
/
CREATE INDEX USER_NAME.SDE_LOGFILE_DATA_IDX1
ON USER_NAME.SDE_LOGFILE_DATA(LOGFILE_DATA_ID,SDE_ROW_ID)
/
CREATE INDEX USER_NAME.SDE_LOGFILE_DATA_IDX2
ON USER_NAME.SDE_LOGFILE_DATA(SDE_ROW_ID)
/
GRANT DELETE ON USER_NAME.SDE_LOGFILE_DATA TO SDE
/
CREATE GLOBAL TEMPORARY TABLE USER_NAME.SDE_LOGFILES
(
logfile_name VARCHAR2(256) NOT NULL ENABLE,
logfile_id NUMBER(*,0) NOT NULL ENABLE,
logfile_data_id NUMBER(*,0) NOT NULL ENABLE,
registration_id NUMBER(*,0) NOT NULL ENABLE,
flags NUMBER(*,0) NOT NULL ENABLE,
session_tag NUMBER(*,0) NOT NULL ENABLE,
logfile_data_db VARCHAR2(32),
logfile_data_owner VARCHAR2(32),
logfile_data_table VARCHAR2(98),
column_name NVARCHAR2(32)
)
ON COMMIT PRESERVE ROWS
/
CREATE UNIQUE INDEX USER_NAME.SDE_LOGFILES_PK
ON USER_NAME.SDE_LOGFILES(LOGFILE_ID)
/
CREATE UNIQUE INDEX USER_NAME.SDE_LOGFILS_UK
ON USER_NAME.SDE_LOGFILES(LOGFILE_NAME)
/
CREATE UNIQUE INDEX USER_NAME.SDE_LOGFILES_UK2
ON USER_NAME.SDE_LOGFILES(LOGFILE_DATA_ID)
/
GRANT DELETE ON USER_NAME.SDE_LOGFILES TO SDE
/
