Creating multiple indexes with different names on the same column does nothing.

[David Van Couvering <da...@vancouvering.com>]

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

Re: Creating multiple indexes with different names on the same column does nothing.

[Mark Thornton <mt...@optrak.co.uk>]

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.
>
One case where two indexes on the same column does make sense is if you 
are permitted to specify different collations for each index. Thus with 
a table of place names you could have different indexes for each 
language that you choose to support. OK, Derby doesn't support this (I 
do in a special database used for geographical information).

Mark Thornton

Re: Creating multiple indexes with different names on the same column does nothing.

[Rick Hillegas <Ri...@Sun.COM>]

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

Re: Creating multiple indexes with different names on the same column does nothing.

[Kathey Marsden <km...@sbcglobal.net>]

Knut Anders Hatlen wrote:
> David Van Couvering <da...@vancouvering.com> writes:
>
>   
>> I noticed that if you try to create two indexes with different names on the
>> same column set that Derby silently ignores the command.
>>
>>     
There is a related issue:
https://issues.apache.org/jira/browse/DERBY-3300

Kathey

Re: Creating multiple indexes with different names on the same column does nothing.

[Knut Anders Hatlen <Kn...@Sun.COM>]

David Van Couvering <da...@vancouvering.com> writes:

> 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.

Hi David,

We do raise a warning in these cases, so it's not quite silent, but I
don't think many applications actually check the warnings. IJ does,
though:

ij> create table t (x int);
0 rows inserted/updated/deleted
ij> create index i1 on t(x);
0 rows inserted/updated/deleted
ij> create index i2 on t(x);
0 rows inserted/updated/deleted
WARNING 01504: The new index is a duplicate of an existing index: I1.

-- 
Knut Anders