db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <Richard.Hille...@Sun.COM>
Subject Re: Creating multiple indexes with different names on the same column does nothing.
Date Mon, 03 Nov 2008 14:11:34 GMT
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


Mime
View raw message