Index Question
A poster asked for help answering basic Informix index questions (why index, effect of having many indexes, whether ABC and CBA indexes can coexist, unique index vs. index vs. unique/primary key). Art Kagel and Jack Parker answered: indexes avoid sorts and sequential scans but each extra index adds cost to inserts/updates/deletes; ABC and CBA can coexist; UNIQUE enforces uniqueness and gives a slightly shallower (one less level) index, and constraints formalize keys for foreign keys and ER. Fernando Nunes added a practical difference: with a declared constraint, uniqueness is checked at COMMIT (so UPDATE col=col+1 succeeds), whereas a plain unique index checks row by row and fails; Art agreed, noting it rarely matters for primary keys but can for unique columns. Questions were answered.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi All, I would like to seek your comments on the below questions. I've already answered some, but not all. thanks is advance. What's the purpose of creating indices on tables? ANSWER: Creating indices on tables is done for the purpose of making the sorting faster. On the other hard, wrong indices can cause slowdown. Whats the effect of multiple indices? 4 indices vs 5 or more Is it possible to have index ABC and CBA at the same time ANSWER Yes. What's the difference between unique index and index? Unique key and unique index ANSWER: Unique index >>> can be only one record (that's why it's unique). Index >>> Just like any other DB, it is common that a table have an index which is use
Here: What's the purpose of creating indices on tables? Indexes are used for two purposes: 1) to eliminate sorting when possible, 2) to improve search processing by eliminating sequential scans which reduces IO Whats the effect of multiple indices? 4 indices vs 5 or more More indexes increase the opportunities for the optimizer to eliminate sequential scans for more queries improving select performance, however, each index adds more cost to the processing of inserts, deletes, and updates. Is it possible to have index ABC and CBA at the same time ANSWER Yes. What's the difference between unique index and index? Unique key and unique index? Declaring an index UNIQUE does two things: 1) it enforces the uniqueness of a particular set of columns to eliminate duplicate rows in a table, 2) it produces a more efficient index for keys that are indeed unique. Unique indexes have one less level than duplicate indexes. A unique or primary key uses a unique index under the hood to enforce the uniqueness of the declared key. Declaring the constraint just formalizes the relationship permitting the key to be used for foreign key relationships and for Enterprise Replication. There is not operational or performance difference between a unique index and declaring that index key to be a unique or primary key constraint. Art Art S. Kagel, Principal Consultant ASK Database Management Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, May 7, 2014 at 4:33 AM, JACK PAPA <informix2009@gmail.com> wrote: > Hi All, > > I would like to seek your comments on the below questions. I've already > answered some, but not all. thanks is advance. > > What's the purpose of creating indices on tables? > ANSWER: Creating indices on tables is done for the purpose of making the > sorting faster. On the other hard, wrong indices can cause slowdown. > Whats the effect of multiple indices? 4 indices vs 5 or more > Is it possible to have index ABC and CBA at the same time > ANSWER Yes. > What's the difference between unique index and index? Unique key and > unique > index > > ANSWER: Unique index >>> can be only one record (that's why it's unique). > > Index >>> Just like any other DB, it is common that a table have an index > which is use > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a1133b04abed4e404f8ccc868
On May 7, 2014, at 4:33 AM, JACK PAPA <informix2009@gmail.com> wrote: > Hi All,=20 >=20 > I would like to seek your comments on the below questions. I've = already=20 > answered some, but not all. thanks is advance.=20 >=20 > =95 What's the purpose of creating indices on tables?=20 > ANSWER: Creating indices on tables is done for the purpose of making = the=20 > sorting faster. On the other hard, wrong indices can cause slowdown.=20= So you can find a row quickly instead of reading the entire table. A = unique constraint is enforced through a unique index, so an attempt to = insert the same value twice in the index will cause an error. > =95 What=92s the effect of multiple indices? 4 indices vs 5 or more=20 Each index has to be maintained - every insert you do to a table is = mirrored by an insert into the index. So 5 indexes means you are doing = 1+5 writes. You would use multiple indexes when you have a need to = access a table from different angles, give me customer =91x=92, give me = data from date =91y=92, find all customers who live in this town. > =95 Is it possible to have index ABC and CBA at the same time=20 > ANSWER Yes.=20 sure > =95 What's the difference between unique index and index? Unique key = and unique=20 > index=20 >=20 > ANSWER: Unique index >>> can be only one record (that's why it's = unique).=20 ...And a non-unique index can have multiple occurrences of the same = value > Index >>> Just like any other DB, it is common that a table have an = index=20 > which is use=20 If the table is small (under 2 pages), then you might not have an index = as it would be more expensive to read the index and then the data rather = than just read one or both pages of data. j. >=20 >=20 > = **************************************************************************= *****=20 > Forum Note: Use "Reply" to post a response in the discussion forum.=20= >=20
Actually there is a practical/operational difference between a UNIQUE INDEX
and a PRIMARY KEY:
castelo@primary:informix-> dbaccess -e stores test_index.sql
Database selected.
DROP TABLE IF EXISTS test_index;Table dropped.
CREATE TABLE test_index
(
col1 INTEGER
);
Table created.
CREATE UNIQUE INDEX ix_test_index_1 ON test_index(col1);Index created.
INSERT INTO test_index VALUES (1);1 row(s) inserted.
INSERT INTO test_index VALUES (2);1 row(s) inserted.
INSERT INTO test_index VALUES (3);1 row(s) inserted.
UPDATE
test_index
SET
col1 = col1 + 1;
346: Could not update a row in the table.
100: ISAM error: duplicate value for a record with unique key.
Error in line 15Near character position 14
ALTER TABLE test_index ADD CONSTRAINT PRIMARY KEY(col1);Table altered.
UPDATE
test_index
SET
col1 = col1 + 1;
3 row(s) updated.
Database closed.
castelo@primary:informix->
With a constraint, the integrity is validated at COMMIT time. So it works.
With just the index, it's row by row...
Regards
:
On Wed, May 7, 2014 at 11:25 AM, Art Kagel <art.kagel@gmail.com> wrote:
> Here:
>
> What's the purpose of creating indices on tables?
>
> Indexes are used for two purposes: 1) to eliminate sorting when
> possible, 2) to improve search processing by eliminating sequential scans
> which reduces IO
>
> Whats the effect of multiple indices? 4 indices vs 5 or more
>
> More indexes increase the opportunities for the optimizer to eliminate
> sequential scans for more queries improving select performance, however,
> each index adds more cost to the processing of inserts, deletes, and
> updates.
>
> Is it possible to have index ABC and CBA at the same time
>
> ANSWER Yes.
> What's the difference between unique index and index? Unique key and
> unique index?
>
> Declaring an index UNIQUE does two things: 1) it enforces the uniqueness
> of a particular set of columns to eliminate duplicate rows in a table, 2)
> it produces a more efficient index for keys that are indeed unique. Unique
> indexes have one less level than duplicate indexes. A unique or primary
> key uses a unique index under the hood to enforce the uniqueness of the
> declared key. Declaring the constraint just formalizes the relationship
> permitting the key to be used for foreign key relationships and for
> Enterprise Replication. There is not operational or performance difference
> between a unique index and declaring that index key to be a unique or
> primary key constraint.
>
> Art
>
> Art S. Kagel, Principal Consultant
> ASK Database Management
>
> Blog: http://informix-myview.blogspot.com/
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions
> and do not reflect on the IIUG, nor any other organization with which I am
> associated either explicitly, implicitly, or by inference. Neither do
> those opinions reflect those of other individuals affiliated with any
> entity with which I am affiliated nor those of the entities themselves.
>
> On Wed, May 7, 2014 at 4:33 AM, JACK PAPA <informix2009@gmail.com> wrote:
>
> > Hi All,
> >
> > I would like to seek your comments on the below questions. I've already
> > answered some, but not all. thanks is advance.
> >
> > What's the purpose of creating indices on tables?
> > ANSWER: Creating indices on tables is done for the purpose of making the
> > sorting faster. On the other hard, wrong indices can cause slowdown.
> > Whats the effect of multiple indices? 4 indices vs 5 or more
> > Is it possible to have index ABC and CBA at the same time
> > ANSWER Yes.
> > What's the difference between unique index and index? Unique key and
> > unique
> > index
> >
> > ANSWER: Unique index >>> can be only one record (that's why it's unique).
> >
> > Index >>> Just like any other DB, it is common that a table have an index
> > which is use
> >
> >
> >
> >
>
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --001a1133b04abed4e404f8ccc868
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
--047d7b3a8f2c15e0bc04f8d03c16
Fernando:
Yes, you are correct. However, with a primary key in place, likely there
are foreign keys referencing the primary key and those dependent rows will
have to be updated in the same transaction. The automatic deferment does
not have an affect across multiple statements so you will have to SET
CONSTRAINTS ALL DEFERRED; after a BEGIN WORK; for constraint checking to be
deferred to commit time for the foreign keys and primary key together
anyway. If one is not using constraints in their database they get what
they deserve - kaos!
In practice, though, one ALMOST NEVER updates a primary key (and in theory
one ABSOLUTELY NEVER does so). The upshot is that you are correct, but,
since it is not a situation that really affects any real-world applications
it is kind of moot. For completeness, your point is well taken.
Art
Art S. Kagel, Principal Consultant
ASK Database Management
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on the IIUG, nor any other organization with which I am
associated either explicitly, implicitly, or by inference. Neither do
those opinions reflect those of other individuals affiliated with any
entity with which I am affiliated nor those of the entities themselves.
On Wed, May 7, 2014 at 10:32 AM, Fernando Nunes <domusonline@gmail.com>wrote:
> Actually there is a practical/operational difference between a UNIQUE INDEX
> and a PRIMARY KEY:
>
> castelo@primary:informix-> dbaccess -e stores test_index.sql
>
> Database selected.
>
> DROP TABLE IF EXISTS test_index;> Table dropped.
>
> CREATE TABLE test_index
> (>
> col1 INTEGER
> );
> Table created.
>
> CREATE UNIQUE INDEX ix_test_index_1 ON test_index(col1);> Index created.
>
> INSERT INTO test_index VALUES (1);> 1 row(s) inserted.
>
> INSERT INTO test_index VALUES (2);> 1 row(s) inserted.
>
> INSERT INTO test_index VALUES (3);> 1 row(s) inserted.
>
> UPDATE
>
> test_index
> SET
>
> col1 = col1 + 1;
> 346: Could not update a row in the table.>
> 100: ISAM error: duplicate value for a record with unique key.
> Error in line 15> Near character position 14
>
> ALTER TABLE test_index ADD CONSTRAINT PRIMARY KEY(col1);> Table altered.
>
> UPDATE
>
> test_index
> SET
>
> col1 = col1 + 1;
> 3 row(s) updated.
>
> Database closed.
>
> castelo@primary:informix->
>
> With a constraint, the integrity is validated at COMMIT time. So it works.
> With just the index, it's row by row...
> Regards
> :
>
> On Wed, May 7, 2014 at 11:25 AM, Art Kagel <art.kagel@gmail.com> wrote:
>
> > Here:
> >
> > What's the purpose of creating indices on tables?
> >
> > Indexes are used for two purposes: 1) to eliminate sorting when
> > possible, 2) to improve search processing by eliminating sequential scans
> > which reduces IO
> >
> > Whats the effect of multiple indices? 4 indices vs 5 or more
> >
> > More indexes increase the opportunities for the optimizer to eliminate
> > sequential scans for more queries improving select performance, however,
> > each index adds more cost to the processing of inserts, deletes, and
> > updates.
> >
> > Is it possible to have index ABC and CBA at the same time
> >
> > ANSWER Yes.
> > What's the difference between unique index and index? Unique key and
> > unique index?
> >
> > Declaring an index UNIQUE does two things: 1) it enforces the uniqueness
> > of a particular set of columns to eliminate duplicate rows in a table, 2)
> > it produces a more efficient index for keys that are indeed unique.
> Unique
> > indexes have one less level than duplicate indexes. A unique or primary
> > key uses a unique index under the hood to enforce the uniqueness of the
> > declared key. Declaring the constraint just formalizes the relationship
> > permitting the key to be used for foreign key relationships and for
> > Enterprise Replication. There is not operational or performance
> difference
> > between a unique index and declaring that index key to be a unique or
> > primary key constraint.
> >
> > Art
> >
> > Art S. Kagel, Principal Consultant
> > ASK Database Management
> >
> > Blog: http://informix-myview.blogspot.com/
> >
> > Disclaimer: Please keep in mind that my own opinions are my own opinions
> > and do not reflect on the IIUG, nor any other organization with which I
> am
> > associated either explicitly, implicitly, or by inference. Neither do
> > those opinions reflect those of other individuals affiliated with any
> > entity with which I am affiliated nor those of the entities themselves.
> >
> > On Wed, May 7, 2014 at 4:33 AM, JACK PAPA <informix2009@gmail.com>
> wrote:
> >
> > > Hi All,
> > >
> > > I would like to seek your comments on the below questions. I've already
> > > answered some, but not all. thanks is advance.
> > >
> > > What's the purpose of creating indices on tables?
> > > ANSWER: Creating indices on tables is done for the purpose of making
> the
> > > sorting faster. On the other hard, wrong indices can cause slowdown.
> > > Whats the effect of multiple indices? 4 indices vs 5 or more
> > > Is it possible to have index ABC and CBA at the same time
> > > ANSWER Yes.
> > > What's the difference between unique index and index? Unique key and
> > > unique
> > > index
> > >
> > > ANSWER: Unique index >>> can be only one record (that's why it's
> unique).
> > >
> > > Index >>> Just like any other DB, it is common that a table have an
> index
> > > which is use
> > >
> > >
> > >
> > >
> >
> >
>
>
*******************************************************************************
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> > >
> >
> > --001a1133b04abed4e404f8ccc868
> >
> >
> >
> >
>
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --
> Fernando Nunes
> Portugal
>
> http://informix-technology.blogspot.com
> My email works... but I don't check it frequently...
>
> --047d7b3a8f2c15e0bc04f8d03c16
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--089e012292d8adc5c704f8d0722c
100% agreed... I think I have seen this only once in a customer situation...
On Wed, May 7, 2014 at 3:47 PM, Art Kagel <art.kagel@gmail.com> wrote:
> Fernando:
>
> Yes, you are correct. However, with a primary key in place, likely there
> are foreign keys referencing the primary key and those dependent rows will
> have to be updated in the same transaction. The automatic deferment does
> not have an affect across multiple statements so you will have to SET
> CONSTRAINTS ALL DEFERRED; after a BEGIN WORK; for constraint checking to be
> deferred to commit time for the foreign keys and primary key together
> anyway. If one is not using constraints in their database they get what
> they deserve - kaos!
>
> In practice, though, one ALMOST NEVER updates a primary key (and in theory
> one ABSOLUTELY NEVER does so). The upshot is that you are correct, but,
> since it is not a situation that really affects any real-world applications
> it is kind of moot. For completeness, your point is well taken.
>
> Art
>
> Art S. Kagel, Principal Consultant
> ASK Database Management
>
> Blog: http://informix-myview.blogspot.com/
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions
> and do not reflect on the IIUG, nor any other organization with which I am
> associated either explicitly, implicitly, or by inference. Neither do
> those opinions reflect those of other individuals affiliated with any
> entity with which I am affiliated nor those of the entities themselves.
>
> On Wed, May 7, 2014 at 10:32 AM, Fernando Nunes <domusonline@gmail.com
> >wrote:
>
> > Actually there is a practical/operational difference between a UNIQUE
> INDEX
> > and a PRIMARY KEY:
> >
> > castelo@primary:informix-> dbaccess -e stores test_index.sql
> >
> > Database selected.
> >
> > DROP TABLE IF EXISTS test_index;> > Table dropped.
> >
> > CREATE TABLE test_index
> > (> >
> > col1 INTEGER
> > );
> > Table created.
> >
> > CREATE UNIQUE INDEX ix_test_index_1 ON test_index(col1);> > Index created.
> >
> > INSERT INTO test_index VALUES (1);> > 1 row(s) inserted.
> >
> > INSERT INTO test_index VALUES (2);> > 1 row(s) inserted.
> >
> > INSERT INTO test_index VALUES (3);> > 1 row(s) inserted.
> >
> > UPDATE
> >
> > test_index
> > SET
> >
> > col1 = col1 + 1;
> > 346: Could not update a row in the table.> >
> > 100: ISAM error: duplicate value for a record with unique key.
> > Error in line 15> > Near character position 14
> >
> > ALTER TABLE test_index ADD CONSTRAINT PRIMARY KEY(col1);> > Table altered.
> >
> > UPDATE
> >
> > test_index
> > SET
> >
> > col1 = col1 + 1;
> > 3 row(s) updated.
> >
> > Database closed.
> >
> > castelo@primary:informix->
> >
> > With a constraint, the integrity is validated at COMMIT time. So it
> works.
> > With just the index, it's row by row...
> > Regards
> > :
> >
> > On Wed, May 7, 2014 at 11:25 AM, Art Kagel <art.kagel@gmail.com> wrote:
> >
> > > Here:
> > >
> > > What's the purpose of creating indices on tables?
> > >
> > > Indexes are used for two purposes: 1) to eliminate sorting when
> > > possible, 2) to improve search processing by eliminating sequential
> scans
> > > which reduces IO
> > >
> > > Whats the effect of multiple indices? 4 indices vs 5 or more
> > >
> > > More indexes increase the opportunities for the optimizer to eliminate
> > > sequential scans for more queries improving select performance,
> however,
> > > each index adds more cost to the processing of inserts, deletes, and
> > > updates.
> > >
> > > Is it possible to have index ABC and CBA at the same time
> > >
> > > ANSWER Yes.
> > > What's the difference between unique index and index? Unique key and
> > > unique index?
> > >
> > > Declaring an index UNIQUE does two things: 1) it enforces the
> uniqueness
> > > of a particular set of columns to eliminate duplicate rows in a table,
> 2)
> > > it produces a more efficient index for keys that are indeed unique.
> > Unique
> > > indexes have one less level than duplicate indexes. A unique or primary
> > > key uses a unique index under the hood to enforce the uniqueness of the
> > > declared key. Declaring the constraint just formalizes the relationship
> > > permitting the key to be used for foreign key relationships and for
> > > Enterprise Replication. There is not operational or performance
> > difference
> > > between a unique index and declaring that index key to be a unique or
> > > primary key constraint.
> > >
> > > Art
> > >
> > > Art S. Kagel, Principal Consultant
> > > ASK Database Management
> > >
> > > Blog: http://informix-myview.blogspot.com/
> > >
> > > Disclaimer: Please keep in mind that my own opinions are my own
> opinions
> > > and do not reflect on the IIUG, nor any other organization with which I
> > am
> > > associated either explicitly, implicitly, or by inference. Neither do
> > > those opinions reflect those of other individuals affiliated with any
> > > entity with which I am affiliated nor those of the entities themselves.
> > >
> > > On Wed, May 7, 2014 at 4:33 AM, JACK PAPA <informix2009@gmail.com>
> > wrote:
> > >
> > > > Hi All,
> > > >
> > > > I would like to seek your comments on the below questions. I've
> already
> > > > answered some, but not all. thanks is advance.
> > > >
> > > > What's the purpose of creating indices on tables?
> > > > ANSWER: Creating indices on tables is done for the purpose of making
> > the
> > > > sorting faster. On the other hard, wrong indices can cause slowdown.
> > > > Whats the effect of multiple indices? 4 indices vs 5 or more
> > > > Is it possible to have index ABC and CBA at the same time
> > > > ANSWER Yes.
> > > > What's the difference between unique index and index? Unique key and
> > > > unique
> > > > index
> > > >
> > > > ANSWER: Unique index >>> can be only one record (that's why it's
> > unique).
> > > >
> > > > Index >>> Just like any other DB, it is common that a table have an
> > index
> > > > which is use
> > > >
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>
*******************************************************************************
> > > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > > >
> > > >
> > >
> > > --001a1133b04abed4e404f8ccc868
> > >
> > >
> > >
> > >
> >
> >
>
>
*******************************************************************************
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> > >
> >
> > --
> > Fernando Nunes
> > Portugal
> >
> > http://informix-technology.blogspot.com
> > My email works... but I don't check it frequently...
> >
> > --047d7b3a8f2c15e0bc04f8d03c16
> >
> >
> >
> >
>
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response
However... the situation can happen with UNIQUE columns (not necessarily
primary keys). And those are more susceptible of being updated.
Regards
On Wed, May 7, 2014 at 4:26 PM, Fernando Nunes <domusonline@gmail.com>wrote:
> 100% agreed... I think I have seen this only once in a customer
> situation...
>
> On Wed, May 7, 2014 at 3:47 PM, Art Kagel <art.kagel@gmail.com> wrote:
>
> > Fernando:
> >
> > Yes, you are correct. However, with a primary key in place, likely there
> > are foreign keys referencing the primary key and those dependent rows
> will
> > have to be updated in the same transaction. The automatic deferment does
> > not have an affect across multiple statements so you will have to SET
> > CONSTRAINTS ALL DEFERRED; after a BEGIN WORK; for constraint checking to
> be
> > deferred to commit time for the foreign keys and primary key together
> > anyway. If one is not using constraints in their database they get what
> > they deserve - kaos!
> >
> > In practice, though, one ALMOST NEVER updates a primary key (and in
> theory
> > one ABSOLUTELY NEVER does so). The upshot is that you are correct, but,
> > since it is not a situation that really affects any real-world
> applications
> > it is kind of moot. For completeness, your point is well taken.
> >
> > Art
> >
> > Art S. Kagel, Principal Consultant
> > ASK Database Management
> >
> > Blog: http://informix-myview.blogspot.com/
> >
> > Disclaimer: Please keep in mind that my own opinions are my own opinions
> > and do not reflect on the IIUG, nor any other organization with which I
> am
> > associated either explicitly, implicitly, or by inference. Neither do
> > those opinions reflect those of other individuals affiliated with any
> > entity with which I am affiliated nor those of the entities themselves.
> >
> > On Wed, May 7, 2014 at 10:32 AM, Fernando Nunes <domusonline@gmail.com
> > >wrote:
> >
> > > Actually there is a practical/operational difference between a UNIQUE
> > INDEX
> > > and a PRIMARY KEY:
> > >
> > > castelo@primary:informix-> dbaccess -e stores test_index.sql
> > >
> > > Database selected.
> > >
> > > DROP TABLE IF EXISTS test_index;> > > Table dropped.
> > >
> > > CREATE TABLE test_index
> > > (> > >
> > > col1 INTEGER
> > > );
> > > Table created.
> > >
> > > CREATE UNIQUE INDEX ix_test_index_1 ON test_index(col1);> > > Index created.
> > >
> > > INSERT INTO test_index VALUES (1);> > > 1 row(s) inserted.
> > >
> > > INSERT INTO test_index VALUES (2);> > > 1 row(s) inserted.
> > >
> > > INSERT INTO test_index VALUES (3);> > > 1 row(s) inserted.
> > >
> > > UPDATE
> > >
> > > test_index
> > > SET
> > >
> > > col1 = col1 + 1;
> > > 346: Could not update a row in the table.> > >
> > > 100: ISAM error: duplicate value for a record with unique key.
> > > Error in line 15> > > Near character position 14
> > >
> > > ALTER TABLE test_index ADD CONSTRAINT PRIMARY KEY(col1);> > > Table altered.
> > >
> > > UPDATE
> > >
> > > test_index
> > > SET
> > >
> > > col1 = col1 + 1;
> > > 3 row(s) updated.
> > >
> > > Database closed.
> > >
> > > castelo@primary:informix->
> > >
> > > With a constraint, the integrity is validated at COMMIT time. So it
> > works.
> > > With just the index, it's row by row...
> > > Regards
> > > :
> > >
> > > On Wed, May 7, 2014 at 11:25 AM, Art Kagel <art.kagel@gmail.com>
> wrote:
> > >
> > > > Here:
> > > >
> > > > What's the purpose of creating indices on tables?
> > > >
> > > > Indexes are used for two purposes: 1) to eliminate sorting when
> > > > possible, 2) to improve search processing by eliminating sequential
> > scans
> > > > which reduces IO
> > > >
> > > > Whats the effect of multiple indices? 4 indices vs 5 or more
> > > >
> > > > More indexes increase the opportunities for the optimizer to
> eliminate
> > > > sequential scans for more queries improving select performance,
> > however,
> > > > each index adds more cost to the processing of inserts, deletes, and
> > > > updates.
> > > >
> > > > Is it possible to have index ABC and CBA at the same time
> > > >
> > > > ANSWER Yes.
> > > > What's the difference between unique index and index? Unique key and
> > > > unique index?
> > > >
> > > > Declaring an index UNIQUE does two things: 1) it enforces the
> > uniqueness
> > > > of a particular set of columns to eliminate duplicate rows in a
> table,
> > 2)
> > > > it produces a more efficient index for keys that are indeed unique.
> > > Unique
> > > > indexes have one less level than duplicate indexes. A unique or
> primary
> > > > key uses a unique index under the hood to enforce the uniqueness of
> the
> > > > declared key. Declaring the constraint just formalizes the
> relationship
> > > > permitting the key to be used for foreign key relationships and for
> > > > Enterprise Replication. There is not operational or performance
> > > difference
> > > > between a unique index and declaring that index key to be a unique or
> > > > primary key constraint.
> > > >
> > > > Art
> > > >
> > > > Art S. Kagel, Principal Consultant
> > > > ASK Database Management
> > > >
> > > > Blog: http://informix-myview.blogspot.com/
> > > >
> > > > Disclaimer: Please keep in mind that my own opinions are my own
> > opinions
> > > > and do not reflect on the IIUG, nor any other organization with
> which I
> > > am
> > > > associated either explicitly, implicitly, or by inference. Neither do
> > > > those opinions reflect those of other individuals affiliated with any
> > > > entity with which I am affiliated nor those of the entities
> themselves.
> > > >
> > > > On Wed, May 7, 2014 at 4:33 AM, JACK PAPA <informix2009@gmail.com>
> > > wrote:
> > > >
> > > > > Hi All,
> > > > >
> > > > > I would like to seek your comments on the below questions. I've
> > already
> > > > > answered some, but not all. thanks is advance.
> > > > >
> > > > > What's the purpose of creating indices on tables?
> > > > > ANSWER: Creating indices on tables is done for the purpose of
> making
> > > the
> > > > > sorting faster. On the other hard, wrong indices can cause
> slowdown.
> > > > > Whats the effect of multiple indices? 4 indices vs 5 or more
> > > > > Is it possible to have index ABC and CBA at the same time
> > > > > ANSWER Yes.
> > > > > What's the difference between unique index and index? Unique key
> and
> > > > > unique
> > > > > index
> > > > >
> > > > > ANSWER: Unique index >>> can be only one record (that's why it's
> > > unique).
> > > > >
> > > > > Index >>> Just like any other DB, it is common that a table have an
> > > index
> > > > > which is use
> > > > >
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>
*******************************************************************************
> > > > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > > >
True also and also less likely to be used as foreign keys.
Art
Art S. Kagel, Principal Consultant
ASK Database Management
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on the IIUG, nor any other organization with which I am
associated either explicitly, implicitly, or by inference. Neither do
those opinions reflect those of other individuals affiliated with any
entity with which I am affiliated nor those of the entities themselves.
On Wed, May 7, 2014 at 12:11 PM, Fernando Nunes <domusonline@gmail.com>wrote:
> However... the situation can happen with UNIQUE columns (not necessarily
> primary keys). And those are more susceptible of being updated.
> Regards
>
> On Wed, May 7, 2014 at 4:26 PM, Fernando Nunes <domusonline@gmail.com
> >wrote:
>
> > 100% agreed... I think I have seen this only once in a customer
> > situation...
> >
> > On Wed, May 7, 2014 at 3:47 PM, Art Kagel <art.kagel@gmail.com> wrote:
> >
> > > Fernando:
> > >
> > > Yes, you are correct. However, with a primary key in place, likely
> there
> > > are foreign keys referencing the primary key and those dependent rows
> > will
> > > have to be updated in the same transaction. The automatic deferment
> does
> > > not have an affect across multiple statements so you will have to SET
> > > CONSTRAINTS ALL DEFERRED; after a BEGIN WORK; for constraint checking
> to
> > be
> > > deferred to commit time for the foreign keys and primary key together
> > > anyway. If one is not using constraints in their database they get what
> > > they deserve - kaos!
> > >
> > > In practice, though, one ALMOST NEVER updates a primary key (and in
> > theory
> > > one ABSOLUTELY NEVER does so). The upshot is that you are correct, but,
> > > since it is not a situation that really affects any real-world
> > applications
> > > it is kind of moot. For completeness, your point is well taken.
> > >
> > > Art
> > >
> > > Art S. Kagel, Principal Consultant
> > > ASK Database Management
> > >
> > > Blog: http://informix-myview.blogspot.com/
> > >
> > > Disclaimer: Please keep in mind that my own opinions are my own
> opinions
> > > and do not reflect on the IIUG, nor any other organization with which I
> > am
> > > associated either explicitly, implicitly, or by inference. Neither do
> > > those opinions reflect those of other individuals affiliated with any
> > > entity with which I am affiliated nor those of the entities themselves.
> > >
> > > On Wed, May 7, 2014 at 10:32 AM, Fernando Nunes <domusonline@gmail.com
> > > >wrote:
> > >
> > > > Actually there is a practical/operational difference between a UNIQUE
> > > INDEX
> > > > and a PRIMARY KEY:
> > > >
> > > > castelo@primary:informix-> dbaccess -e stores test_index.sql
> > > >
> > > > Database selected.
> > > >
> > > > DROP TABLE IF EXISTS test_index;> > > > Table dropped.
> > > >
> > > > CREATE TABLE test_index
> > > > (> > > >
> > > > col1 INTEGER
> > > > );
> > > > Table created.
> > > >
> > > > CREATE UNIQUE INDEX ix_test_index_1 ON test_index(col1);> > > > Index created.
> > > >
> > > > INSERT INTO test_index VALUES (1);> > > > 1 row(s) inserted.
> > > >
> > > > INSERT INTO test_index VALUES (2);> > > > 1 row(s) inserted.
> > > >
> > > > INSERT INTO test_index VALUES (3);> > > > 1 row(s) inserted.
> > > >
> > > > UPDATE
> > > >
> > > > test_index
> > > > SET
> > > >
> > > > col1 = col1 + 1;
> > > > 346: Could not update a row in the table.> > > >
> > > > 100: ISAM error: duplicate value for a record with unique key.
> > > > Error in line 15> > > > Near character position 14
> > > >
> > > > ALTER TABLE test_index ADD CONSTRAINT PRIMARY KEY(col1);> > > > Table altered.
> > > >
> > > > UPDATE
> > > >
> > > > test_index
> > > > SET
> > > >
> > > > col1 = col1 + 1;
> > > > 3 row(s) updated.
> > > >
> > > > Database closed.
> > > >
> > > > castelo@primary:informix->
> > > >
> > > > With a constraint, the integrity is validated at COMMIT time. So it
> > > works.
> > > > With just the index, it's row by row...
> > > > Regards
> > > > :
> > > >
> > > > On Wed, May 7, 2014 at 11:25 AM, Art Kagel <art.kagel@gmail.com>
> > wrote:
> > > >
> > > > > Here:
> > > > >
> > > > > What's the purpose of creating indices on tables?
> > > > >
> > > > > Indexes are used for two purposes: 1) to eliminate sorting when
> > > > > possible, 2) to improve search processing by eliminating sequential
> > > scans
> > > > > which reduces IO
> > > > >
> > > > > Whats the effect of multiple indices? 4 indices vs 5 or more
> > > > >
> > > > > More indexes increase the opportunities for the optimizer to
> > eliminate
> > > > > sequential scans for more queries improving select performance,
> > > however,
> > > > > each index adds more cost to the processing of inserts, deletes,
> and
> > > > > updates.
> > > > >
> > > > > Is it possible to have index ABC and CBA at the same time
> > > > >
> > > > > ANSWER Yes.
> > > > > What's the difference between unique index and index? Unique key
> and
> > > > > unique index?
> > > > >
> > > > > Declaring an index UNIQUE does two things: 1) it enforces the
> > > uniqueness
> > > > > of a particular set of columns to eliminate duplicate rows in a
> > table,
> > > 2)
> > > > > it produces a more efficient index for keys that are indeed unique.
> > > > Unique
> > > > > indexes have one less level than duplicate indexes. A unique or
> > primary
> > > > > key uses a unique index under the hood to enforce the uniqueness of
> > the
> > > > > declared key. Declaring the constraint just formalizes the
> > relationship
> > > > > permitting the key to be used for foreign key relationships and for
> > > > > Enterprise Replication. There is not operational or performance
> > > > difference
> > > > > between a unique index and declaring that index key to be a unique
> or
> > > > > primary key constraint.
> > > > >
> > > > > Art
> > > > >
> > > > > Art S. Kagel, Principal Consultant
> > > > > ASK Database Management
> > > > >
> > > > > Blog: http://informix-myview.blogspot.com/
> > > > >
> > > > > Disclaimer: Please keep in mind that my own opinions are my own
> > > opinions
> > > > > and do not reflect on the IIUG, nor any other organization with
> > which I
> > > > am
> > > > > associated either explicitly, implicitly, or by inference. Neither
> do
> > > > > those opinions reflect those of other individuals affiliated with
> any
> > > > > entity with which I am affiliated nor those of the entities
> > themselves.
> > > > >
> > > > > On Wed, May 7, 2014 at 4:33 AM, JACK PAPA <informix2009@gmail.com>
> > > > wrote:
> > > > >
> > > > > > Hi All,
> > > > > >
> > > > > > I would like to seek your comments on the below questions. I've
> > > already
> > > > > > answered some, but not all. thanks is advance.
> > > > > >
> > > > > > What's the purp