On Sep 22, 2008, at 10:53 AM, Jeffrey Becker wrote: > I think the policy other dbms > systems have of making these things hints rather than requirements is > a good one because it still allows the query optimizer to make the > best choice when the hints its given become incorrect.
If you want the query optimizer to make the choice, omit the INDEX BY clause all together. If you include an INDEX BY clause, that is saying that you the programmer know better than the optimizer and the optimizer is not to second-guess you. In the two high-profile use cases, the programmers already have the statement using the "correct" index without an INDEX BY clause. They just want to be alerted if some future schema change alters the index choice, perhaps by deleting one of the indexes that were being used. If the INDEX BY clause becomes a hint, then this function of the clause is removed. And without the impetus of those two high-profile use cases, the functionality will not be added at all. So, I am offering this choice: (1) The ability to select and index with an error if that index won't work and (2) no new capabilities at all. > I'd prefer to > see some sort of programmatic method of doing this. The method I'd > find ideal would be to have some sort of sqlite_suggest_* api which > would allow a user to apply hints to an already prepared statement. The indices are already chosen by the time the sqlite3_stmt is constructed. It is too late to offer hints after the fact. > I > forget if sqlite_stmt keeps a copy of the sql so I may well be > suggesting the impossible here. The api would reinforce the > non-standard nature of the action while keeping the sql dialect free > of non-standard sql. D. Richard Hipp [EMAIL PROTECTED] _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users