>Hi,
> How to have indexes to speed up the query which only used for report generating? There are 7 keys may be used to generate the same report.
>
>Any ideas? Using INDEX ON and delete the CDX after use?
Take a look at the part of the Index...on...
tag [tagname] of [filename here] syntax. By explicitly setting a filename different from yourtable.cdx, you are creating a non-structural cdx which can be safely deleted once it's closed.
Well, I'm not sure this non-structural index wouldn't get registered in the .dbc, so that's one thing to check.
IMO, a better strategy would be to do a partial query on the existing tags, and then index the resulting cursor on whatever you need (using the above syntax). So,
Select * from yourtable where [use conditions based on tags you have];
into cursor temp
lcCdxFile=forcepath(sys(3), lcTempDir)
index on field1 tag field1 of (lcCdxFile)
...
index on fieldN tag fieldN of (lcCdxFile)
Select * from temp where...
etc
Still, I'm not too sure this would fool Rushmore - you may still be better off letting it create the needed indexes automatically, and maybe cutting your query into a couple of simpler ones. We had a thread here where it turned out that a good strategy would be to run a simpler query on the big table first, and then do any joins on the resulting cursor.