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 ?

mohitanchlia@gmail.com wrote:
> 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 ?
>

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@gmail.com wrote:
> 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 ?
>

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...@domus.online.pt> wrote:
> mohitanch...@gmail.com wrote:
> > 1. I just want to know if informix storecreateindexor 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 betterenableindexes orcreateindexes. Does update
> > stats work better oncreateindexes ?
>
> Enabling anindexis similar tocreatea 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...

On Jun 20, 11:48 am, mohitanch...@gmail.com wrote:
> 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...@domus.online.pt> wrote:
>
> > mohitanch...@gmail.com wrote:
> > > 1. I just want to know if informix storecreateindexor 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 betterenableindexes orcreateindexes. Does update
> > > stats work better oncreateindexes ?
>
> > Enabling anindexis similar tocreatea 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...

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