Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 10 of 19 total
Thread Best way to merge several tables into one?
Fri, Sep 28 2007 11:29 AMPermanent Link

Dave Harrison
Is there a way to use SQL to take 10 tables with identical structure and
view them as one table? In MySQL there is something called a Merge table
which logically joins the tables together (vertically-like an append) so
it appears they are all one table without physically copying any data.
It would be great if DBISAM or ElevateDb could achieve something like that.

Failing that, is there a way in DB v4 to merge the data into one table
using SQL? I'd like to do it in one or two operations if possible.

TIA

Dave
Fri, Sep 28 2007 11:53 AMPermanent Link

Eryk Bottomley
Dave,

> Is there a way to use SQL to take 10 tables with identical structure and
> view them as one table? In MySQL there is something called a Merge table
> which logically joins the tables together (vertically-like an append) so
> it appears they are all one table without physically copying any data.
> It would be great if DBISAM or ElevateDb could achieve something like that.
>
> Failing that, is there a way in DB v4 to merge the data into one table
> using SQL? I'd like to do it in one or two operations if possible.

SELECT {whatever} UNION [ALL] ...etc.    ...see help file for examples.

Eryk
Fri, Sep 28 2007 1:48 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Dave,

<< Is there a way to use SQL to take 10 tables with identical structure and
view them as one table? In MySQL there is something called a Merge table
which logically joins the tables together (vertically-like an append) so it
appears they are all one table without physically copying any data. It would
be great if DBISAM or ElevateDb could achieve something like that. >>

You can use UNION [ALL] to do so, but it will physically copy the rows from
each table into one big result set.

--
Tim Young
Elevate Software
www.elevatesoft.com

Mon, Oct 1 2007 12:52 PMPermanent Link

Dave Harrison
Tim Young [Elevate Software] wrote:
> Dave,
>
> << Is there a way to use SQL to take 10 tables with identical structure and
> view them as one table? In MySQL there is something called a Merge table
> which logically joins the tables together (vertically-like an append) so it
> appears they are all one table without physically copying any data. It would
> be great if DBISAM or ElevateDb could achieve something like that. >>
>
> You can use UNION [ALL] to do so, but it will physically copy the rows from
> each table into one big result set.
>

This seems to work well if I extract just a small number of records from
each table. But if I try and merge all tables into one using dbSys, then
I get "DBISAM Engine Error # 10258 Cannot lock record in the table
'table_dest'". (Destination table)

This is the same error I get when using dbSys to copy rows from a large
table to a new table using "Insert into table2 select * from table1". It
will get half way through after about 90 minutes and this error pops up.

So it looks like it is impossible to move a lot of rows at a time with
DBSys. (If it takes more than 90-120 minutes).

Dave
DBISAM 4.25 build 5
Mon, Oct 1 2007 3:58 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Dave,

<< This seems to work well if I extract just a small number of records from
each table. But if I try and merge all tables into one using dbSys, then I
get "DBISAM Engine Error # 10258 Cannot lock record in the table
'table_dest'". (Destination table)

This is the same error I get when using dbSys to copy rows from a large
table to a new table using "Insert into table2 select * from table1". It
will get half way through after about 90 minutes and this error pops up.

So it looks like it is impossible to move a lot of rows at a time with
DBSys. (If it takes more than 90-120 minutes). >>

How many rows are being copied ?  Is the total size of the target table
going to exceed 4GB ?  If so, then you need to turn on large file support in
the engine/DBSYS.

--
Tim Young
Elevate Software
www.elevatesoft.com

Mon, Oct 1 2007 4:24 PMPermanent Link

Dave Harrison
Tim Young [Elevate Software] wrote:
> Dave,
>
> << This seems to work well if I extract just a small number of records from
> each table. But if I try and merge all tables into one using dbSys, then I
> get "DBISAM Engine Error # 10258 Cannot lock record in the table
> 'table_dest'". (Destination table)
>
>  This is the same error I get when using dbSys to copy rows from a large
> table to a new table using "Insert into table2 select * from table1". It
> will get half way through after about 90 minutes and this error pops up.
>
>  So it looks like it is impossible to move a lot of rows at a time with
> DBSys. (If it takes more than 90-120 minutes). >>
>
> How many rows are being copied ?  Is the total size of the target table
> going to exceed 4GB ?  If so, then you need to turn on large file support in
> the engine/DBSYS.
>

The data file is 1.3 gb, the index is 856 MB. This error happens every
time I try and use DbSys to copy a large table. The problem might be it
is trying to throw all the rows (15 million) into 1 transaction and
eventually blows up.

Dave
Mon, Oct 1 2007 10:32 PMPermanent Link

"Donat Hebert \(Worldsoft\)"
That is certainly large.  As a rule of thumb, you should consider a commit
interval
for large inserts. ie Interval of 500 to 1000 records to take smaller bites
than gulps Smile



> The data file is 1.3 gb, the index is 856 MB. This error happens every
> time I try and use DbSys to copy a large table. The problem might be it is
> trying to throw all the rows (15 million) into 1 transaction and
> eventually blows up.
>
> Dave

Tue, Oct 2 2007 5:39 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Dave,

<< The data file is 1.3 gb, the index is 856 MB. This error happens every
time I try and use DbSys to copy a large table. The problem might be it is
trying to throw all the rows (15 million) into 1 transaction and eventually
blows up. >>

Ah, yes, that's the issue.   The OS might have an issue with that many row
locks (15 million).  You'll probably want to break up the INSERT into
smaller transactional chunks in order to keep the number of row locks from
escalating beyond a few thousand.

--
Tim Young
Elevate Software
www.elevatesoft.com

Wed, Oct 3 2007 12:16 AMPermanent Link

Dave Harrison
Tim Young [Elevate Software] wrote:
> Dave,
>
> << The data file is 1.3 gb, the index is 856 MB. This error happens every
> time I try and use DbSys to copy a large table. The problem might be it is
> trying to throw all the rows (15 million) into 1 transaction and eventually
> blows up. >>
>
> Ah, yes, that's the issue.   The OS might have an issue with that many row
> locks (15 million).  You'll probably want to break up the INSERT into
> smaller transactional chunks in order to keep the number of row locks from
> escalating beyond a few thousand.
>
Tim,
   Why is the OS doing row locks? I thought DBISAM would have locked
the table until the query completed so there would be only 1 lock. How
does Oracle or MS SQL handle it under Windows? Splitting the query into
chunks within a loop is a bit of a pain for a one-time query. Has
ElevateDb solved this problem? TIA

Dave
Wed, Oct 3 2007 3:05 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Dave


Isn't it just a matter of setting the COMMIT interval?

Roy Lambert
Page 1 of 2Next Page »
Jump to Page:  1 2
Image