Re: Query to find in-place alter pending tables running forever

select {+ ORDERED } pg_partnum + pg_pagenum - 1 partn from sysdbspaces a, syspaghdr where pg_partnum = 1048576 * a.dbsnum + 1 and pg_next != 0 into temp pp with no log otherwise you will read each and every page in your instance and this may give you grey hair whenever it is finished dependant on how big your instance is... See you Superboer. Vineet Mehrotra <vin_us@yahoo.com> wrote in message news:<cmb8jh$kio$1@news.xmission.com>... > HI ALL, > > IHAC who is planning to upgrade from IDS 7.31 UD6 to > 9.40 FC4. Now as part > of the upgrade plan, we would like to run a dummy > update for tables which > are in in-place alter pending state. > > We have 5 prodction instances on two separate unix > servers. On one of the > servers, the query is running fine and returned the > result set pretty > fast. But on the other instance, it is just sleeping > forever. I tried > running set explain on and the place where it is > running slow, it is doing > a sequential scan on one of the tables. Now my > question is, how can I > change the behavior to do index scan. > > here is what I am doing. > > 1. Set OPTCOMPIND to 0. > > 2. Run the query > > dbaccess sysmaster << EOF > > set isolation to dirty read;> select pg_partnum + pg_pagenum - 1 partn > from syspaghdr, sysdbspaces a > where pg_partnum = 1048576 * a.dbsnum + 1 > and pg_next != 0 > into temp pp with no log; > > select b.dbsname database, b.tabname table > from systabnames b, pp where partn = > partnum; > EOF > > 3. Here is the set explain where it is running fine > > QUERY: > ------ > select pg_partnum + pg_pagenum - 1 partn > from syspaghdr, sysdbspaces a > where pg_partnum = 1048576 * a.dbsnum + 1 > and pg_next != 0 > into temp pp with no log > > Estimated Cost: 6 > Estimated # of Rows Returned: 90 > > 1) informix.sysdbstab: INDEX PATH > > (1) Index Keys: dbsnum (Key-Only) > Lower Index Filter: informix.sysdbstab.dbsnum > > 0 > > 2) informix.syspaghdr: INDEX PATH > > Filters: informix.syspaghdr.pg_next != 0 > > (1) Index Keys: pg_partnum pg_pagenum > Lower Index Filter: > informix.syspaghdr.pg_partnum = 1048576 * > informix.sysdbstab.dbsnum + 1 > > NESTED LOOP JOIN > > > QUERY: > ------ > select b.dbsname database, b.tabname table > from systabnames b, pp where partn = partnum > > Estimated Cost: 10 > Estimated # of Rows Returned: 10 > > 1) informix.pp: SEQUENTIAL SCAN (Serial, fragments: > ALL) > > 2) informix.b: INDEX PATH > > (1) Index Keys: partnum > Lower Index Filter: informix.b.partnum = > informix.pp.partn > NESTED LOOP JOIN > > > > 4. Here is the explain output where it is sleeping > forever > > > QUERY: > ------ > select pg_partnum + pg_pagenum - 1 partn > from syspaghdr, sysdbspaces a > where pg_partnum = 1048576 * a.dbsnum + 1 > and pg_next != 0 > into temp pp with no log > > Estimated Cost: 30 > Estimated # of Rows Returned: 4 > > 1) informix.syspaghdr: SEQUENTIAL SCAN > > Filters: informix.syspaghdr.pg_next != 0 > > 2) informix.sysdbstab: INDEX PATH > > Filters: informix.syspaghdr.pg_partnum = 1048576 * > > informix.sysdbstab.dbsnum + 1 > > (1) Index Keys: dbsnum (Key-Only) > Lower Index Filter: informix.sysdbstab.dbsnum > > 0 > NESTED LOOP JOIN > > > > Any ideas what I should be doing to make it run fast. > > Regards, > > Vineet > > > > > __________________________________ > Do you Yahoo!? > Check out the new Yahoo! Front Page. > www.yahoo.com > > > sending to informix-list