Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
create & drop index definitions generator 1
- Thread starter vpshriyan
- Start date
- Status
-
1
- Thread starter
- #3
Hi WiccaChic,
Greetings! Thanks for your response.
Well, you can put this script into practical use. some of them are:
1. Serves as a ready index reference document to a DBA or others who are associated with the database.
2. Indexing strategy facilitates faster access, enforce constraints & business rules, hence, attracts greater importance in an application. This script generates clear and precise index definitions without much difficulty, hardship or effort. Further, you may modify/customise it making it more suitable for your requirement.
3. dbschema is not designed to generate index only definitions. In the full schema definition they are flung into multiple lines, leading to difficulty in selecting all those lines of information.
Test the following command and draw your conclusion:
dbschema -d <your_db_name> -ss | egrep -i "create unique index|create index| in " | pg
4. As informix documents suggests, it would be favorable or advantageous to drop and recreate all existing indexes on certain frequency. This reduces the index extents interleaving or fragmentations and improves index performance. By using the SQL scripts generated by this utility one may automate this process.
And you may think of more ...
Regards,
Shriyan
Greetings! Thanks for your response.
Well, you can put this script into practical use. some of them are:
1. Serves as a ready index reference document to a DBA or others who are associated with the database.
2. Indexing strategy facilitates faster access, enforce constraints & business rules, hence, attracts greater importance in an application. This script generates clear and precise index definitions without much difficulty, hardship or effort. Further, you may modify/customise it making it more suitable for your requirement.
3. dbschema is not designed to generate index only definitions. In the full schema definition they are flung into multiple lines, leading to difficulty in selecting all those lines of information.
Test the following command and draw your conclusion:
dbschema -d <your_db_name> -ss | egrep -i "create unique index|create index| in " | pg
4. As informix documents suggests, it would be favorable or advantageous to drop and recreate all existing indexes on certain frequency. This reduces the index extents interleaving or fragmentations and improves index performance. By using the SQL scripts generated by this utility one may automate this process.
And you may think of more ...
Regards,
Shriyan
- Thread starter
- #5
Hi Shriyan,
I have a question about index extents.
Just did a dbexport to defrag a DB, I modified the initial extent/next extent numbers where needed, and dbimported.
I got the following info using oncheck -pT.
The table I'm using for an example has all data pages in the 1st extent (extent size 82837, next size 8283) as expected but the initial extent size for all indexes ranges from 757 to 1818 and next extent size ranges from 7 to 18.
Some indexes have 60+ extents right after defragging.
I know that extents created contiguously are treated as one extent but where are the initial extent/next extent #'s for extents coming from? I thought these numbers would be inherited from the table but they're not so I'm confused.
Any index extents created later due to database activity will be heavily fragmented quickly with these very small next extent sizes.
Thanks for taking the time to look at this,
Mike
I have a question about index extents.
Just did a dbexport to defrag a DB, I modified the initial extent/next extent numbers where needed, and dbimported.
I got the following info using oncheck -pT.
The table I'm using for an example has all data pages in the 1st extent (extent size 82837, next size 8283) as expected but the initial extent size for all indexes ranges from 757 to 1818 and next extent size ranges from 7 to 18.
Some indexes have 60+ extents right after defragging.
I know that extents created contiguously are treated as one extent but where are the initial extent/next extent #'s for extents coming from? I thought these numbers would be inherited from the table but they're not so I'm confused.
Any index extents created later due to database activity will be heavily fragmented quickly with these very small next extent sizes.
Thanks for taking the time to look at this,
Mike
- Thread starter
- #7
Hi Mike,
Sorry for the delayed response from my side. I have a question; Do you have detached indexes? I mean, is your indexes are created in other space than where the tables are created? If your answer is No, then your are in serious trouble.
Assume that a table has an initial extent of 'n' number of pages by default. It is possible that it might contain primary key, which is enforced via a unique index, again on which a dba has no control as for the storage location specification is concerned. Hence, certain pages out of those 'n' number of pages will be used for primary index storage. You may have non-primary indexes present too. Again it will also be split or adjusted with those 'n' number of pages. This way the extent will fill-up in faster pace and next extent will be spawned, leading multiplicity of extents.
On the contrary, if you have created the indexes in separate dbspace, you get most of the pages present in the extent for index storage barring bit-map stored (overhead) pages. As a result you get a contiguous index storage area, leading to less fragments and extents.
Theoretically speaking, a detached index should derive its initial and next extents from the attached table definition. However, in practice it does not! I have notice around 10-20% reduction in effective index extent usages. That means if you got a table having initial extent size of 80,000 Kb, you will have a index intial extent of around 68,000 Kb. or evern lesser. But, it obvious that greater the size of table exetents, greater would be the index extents too.
Regards,
Shriyan
Sorry for the delayed response from my side. I have a question; Do you have detached indexes? I mean, is your indexes are created in other space than where the tables are created? If your answer is No, then your are in serious trouble.
Assume that a table has an initial extent of 'n' number of pages by default. It is possible that it might contain primary key, which is enforced via a unique index, again on which a dba has no control as for the storage location specification is concerned. Hence, certain pages out of those 'n' number of pages will be used for primary index storage. You may have non-primary indexes present too. Again it will also be split or adjusted with those 'n' number of pages. This way the extent will fill-up in faster pace and next extent will be spawned, leading multiplicity of extents.
On the contrary, if you have created the indexes in separate dbspace, you get most of the pages present in the extent for index storage barring bit-map stored (overhead) pages. As a result you get a contiguous index storage area, leading to less fragments and extents.
Theoretically speaking, a detached index should derive its initial and next extents from the attached table definition. However, in practice it does not! I have notice around 10-20% reduction in effective index extent usages. That means if you got a table having initial extent size of 80,000 Kb, you will have a index intial extent of around 68,000 Kb. or evern lesser. But, it obvious that greater the size of table exetents, greater would be the index extents too.
Regards,
Shriyan
Hi Shriyan,
Thanks for replying.
The indexes are detached, located in a separate dbspace.
We are a PeopleSoft shop so many of the tables (14K+) have 7+ non-primary indices & I find it alarming that a highly volatility table will have indices (primary & non-primary)with initial extent / next extent sizes that are ridiculously small.
Some indices have an initial extent size that is 1% of table's initial extent size so I guess I just have to accept this as the way Informix intends the IDS product to function.
Thanks again,
Mike
Thanks for replying.
The indexes are detached, located in a separate dbspace.
We are a PeopleSoft shop so many of the tables (14K+) have 7+ non-primary indices & I find it alarming that a highly volatility table will have indices (primary & non-primary)with initial extent / next extent sizes that are ridiculously small.
Some indices have an initial extent size that is 1% of table's initial extent size so I guess I just have to accept this as the way Informix intends the IDS product to function.
Thanks again,
Mike
byrdfarmer
Vendor
Hello mjldba,
Can you fill me in on why you went with seperating your indices from there tables? I have been considering trying this to improve peformance on at least one VERY HUGE table but I have been a little nervous and I wonder how I might gauge the return if any?
Can you fill me in on why you went with seperating your indices from there tables? I have been considering trying this to improve peformance on at least one VERY HUGE table but I have been a little nervous and I wonder how I might gauge the return if any?
Hi byrdfarmer,
This is actually a question better answered by someone like Shriyan who has achieved IDS guru status in my book.
I separate indices from data in different dbspaces because it was recommended by a friend with many yrs of IDS experience. It also allows me to spread the index load / data table load across different HD's easily.
I suspect you might see some performance improvement by intentionally fragmenting the huge table across many spindles but this is something I've never tried and I would not want to lead you astray with untested / untried info.
Sorry I haven't been much help.
Good luck with your issues
Mike
This is actually a question better answered by someone like Shriyan who has achieved IDS guru status in my book.
I separate indices from data in different dbspaces because it was recommended by a friend with many yrs of IDS experience. It also allows me to spread the index load / data table load across different HD's easily.
I suspect you might see some performance improvement by intentionally fragmenting the huge table across many spindles but this is something I've never tried and I would not want to lead you astray with untested / untried info.
Sorry I haven't been much help.
Good luck with your issues
Mike
- Status
Similar threads
- Locked
- Question