Thank you. These solutions seem to be very helpful. I will try them. Do you recommend creating the index after data load, or before? The index is vital because I need to be able to select arbitrary row ranges from tables, ie, gets 5000 to 6500. Experimenting with the console on large tables (about 3 gig, 6 million rows, because that's the size we're dealing with), using Select with Offset and Limit took longer the further "down" the table one went; using an index and SELECT... BETWEEN seemed to be mostly linear.
I know there's a speed hit on INSERT when you have an index, because you need to update the index; I also know indexing a large table takes a long time. I'm just wondering which hurts more. On Feb 17, 1:39 pm, Thomas Mueller <[email protected]> wrote: > Hi, > > CREATE TABLE AS SELECT is faster than first creating the table and > then inserting the data. I would try to combine creating the table > with inserting the data. Example: > > CREATE TABLE amp_20091124(ROW_ID IDENTITY,...) AS > SELECT NULL, * FROM CSVREAD... > > Another idea is: > > -- create an empty table > CREATE TABLE amp_20091124 AS SELECT * FROM > CSVREAD('C:\Temp\Mark Issue 11302009\amp_20091124.csv') > WHERE 1=0; > > -- add the ROW_ID > -- seehttp://www.h2database.com/html/grammar.html#alter_table_add > ALTER TABLE amp_20091124 ADD COLUMN ROW_ID IDENTITY ; > -- maybe BEFORE firstColumnName > > -- insert the data, with NULL as the ROW_ID > INSERT INTO amp_20091124 AS SELECT *, NULL FROM CSVREAD... > > Regards, > Thomas -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
