> And then I put all of those in one giant (almost: only about 15 columns) view > that the user can do arbitrary queries on, most of which only touch one or > two of those columns from C, and many of which touch zero.
SQLite does not do query re-write. If you use a database engine that *does* do full query re-write you will find that the result is exactly what you want, but only if you enable the most expensive optimizations -- and query re-write is a *very* expensive and complicated operation when done correctly and exhaustively. Many database engines "claim" to do query re-write, but most of them do not do a very good job of it because it is complicated and expensive. Also, the "meaning" of query-rewrite is different for different engines. In the case of Oracle (for example) it means to eliminate common elements into materialized views and then re-write the query against those views. In DB/2, it means to take the entire query and generate a graph from it. Then recursively substitute into the graph any externalities (cte's, views, etc), then apply relational algebra to the graph and compute the most efficient, minimal, yet semantically equivalent graph (which might not be able to be expressed as SQL) and then generate the execution plan from that graph. This is a completely separate step from SQL Parsing and plan generation. It is not concerned with "how" to execute the query, but rather with optimizing the semantics of the query. Very few commercial SQL engines will do a good job in optimizing queries such as you describe. Determining if the query re-write optimizations are any good is easy: no matter how you phrase your query you should always get the same result with the same execution time. If re-phrasing the query into a semantic equivalent results in a different plan, then the re-writer does not work. QGM (IBM's query re-write) will pass this test with flying colours if you allow it to do exhaustive optimization. Query rewrite only needs schema information, it will not benefit from statistics or other information which is used to generate or optimize the execution plan. A key sign that a database engine does not do query re-write in a meaningful fashion is that it supports various methods of hinting how to solve (execute) the query. SQL is supposed to be declarative and therefore independent of the skill (or lack thereof) of the query writer. --- () ascii ribbon campaign against html e-mail /\ www.asciiribbon.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
