Hi, > why do you need H2 to not allow two indexes on the same column(s)
That two indexes are on same columns is inefficient. you wasted disk space, even when the extra cost to update the table. Besides, if you been created once, hard to notice at a later time. However, it may be sufficient check of index name only. If you do not think it is useful, please rejected that patch. > If I understand correctly, for you Derby doesn't allow two indexes on the > same column? If yes what is the test case? sorry. I have been tested with ij (derby's client tool). ij> create table test(id int, id2 int); 0 rows inserted/updated/deleted ij> create index CASE2_1 on test(id asc); 0 rows inserted/updated/deleted ij> create index CASE2_2 on test(id asc); 0 rows inserted/updated/deleted WARNING 01504: The new index is a duplicate of an existing index: CASE2_1. When using derby from jdbc, derby does not throws Exception. (when using oracle from jdbc, oracle still throws Exception) litailang 2012/1/4 Thomas Mueller <[email protected]>: > Hi, > > What is your use case (why do you need H2 to not allow two indexes on the > same column(s))? I think the implementation complexity to detect this case > is relatively large, and I'm not sure if it's really worth it. > > All databases I tested (H2, MySQL, HSQLDB, Derby, PostgreSQL, SQLite) didn't > fail to create two indexes on the same column. I didn't test Oracle and IBM > DB2. My test case is: > > drop table test; > create table test(name varchar(255)); > create index idx_n1 on test(name); > create index idx_n2 on test(name); > > If I understand correctly, for you Derby doesn't allow two indexes on the > same column? If yes what is the test case? > > Probably a tool to detect unneeded indexes would be nice. But this would > also include indexes that not completely the same, for example: > > non-unique index on test(name) > unique index on test(name, firstName) > > The first index isn't required usually, as the second index indexes the > column "name" as well. However depending on the use case it might make sense > to keep both indexes, because index lookup in the first index is slightly > faster. > > Regards, > Thomas > > > > On Thursday, December 8, 2011, litailang wrote: >> >> Hi. >> >> I wrote a patch for improving 'create Index' compatibility. >> >> DB2, Oracle and Derby checks that columns are already indexed when >> execute create Index statement. >> >> Oracle checks that columns already indexed and indexing ascending or >> decending. >> DB2 checks only that columns already indexed because DB2 has 'allow >> reverse scans' option. >> >> PostgreSQL, MySQL and H2 checks index name already exists only. >> >> >> -- >> ----------- >> litailang >> >> -- >> 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. >> > -- > 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. -- ----------- Ayataro Kogo -- 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.
