Niels, let me give you my schema and a few lines of data:

# mar 23 test db stuff
create table table_ combined(
type_flag tinyint,
ins_key mediumint,
trade_price double,
trade_size int,
trade_time int,
bid_price double,
bid_size int,
bid_time int,
ask_price double,
ask_size int,
ask_time int,
total_volume int,
avg_bid_ask_spread double,
nullcol varchar(5)
);


Example data ("select * from table_combined;"):
type_flag,ins_key,trade_price,trade_size,trade_time,bid_price,bid_size,bid_time,ask_price,ask_size,ask_time,total_volume,avg_bid_ask_spread,nullcol
1,151631,1258,22000,1199113221,1258,8,1199113221,1260,24,1199113221,0,1,
1,160133,1395,71100,1199113221,1390,116,1199113221,1395,113,1199113221,0,1,
1,154315,186000,130,1199113238,186000,737,1199113238,186100,151,1199113238,0,1,


Thanks.


On Wed, Mar 25, 2009 at 12:37 AM, Stefan Manegold <Stefan.Manegold@cwi.nl> wrote:
On Tue, Mar 24, 2009 at 11:38:59AM +0100, Milena.Ivanova@cwi.nl wrote:
> > Hi, then I'm totally lost.
> > That was my last resort in creating a big table.
> >
> > So:
> > (1) I can't create a 600-million table using COPY (because it hangs)
> > (2) I can't create two 300-million tables and combined them into one (bug)
> >
> > Have I missed other ways of doing this?
> >
> > Just wanted to make sure, the MonetDB can support >600-million-by-10
> > records
> > in a SINGLE table?
>
> Hi,
> In SkyServer DR6 database of 2.5TB MonetDB supports tables longer than
> 600M rows, although with smaller number of columns.
> For instance:
> sql>select count(*) from photoprofile;
> +-------------+
> | count_      |
> +=============+
> | 13282921742 |
> +-------------+
> 1 tuple
> sql>select * from photoprofile limit 1;
> +-----+------+---------------+---------------+--------------------+
> | bin | band | profmean      | proferr       | objid              |
> +=====+======+===============+===============+====================+
> |   0 |    0 | 1.49587098e-0 | 6.95472624e-1 | 587722951693303809 |
> :     :      : 8             : 0             :                    :
> +-----+------+---------------+---------------+--------------------+
>
> where 2 columns are tinyint, 2 - real and 1 -bigint. The table was loaded
> by 'copy .. into ..' statement.
> Milena

In this line, I also recall reports that the TPC-H dataset at scale factor
100 (i.e., ~100GB .cvs) has been successfully loaded into MonetDB/SQL (both
Nov2008-SP2 & Feb2009) on a 64-bit 8 GB RAM Linux desktop.
Among others, the SF-100 TPC-H dataset contains a 600037902 record
"lineitem" table with the following schema:

CREATE TABLE lineitem (
       l_orderkey INT NOT NULL,
       l_partkey INT NOT NULL,
       l_suppkey INT NOT NULL,
       l_linenumber INT NOT NULL,
       l_quantity FLOAT NOT NULL,
       l_extendedprice FLOAT NOT NULL,
       l_discount FLOAT NOT NULL,
       l_tax FLOAT NOT NULL,
       l_returnflag VARCHAR(1) NOT NULL,
       l_linestatus VARCHAR(1) NOT NULL,
       l_shipdate DATE NOT NULL,
       l_commitdate DATE NOT NULL,
       l_receiptdate DATE NOT NULL,
       l_shipinstruct VARCHAR(25) NOT NULL,
       l_shipmode VARCHAR(10) NOT NULL,
       l_comment VARCHAR(44) NOT NULL
);

AFAIK, the table has been loaded using "COPY 600037902 RECORDS INTO ... FROM
FILE ..." from a single 75 GB .csv file.

Stefan

