On Tue, 21 Mar 2006, Miha Vrhovnik wrote:
>>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?
>
>What I had in my mind is this:
>
>CREATE TABLE measurement (
> city_id int not null,
> logdate date not null,
> peaktemp int,
> unitsales int
>);
>
>CREATE TABLE measurement_yy04mm02 (
> CHECK ( logdate >= DATE '2004-02-01' AND logdate < DATE '2004-03-01' )
>) INHERITS (measurement);
>CREATE TABLE measurement_yy04mm03 (
> CHECK ( logdate >= DATE '2004-03-01' AND logdate < DATE '2004-04-01' )
>) INHERITS (measurement);
>
>--next 2 are inserted into measurement_yy04mm02
>INSERTI INTO measurement VALUES(1, '2004-02-15', 1, 1);
>INSERTI INTO measurement VALUES(1, '2004-02-15', 1, 2);
>
>--next 2 are inserted into measurement_yy04mm03
>INSERTI INTO measurement VALUES(1, '2004-03-15', 1, 1);
>INSERTI INTO measurement VALUES(1, '2004-03-15', 1, 2);
>
>SELECT * FROM measurement WHERE logdate = '2004-02-15'; returns rows from
>measurement_yy04mm02
>and
>SELECT * FROM measurement; returns rows from both/all tables.
>
>all queries are should now be a lot fasters. Don't you agree?
I wouldn't agree. Not only do we now have to traverse whatever index/table
contains the result, we also have to evaluate any condition just to
determine which table to traverse.
Where table partitioning may be a win on other DBMS is where you have
different tables in different table spaces, which are usually configured
on different devices, and allows access to multiple rows in the 'virtual'
table to be accessed in parallel from the multiple devices. It also allows
the table to be larger than any single tablespace.
>
>Regards,
>Miha
>
Christian
--
/"\
\ / ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
X - AGAINST MS ATTACHMENTS
/ \
