Hello Thomas, Hello Aleksey

Yes, I was the originator of idea1 and 2

>idea 1: spell out the alias of the intermediate file - benefits:
>idea 2: give some info on the key expression of the temporary index *and* the alias it is created on.
>idea 3: whenever vfp changes join order print out a line with the reason.

There's also a rather old wish on the wish list from Thomas (ID:742), which also had the idea 3 and the idea to have a measurement of the time each single step would take.

Another thing, that's irritating on the output of sys(3054) is, that you get "none" optimization messages for filter conditions on tables, that have no filter condition in the sql. This leads to the belief, that rushmore fails to fully optimize.

for example: Select * from tabParent P inner join tabChild C on c.id=p.id where c.somefield=someexpression
will result in a line "Rushmore optimization level for table tabParent: none", because there is no where clause regarding tabParent. Reads, as if the sql is not fully optimized, but if there is an index on id surely the join is optimized. I'd skip these irritationg messages, just show what's really done.

How about ASHOWPLAN(ArrayName,nOption[,cSQL]), a new array function creating a showlplan array, that can be analized programmatically. Each line of the array would then show one step of the sql execution, one filter or one join:

1: cAlias1 (1. alias involved)
2: cAlias2 (2. alias involved (if at all))
3: cClause (Where or Join/On clause)
4: cTag (tag of the index used or (virtual) tag name of a temp index created)
5: cKeyexpression (index expression, perhaps with ON cAlias1/2, that is on which alias, if temp index)
6: cOptimizationType (filter/join)
7: cOptimizationsuccess (none, partial, full)
8: cResultAlias (cursor alias of sql,table alias of sql or (virtual) intermediate result alias, could also be cAlias1 for where clauses)
9: nMilliseconds (time of this single step)
10: cReasonOrderChange (reason for order change, if order changed compared to the original sql/FORCE)
11: nSubquerylevel (level of the subquery executed, 1 for main select or parallel union selects)
12: nUnionlevel (number of the union select executed at the moment, 1...n)
13: cPartialSQL (an sql statement equivalent to the executed single step, if possible - I hope you are not superstitious)

Bye, Olaf.