SQLite Star Query

While investigating the nature of SQLite‘s query optimisation strategy I came across this 2004 presentation (link dead! try internet archive) by D. Richard Hipp which provides a excellent overview of SQLite’s technical architecture. The reason I’m looking at the optimiser is to figure out if SQLite can handle a star-schema query.

First impressions were not promising as SQLite doesn’t support merge-joins or bitmap-joins, only the classic loop-join is implemented e.g.

Select * from t1,t2;

…is evaluated as;

for each row in t1:
for each row in t2:
output one row of result

Also, as only one index per table is used in a query this appears to rule out the classic one-index-per-dimension scenario normally applied to fact tables. All is not lost however, by using the INTERSECT command it’s possible to simulate a bit mapped query e.g.

Select sum(sales) from fact_table ft
where ft.rowid in (
select rowid from fact_table ft where ft.customer_dim_id = "35"
select rowid from fact_table ft where ft.product_dim_id = "56787"
select rowid from fact_table ft where ft.time_dim_id = "20070823")

Not as slick as an Oracle 10G star transformation but ‘good enough’ for the task at hand. What, I hear you ask, is that task and why would anybody use SQLite to implement a data warehouse star schema? For the last few months I’ve been rabbiting on about micro-ETL, well, I’m about to embark on my micro-BI phase (you’ve been warned) and for a micro-BI environment a micro-datawarehouse is essential. More anon…

Part II – http://blog.gobansaor.com/2007/08/31/sqlite-star-query-part-ii/

Part III – http://blog.gobansaor.com/2009/09/29/tag-cubes-sqlite-star-query-part-iii/


7 responses to “SQLite Star Query

  1. Pingback: SQLite Star Query Part II « Gobán Saor

  2. Timothy Fitz

    Unfortunately your link to the 2004 presentation is now dead, and a few quick googles didn’t turn anything up. Do you have a copy of it or can you find one?

  3. Pingback: TAG Cubes – SQLite Star Query Part III « Gobán Saor

  4. Pingback: Star Schemas – to boldly go where no Excel spreadsheet has gone before « Gobán Saor