[h2] Re: int not 100% continuous
thank you very much :-) On Sunday, 18 December 2022 at 19:47:59 UTC+8 Evgenij Ryazanov wrote: > Hi! > > If you don't care about exact ordering, the fastest way is > UPDATE tableName SET columnName = ROWNUM(); > > If you want to preserve it, a slower command is needed: > MERGE INTO tableName USING > (SELECT columnName, ROW_NUMBER() OVER(ORDER BY columnName) FROM tableName) > T(columnName, R) > ON tableName.columnName = T.columnName > WHEN MATCHED THEN UPDATE SET columnName = T.R; > This command also requires a lot of memory (because H2 cannot buffer > window functions on disk). > > If you have an ascending index on tableName(columnName), you can try to > use more efficient version without window functions: > MERGE INTO tableName USING > (SELECT columnName, ROWNUM() FROM tableName ORDER BY columnName) > T(columnName, R) > ON tableName.columnName = T.columnName > WHEN MATCHED THEN UPDATE SET columnName = T.R; > Without a compatible index ROWNUM() can number rows in order different > from specified in ORDER BY clause. > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/4f19eb9d-f938-4aa0-9742-81f8f3bb2754n%40googlegroups.com.
[h2] Re: int not 100% continuous
Hi! If you don't care about exact ordering, the fastest way is UPDATE tableName SET columnName = ROWNUM(); If you want to preserve it, a slower command is needed: MERGE INTO tableName USING (SELECT columnName, ROW_NUMBER() OVER(ORDER BY columnName) FROM tableName) T(columnName, R) ON tableName.columnName = T.columnName WHEN MATCHED THEN UPDATE SET columnName = T.R; This command also requires a lot of memory (because H2 cannot buffer window functions on disk). If you have an ascending index on tableName(columnName), you can try to use more efficient version without window functions: MERGE INTO tableName USING (SELECT columnName, ROWNUM() FROM tableName ORDER BY columnName) T(columnName, R) ON tableName.columnName = T.columnName WHEN MATCHED THEN UPDATE SET columnName = T.R; Without a compatible index ROWNUM() can number rows in order different from specified in ORDER BY clause. -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/d1add6b0-1d5e-4194-b3fd-2f4ff96a90b0n%40googlegroups.com.
Re: [h2] index doesn't speed up
From https://www.h2database.com/html/performance.html: This database uses indexes to improve the performance ofSELECT, UPDATE, DELETE. If a column is used in the WHERE clause of a query, and if an index exists on this column, then the index can be used. Multi-column indexes are used if all or the first columns of the index are used.Both equality lookup and range scans are supported.Indexes are used to order result sets, but only if the condition uses the same index or no index at all.The results are sorted in memory if required. Indexes are created automatically for primary key and unique constraints. Indexes are also created for foreign key constraints, if required. For other columns, indexes need to be created manually using the CREATE INDEX statement. -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/d7ea70cbb05ec219754b0d1353b2847c26e1e1f6.camel%40manticore-projects.com.
Re: [h2] index doesn't speed up
Greetings! As far as I remember, H2 considers composite indices only in certain situations -- but not for all possible optimisations. Please EXPLAIN your query to check, if the index has been considered (I guess, it has not). Maybe try again with 2 different indices, one for MEM and one for SEQUENCE (replacing you composite index). Good luck Andreas On Sun, 2022-12-18 at 02:17 -0800, mche...@gmail.com wrote: > hi > i have 20 millions rows , and this index doesn't speed up the > query, please help: > > create index mem on data(mem, sequence); > > select * from data where mem is not null order by sequence > > mem is varchar(200); and sequence is bigint > > thanks > -- > You received this message because you are subscribed to the Google > Groups "H2 Database" group. > To unsubscribe from this group and stop receiving emails from it, > send an email to h2-database+unsubscr...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/h2-database/d23b4ca9-2f9b-4bbe-84dd-b7d8469e129dn%40googlegroups.com > . -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/cd57a7444f5009c7883fad2d61ce5fb9ad39df57.camel%40manticore-projects.com.
[h2] index doesn't speed up
hi i have 20 millions rows , and this index doesn't speed up the query, please help: create index mem on data(mem, sequence); select * from data where mem is not null order by sequence mem is varchar(200); and sequence is bigint thanks -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/d23b4ca9-2f9b-4bbe-84dd-b7d8469e129dn%40googlegroups.com.
[h2] int not 100% continuous
hi i have column which is bigint and not int not 100% continuous , so the data is [1,2,36,10,11] , now i want to reinput the data ane make it continuouse, such as [1,2,3,4,5,6]. I got 20 millions rows, what is the fastest way to do this? thanks Peter -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/cddcb737-0e2a-4592-9763-dd5372019792n%40googlegroups.com.
