Hello, I plan to use sqlite to implement an 'aggregation tool'. Basically, the design would be the following:
1. I implement a virtual table CFL(contract, amount) which may provide up to 1 billion unsorted rows. 2. The aggregation phasis will be defined at run time and may consists in something as simple as 'select contract, sum(amount) from cfl group by contract'. 3. Since there will be only ~1000 different contracts, I expect SQLite to aggregate 'on the fly' the rows from CFL. 4. Unfortunately, as shown by the explain plan, it looks that SQLite first select all rows from CFL. When all rows are retrieved (which implies something like a 8Go temporary file), the rows are sorted and aggregated. [0, 0, 0, "SCAN TABLE CFL VIRTUAL TABLE INDEX 0: (~0 rows)"] [0, 0, 0, "USE TEMP B-TREE FOR GROUP BY"] Is there a way to force rows to be dispatched (using the group by) and aggregated on the fly instead of being stored, sorted and then aggregated? Note that I can't change the production order of the rows in the virtual table. Thanks for your help, Regards, Lionel ----------------------------------------- Moody's monitors email communications through its networks for regulatory compliance purposes and to protect its clients, employees and business and where allowed to do so by applicable law. Parties communicating with Moody's consent to such monitoring by their use of the email communication. The information contained in this e-mail message, and any attachment thereto, is confidential and may not be disclosed without our express permission. If you are not the intended recipient or an employee or agent responsible for delivering this message to the intended recipient, you are hereby notified that you have received this message in error and that any review, dissemination, distribution or copying of this message, or any attachment thereto, in whole or in part, is strictly prohibited. If you have received this message in error, please immediately notify us by telephone, fax or e-mail and delete the message and all of its attachments. Thank you. Every effort is made to keep our network fr ee from viruses. You should, however, review this e-mail message, as well as any attachment thereto, for viruses. We take no responsibility and have no liability for any computer virus which may be transferred via this e-mail message. ----------------------------------------- _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