> > Thanks.
> >
> > On Mon, Mar 23, 2009 at 9:14 AM, Niels Nes <Niels.Nes@cwi.nl> wrote:
> >
> >> On Mon, Mar 23, 2009 at 09:03:09AM -0700, Yue Sheng wrote:
> >> >    Tried the whole process. Still the same problem (table_combined
> >> >    has the same scheme as table_1):
> >> >
> >> >    insert into table_combined select * from table_1;
> >> >    insert into table_combined select * from table_2;
> >> >    select count(*)  from table_combined;
> >> >    this gives ONLY the number of records in table_1, but
> >> >    table_combined as roughly same size as the other two combined.
> >> >    NONE OF THE TABLE HAS ANY INDEXING
> >> >    Could someone please confirm if this is a bug? Both table_1 and
> >>
> >> The way you explain it, it seems a bug.
> >>
> >>
> >> Niels
> >> >    table_2 has over 200million rows of data.
> >> >
> >> >    On Sat, Mar 21, 2009 at 4:47 PM, Yue Sheng
> >> >    <[1]yuesheng8@gmail.com> wrote:
> >> >
> >> >      (1) There's no index or key or anything on the table.
> >> >
> >> >    (2) The combined table is the size of table_1 + table_2. But
> >> >    only table_1 shows up in query
> >> >    On Sat, Mar 21, 2009 at 1:51 PM, Lefteris <[2]lsidir@gmail.com>
> >> >    wrote:
> >> >
> >> >      Hi Yue,
> >> >      is there any chance that you have set uniqueness in the
> >> >      table specs
> >> >      and thus not inserting duplicate tuples from table_1 and
> >> >      table_2? If
> >> >      not, then I would guess that this is a bug and it needs
> >> >      further
> >> >      investigation.
> >> >      lefteris
> >> >
> >> >    On Sat, Mar 21, 2009 at 3:12 AM, Yue Sheng
> >> >    <[3]yuesheng8@gmail.com> wrote:
> >> >    > two tables: table_1 and table_2 both have exactly the same
> >> >    schema. table_1
> >> >    > has 2.15MM rows of data, table_2 has 3.35MM row of data. No
> >> >    indexing
> >> >    > created a new table: table_combined using same schema as
> >> >    table_1
> >> >    > insert into table_combined select * from table_1;
> >> >    > (30 minutes later)
> >> >    > insert into table_combined select * from table_2;
> >> >    >
> >> >    > (40 minutes later)
> >> >    > select count(*) * 1 from table_combined;
> >> >    > this give ONLY 2.15MM rows with same rows as table_1
> >> >    > What happened to table_2?!?
> >> >    > Yes, I've check dfarm size. It equals 2*(table_1 + table_2 )
> >> >    > So it's there but "just not seeing it"
> >> >    >
> >> >    >
> >> >    >
> >> >    >
> >> >    >
> >> >
> >> >      >
> >> >      ------------------------------------------------------------
> >> >      ------------------
> >> >      > Apps built with the Adobe(R) Flex(R) framework and Flex
> >> >      Builder(TM) are
> >> >      > powering Web 2.0 with engaging, cross-platform
> >> >      capabilities. Quickly and
> >> >      > easily build your RIAs with Flex Builder, the
> >> >      Eclipse(TM)based development
> >> >      > software that enables intelligent coding and step-through
> >> >      debugging.
> >> >      > Download the free 60 day trial.
> >> >      [4]http://p.sf.net/sfu/www-adobe-com
> >> >      > _______________________________________________
> >> >      > MonetDB-users mailing list
> >> >      > [5]MonetDB-users@lists.sourceforge.net
> >> >      >
> >> >      [6]https://lists.sourceforge.net/lists/listinfo/monetdb-user
> >> >      s
> >> >      >
> >> >      >
> >> >      ------------------------------------------------------------
> >> >      ------------------
> >> >      Apps built with the Adobe(R) Flex(R) framework and Flex
> >> >      Builder(TM) are
> >> >      powering Web 2.0 with engaging, cross-platform capabilities.
> >> >      Quickly and
> >> >      easily build your RIAs with Flex Builder, the
> >> >      Eclipse(TM)based development
> >> >      software that enables intelligent coding and step-through
> >> >      debugging.
> >> >      Download the free 60 day trial.
> >> >      [7]http://p.sf.net/sfu/www-adobe-com
> >> >      _______________________________________________
> >> >      MonetDB-users mailing list
> >> >      [8]MonetDB-users@lists.sourceforge.net
> >> >      [9]https://lists.sourceforge.net/lists/listinfo/monetdb-user
> >> >      s
> >> >
> >> > References
> >> >
> >> >    1. mailto:yuesheng8@gmail.com
> >> >    2. mailto:lsidir@gmail.com
> >> >    3. mailto:yuesheng8@gmail.com
> >> >    4. http://p.sf.net/sfu/www-adobe-com
> >> >    5. mailto:MonetDB-users@lists.sourceforge.net
> >> >    6. https://lists.sourceforge.net/lists/listinfo/monetdb-users
> >> >    7. http://p.sf.net/sfu/www-adobe-com
> >> >    8. mailto:MonetDB-users@lists.sourceforge.net
> >> >    9. https://lists.sourceforge.net/lists/listinfo/monetdb-users
> >>
> >> >
> >> ------------------------------------------------------------------------------
> >> > Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM)
> >> are
> >> > powering Web 2.0 with engaging, cross-platform capabilities. Quickly
> >> and
> >> > easily build your RIAs with Flex Builder, the Eclipse(TM)based
> >> development
> >> > software that enables intelligent coding and step-through debugging.
> >> > Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com
> >> > _______________________________________________
> >> > MonetDB-users mailing list
> >> > MonetDB-users@lists.sourceforge.net
> >> > https://lists.sourceforge.net/lists/listinfo/monetdb-users
> >>
> >>
> >> --
> >> Niels Nes, Centre for Mathematics and Computer Science (CWI)
> >> Science Park 123, 1098 XG Amsterdam, The Netherlands
> >> room C0.02,  phone ++31 20 592-4098, fax ++31 20 592-4312
> >> url: http://www.cwi.nl/~niels   e-mail: Niels.Nes@cwi.nl
> >>
> >>
> >> ------------------------------------------------------------------------------
> >> Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are
> >> powering Web 2.0 with engaging, cross-platform capabilities. Quickly and
> >> easily build your RIAs with Flex Builder, the Eclipse(TM)based
> >> development
> >> software that enables intelligent coding and step-through debugging.
> >> Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com
> >> _______________________________________________
> >> MonetDB-users mailing list
> >> MonetDB-users@lists.sourceforge.net
> >> https://lists.sourceforge.net/lists/listinfo/monetdb-users
> >>
> > ------------------------------------------------------------------------------
> > Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are
> > powering Web 2.0 with engaging, cross-platform capabilities. Quickly and
> > easily build your RIAs with Flex Builder, the Eclipse(TM)based development
> > software that enables intelligent coding and step-through debugging.
> > Download the free 60 day trial.
> > http://p.sf.net/sfu/www-adobe-com_______________________________________________
> > MonetDB-users mailing list
> > MonetDB-users@lists.sourceforge.net
> > https://lists.sourceforge.net/lists/listinfo/monetdb-users
> >
>
>
> ------------------------------------------------------------------------------
> Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are
> powering Web 2.0 with engaging, cross-platform capabilities. Quickly and
> easily build your RIAs with Flex Builder, the Eclipse(TM)based development
> software that enables intelligent coding and step-through debugging.
> Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com
> _______________________________________________
> MonetDB-users mailing list
> MonetDB-users@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/monetdb-users
>

--
| Dr. Stefan Manegold | mailto:Stefan.Manegold@cwi.nl |
| CWI,  P.O.Box 94079 | http://www.cwi.nl/~manegold/  |
| 1090 GB Amsterdam   | Tel.: +31 (20) 592-4212       |
| The Netherlands     | Fax : +31 (20) 592-4312       |

------------------------------------------------------------------------------
Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are
powering Web 2.0 with engaging, cross-platform capabilities. Quickly and
easily build your RIAs with Flex Builder, the Eclipse(TM)based development
software that enables intelligent coding and step-through debugging.
Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com
_______________________________________________
MonetDB-users mailing list
MonetDB-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/monetdb-users