You could loop through the indexes in the source table and build an INDEX ON command string for each index. We have similar code that looks something like this:
FOR ln_Loop = 1 TO TAGCOUNT()
lc_IndexExpression = "INDEX ON " + SYS( 14, ln_Loop ) + " TAG " + ;
TAG(ln_Loop)
IF !EMPTY( SYS( 2021, ln_Loop ) )
lc_IndexExpression = lc_IndexExpression + " FOR " + SYS( 2021, ln_Loop )
ENDIF
IF DESCENDING( ln_Loop )
lc_IndexExpression = lc_IndexExpression + " DESCENDING"
ENDIF
IF UNIQUE( ln_Loop )
lc_IndexExpression = lc_IndexExpression + " UNIQUE"
ENDIF
IF CANDIDATE( ln_Loop )
lc_IndexExpression = lc_IndexExpression + " CANDIDATE"
ENDIF
ENDFOR
Note that this doesn't work for Primary Key indexes that are created with ALTER TABLE <> ADD PRIMARY KEY ...
>I'm doing this:
>
>
> SELECT a_sotran
> COPY STRUCTURE TO (lcTempFile) WITH CDX
> USE IN a_sotran
> USE (lcTempFile) ALIAS a_sotran IN 0
> SELECT a_sotran
> APPEND FROM (gf_sotranf)
> APPEND FROM (gf_soytrnf)
>
>
>Basically, I'm making a temp table with the name of the current table that holds the current and history files to fool SBT into accessing them both. The trouble is that the appends are too slow if the indexes are there. They're plenty fast if I drop the "WITH CDX", but I do need the indexes available.
>
>What I'm wondering is if there is some way to attach the indexes after the append, and if that would be faster? I know I can do INDEX ON, but I'd rather not hard code the indexes in case they change in a future version.
>
>Any other suggestions on speeding this up significantly? Those history tables can get really huge, so speed is important.
>
>Thanks,
>
>Michelle