What you've described here is column partitioning - most databases implement row partitioning, where the rows in the table are split between multiple, hidden sub-tables based on the value(s) in one or more columns within the row. The most common application of which is separating date-based data - transparently keeping data for each month in its own table, for example.
And in my ever so humble opinion, this would be a great thing for SQLite to have - anything that allows the database to be more selective about what rows it reads to satisfy a particular query is a good thing. Also, for the record, beware of the word partitioning - it means different things to different databases. For Oracle and SQL Server 2005 partitioning means table partition, while for DB2 it means database partitioning (a la Teradata). -Tom > -----Original Message----- > From: Zibetti Paolo [mailto:[EMAIL PROTECTED] > Sent: Tuesday, March 21, 2006 9:22 AM > To: 'sqlite-users@sqlite.org' > Subject: [sqlite] R: [sqlite] support for table partitioning? > > > The database already knows exactly where to look for each > table when all > the tables are in the same file. > > All it has to do is "lseek()" to the appropriate spot. How > does moving > tables into separate files help this or make it any faster? > > > > "Table partitioning" is a technique used to improve > performance of large > databases running on large machines. > With table partitioning you can configure the DB so that it > stores some > fields of a record in a file and the remaining fields of the > same record in > a different file. > If each record is large, placing the two files on two different disks > usually speeds things up because reading (or writing) a > record requires to > read, in parallel, half the record from one disk and the > other half from the > other disk. > Performance also increases if your select happens to access > only the fields > stored in one of the two files: if the select requires a > sequential scan of > the entire (large) table, the DB manager will have to read > through only half > the mount of data. > In my opinion however table partitioning is beyond the scope > of a DB like > SQLite... > > Bye > > > > > -----Messaggio originale----- > Da: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Inviato: martedì 21 marzo 2006 14.48 > A: sqlite-users@sqlite.org > Oggetto: Re: [sqlite] support for table partitioning? > > "Miha Vrhovnik" <[EMAIL PROTECTED]> wrote: > > On 3/21/2006, "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote: > > > > >"Miha Vrhovnik" <[EMAIL PROTECTED]> wrote: > > >> Hi drh and others, > > >> > > >> Regarding the issues they appear on ML with very large tables and > knowing > > >> that sqlite now enforces constraint checks on tables, is > there any > > >> chances of suporting table partitoning? > > >> > > > > > >Put each table in a separate database file then ATTACH > > >the databases. > > > > > That's not the same. You still need to rewrite queries, > where in real > > table partitioning you don't need to do that. > > What changes to the queries do you think are necessary? > > > The select's and inserts > > are faster because db knows where to put/search for them. > > > > The database already knows exactly where to look for each > table when all the tables are in the same file. All it > has to do is "lseek()" to the appropriate spot. How does > moving tables into separate files help this or make it any > faster? > > -- > D. Richard Hipp <[EMAIL PROTECTED]> >
