1. I just want to know if informix store create index or alter table
type of scripts in some table. I am assuming that informix stores them
in tables otherwise how would dbschema get these ?

Could you please let me know.

2. Also which is better enable indexes or create indexes. Does update
stats work better on create indexes ?

Here's a little article that should answer your first question.

http://www.ibm.com/developerworks/db...echarticle/030
5parker/0305parker.html

I'm not touching the second one.

j.

-----Original Message-----
From: informix-list-bounces (AT) iiug (DOT) org
[mailto:informix-list-bounces (AT) iiug (DOT) org]On Behalf Of
mohitanchlia (AT) gmail (DOT) com
Sent: Sunday, June 17, 2007 3:35 PM
To: informix-list (AT) iiug (DOT) org
Subject: DDL or DML scripts AND enable vs create index


1. I just want to know if informix store create index or alter table
type of scripts in some table. I am assuming that informix stores them
in tables otherwise how would dbschema get these ?

Could you please let me know.

2. Also which is better enable indexes or create indexes. Does update
stats work better on create indexes ?

_______________________________________________
Informix-list mailing list
Informix-list (AT) iiug (DOT) org
http://www.iiug.org/mailman/listinfo/informix-list

If you need more detail, try the the create_idx function in dbdiff2. It
gets all of it's information from the system catalogues - just as dbschema
does.

http://www.iiug.org/software/archive/dbdiff2

j.
-----Original Message-----
From: Mohit Anchlia [mailto:mohitanchlia (AT) gmail (DOT) com]
Sent: Sunday, June 17, 2007 11:46 PM
To: Jack Parker; informix-list (AT) iiug (DOT) org
Subject: Re: DDL or DML scripts AND enable vs create index


But how do I get the complete index's script. For eg: dbschema gives "create
index asssa" script, how do I get this from the database directly. I see
that you could retrieve procedure for sysprocbody table, but how do I get
the indexes.

Also if somebody could throw some light on if creating indexes are better
than enabling indexes. Also does update stats work better with creating
indexes or when indexes are enabled.



On 6/17/07, Jack Parker <jack.parker4 (AT) verizon (DOT) net> wrote:

Here's a little article that should answer your first question.


http://www.ibm.com/developerworks/db...echarticle/030
5parker/0305parker.html

I'm not touching the second one.

j.

-----Original Message-----
From: informix-list-bounces (AT) iiug (DOT) org
[mailto: informix-list-bounces (AT) iiug (DOT) org]On Behalf Of
mohitanchlia (AT) gmail (DOT) com
Sent: Sunday, June 17, 2007 3:35 PM
To: informix-list (AT) iiug (DOT) org
Subject: DDL or DML scripts AND enable vs create index


1. I just want to know if informix store create index or alter table
type of scripts in some table. I am assuming that informix stores them
in tables otherwise how would dbschema get these ?

Could you please let me know.

2. Also which is better enable indexes or create indexes. Does update
stats work better on create indexes ?

_______________________________________________
Informix-list mailing list
Informix-list (AT) iiug (DOT) org
http://www.iiug.org/mailman/listinfo/informix-list

mohitanchlia (AT) gmail (DOT) com wrote:
Enabling an index is similar to create a new one.
Disable indexes have the advantage of appearing in the schema... But to be
honest I don't find them very useful...

Update statistics may or may not be influenced by this... To be honest I don't
know, but I assume that LOW stats are not collected for disabled indexes... It
simply doesn't make sense, since these indexes are never used...

If I didn't catch your idea completely please insist.
Regards,


--
Fernando Nunes
Portugal

http://informix-technology.blogspot.com
My email works... but I don't check it frequently...

mohitanchlia (AT) gmail (DOT) com wrote:
1. As for any RDBMS, catalog tables serve the purpose. So, tables such as
systables, store the table information for the database, syscolumns store
the
column info for each table, sysindexes store indexes for each table,
sysconstraints constraints .. and so on and so forth.

2. I'm not getting this ..? if you don't create the indexes how's it going
to be disabled or enabled ? Update stats work with or without indexes.. if
you have
indexes on tables then it'll have better information on the column
values and can make the access faster by choosing a plan which is least
costly if its got
good information.

HTH

Rajib Sarkar
Sr. Technical Analyst
DB2 RPD Team ( Level 3 Support )



HTH

Thanks for the info. I've read some other comments and everybody
suggests that dropping and creating indexes are better than disabling
them, but haven't got explanation as to why is it like that. Why is
creating index better than enabling them - is there something
different between them that occurs underneath.
On Jun 19, 2:49 pm, Fernando Nunes <s... (AT) domus (DOT) online.pt> wrote:

On Jun 20, 11:48 am, mohitanch... (AT) gmail (DOT) com wrote:
One question: If the indexes already exist, so that you COULD disable,
then reenable them, why do you need to do that?

Are your trying to determine the best way to reorganize an index?
You are aware that you are using IDS which is quite capable of
maintaining its indexes without the neccessity of reorganizing them,
almost EVER? The btree cleaner threads maintain the indexes in
optimal condition most of the time. The only time that a reorg MIGHT
be needed is immediately after a huge delete or update of indexed
columns when you cannot wait for the cleaners to catch up. Otherwise,
just chill.

I don't work with them enough to know, but are Orable, DB2, SQL
Server, and Sybase so bad at online index maintenance that DBAs spend
valuable time reorging indexes constantly?

Art S. Kagel