CODEWITHSUNDEEP
sqlsqlite
BorisV
BorisV

Reputation: 699

SQLite: querying partitioned tables

I have 100 tables with >1M records in each. All tables differ by names but have identical structure:

 (valueX INT NOT NULL, valueY INT NOY NULL, valueZ INT NOT NULL).

All table names are listed in separate table NAMES(name TEXT NOT NULL) but due to SQLite limitations table name cannot be substituted in query as a variable.

I want to search in all tables rows where valueX > valueZ AND valueZ < valueY while skipping first N and retrieving next K rows.

I prefer not to waste disk space and/or memory, since I believe they may be not enough. That's effectively eliminates the UNION option I guess.

What can be done - considering the constraints?

PS. By "What can be done" I express my will to learn about proper strategy and to see a sample of relevant query.

PPS. Number of tables can change anytime.

Upvotes: 1

Views: 437

Answers (1)

CL.
CL.

Reputation: 180192

SQLite will do subquery flattening, if possible, so there is no disk/memory penalty for simply combining the tables with UNION ALL (preferrably in a view) and doing a query on that.

Upvotes: 2

Related Questions