Re: Syntax to create cluster index when create table?
I don't know about other RDBMSs, but with Informix, creating a table with a
clustered index won't accomplish anything.
When an index on a table containing data is clustered, the data rows are
re-ordered to match the order of the index keys. However, when new rows are
inserted into the table, they are added at the end of the tablespace. Assuming
that rows are inserted (or updated) in random order (not in key sequence), the
table/index (by definition) is no longer clustered when the first row is
inserted after the clustering process. In other words, the table/index doesn't
"stay clustered." If a table/index is to be clustered at all times, then the
clustering process must be repeated after every insert/update session using
the "alter index ... to not cluster; alter index ... to cluster" syntax.
HTH,
Milton J. Vidrine, Jr.
Ruiming Chen wrote:
> In Sybase I do
>
> create table factory
> (
> factory_code char(6),
> factory_name varchar(30,1),
> constraint factory_pri primary key clustered (factory_code)
> );>
> This by default will have unique not null cluster index for factory_code
> as
> primary key.
>
> I want to do the same in Informix 7.x by
>
> create table factory
> (
> factory_code char(6) not null,
> factory_name varchar(30,1),
> primary key (factory_code) constraint factory_pri
> );
> create cluster index on factory (factory_code);>
> But this won't work. Because by default the above Informix SQL,
> the primary key created a null non cluster index and assigned a
> constraint name like 129_99.
> I found out this by get in the dbaccess. In my case I have to do
>
> alter index 129_99 to cluster;>
> But that is not what I want! I want to create a not null cluster index
> for the
> primary key when creating table like Sybase does.
> What is the syntax? Thank you!
>
> --Raymond
> --
> RC Square Team.
>
> ------------------------------------------------------------------------
>
> Chui /Chen, Raymond/Ruiming <rctwo@erols.com>
> CS
> RC Square
>
> Chui /Chen, Raymond/Ruiming
> CS <rctwo@erols.com>
> RC Square HTML Mail
> U.S.A. Fax: 301-498-8959
> Home: URL http://www.erols.com/rctwo/
> Work: 301-498-8959
> Netscape Conference Address
> Netscape Conference DLS Server
> Raymond Chui & Ruiming Chen Company
> Additional Information:
> Last Name Chui /Chen
> First NameRaymond/Ruiming
> Version 2.1