Hi David,
Just to be precise, Derby won't create two indexes which have the same
meaning to the optimizer. You can declare multiple indexes on the same
columns provided that the indexes are different in terms of sort order
and uniqueness--and provided that you create the unique indexes last!
Here's a script which successfully declares 16 indexes on a table with 2
columns. If you change this script so that the block of unique indexes
comes first, then only the unique indexes will be created; the
non-unique indexes will raise warnings.
create table t( a int, b int );
-- all succeed
create index t_a_asc__b_asc on t( a asc, b asc);
create index t_a_asc__b_desc on t( a asc, b desc);
create index t_a_desc__b_asc on t( a desc, b asc);
create index t_a_desc__b_desc on t( a desc, b desc);
create index t_b_asc__a_asc on t( b asc, a asc);
create index t_b_asc__a_desc on t( b asc, a desc);
create index t_b_desc__a_asc on t( b desc, a asc);
create index t_b_desc__a_desc on t( b desc, a desc);
create unique index t_a_asc__b_asc_unique on t( a asc, b asc);
create unique index t_a_asc__b_desc_unique on t( a asc, b desc);
create unique index t_a_desc__b_asc_unique on t( a desc, b asc);
create unique index t_a_desc__b_desc_unique on t( a desc, b desc);
create unique index t_b_asc__a_asc_unique on t( b asc, a asc);
create unique index t_b_asc__a_desc_unique on t( b asc, a desc);
create unique index t_b_desc__a_asc_unique on t( b desc, a asc);
create unique index t_b_desc__a_desc_unique on t( b desc, a desc);
-- raises a warning
create index t_a_asc__b_asc_2 on t( a asc, b asc);
select c.conglomeratename
from sys.sysconglomerates c, sys.systables t
where c.tableid = t.tableid
and t.tablename = 'T'
;
Hope this helps,
-Rick
David Van Couvering wrote:
I noticed that if you try to create two indexes with different names
on the same column set that Derby silently ignores the command.
I can understand the rationale - it doesn't make sense to maintain two
keys against the same columns - they're the same key. But it would be
useful to say something that lets the user know that the index already
exists, rather than silently succeeding.
Just saying...
David
FWIW, MySQL let's you create two indexes with different names on the
same columns...
--
David W. Van Couvering
http://davidvancouvering.blogspot.com
