Re: Problem with the sysmaster database.

Jiten Thakkar said: > Hi , > > Environment : > Hardware : Sun4u-Sparc/Ultra > O/S : Solaris 8 > Informix : 9.30.FC2 ( 64 bit) > > I have a database with 40,000 tables. The data warehouse application creates > new tables to store the information for new products. > > My problem is the query on SYSMASTER database is very slow. A simple select > from syslocks ( select * from syslocks where tabname = <some_table> ) takes > 5 minutes. I have run updates statistics on this table. My fact table has 2 > million rows and the select for one of the rows comes back immediately. Can > somebody suggest how to improve the performance of the SYSMASTER database ? > > Thanks, > Jiten > sending to informix-list You can see explain plan for your query. select {+ explain avoid_execute} * from syslocks where tabname = 'some_table' DIRECTIVES FOLLOWED:EXPLAIN AVOID_EXECUTE DIRECTIVES NOT FOLLOWED: Estimated Cost: 84 Estimated # of Rows Returned: 10 1) informix.systabnames: SEQUENTIAL SCAN Filters: informix.systabnames.tabname = 'some_table' 2) informix.syslcktab: SEQUENTIAL SCAN DYNAMIC HASH JOIN Dynamic Hash Filters: informix.syslcktab.partnum = informix.systabnames.partnum ... Actually syslocks is a view. To find locks on some_table it scans the systabnames table. Because systabnames doesn't have index on tabname. It only has index on partnum. You can use this query, it works much faster select * from syslocktab where lk_partnum = (select partnum from <some_database>:systables where tabname = <some_table>)