how to calculate additional extents from sysmaster

Hi,

I am writign a script to calculate additional extents in

a table. Meaning, if a table has 150 extents, how many more

extents can it take before it runs out of extents.

My colleague told me how to calculate using oncheck.

I found a way to get that figure using sysmaster.

The idea is to get the free space available in the

table partition info. From the free space available,

divide the figure by 8 to get the additional number

of extents possible.

This is the script:-

database sysmaster;

select partnum from systabnames where dbsname = "database_name"

and tabname = "table_name" into temp tmp_partnum with no log ;

select physaddr

from sysptntab

where sysptntab.partnum in

(select partnum from tmp_partnum)

into temp tmp_physaddr with no log ;

select min(pg_frcnt) pg_frcnt

from sysphyspaghdrs where pg_pagenum in

(select physaddr from tmp_physaddr) ;

If pg_frcnt returns 140, then 17 more extents are possible.

(140/8) = 17.

Two questions:-

1. This is trivial. The above 3 SQLs can be easily

combined into 1 SQL. However its performance sucks.

It took 5 mintutes to return the result using a single

SQL, as compared to 5 sec for the 3 SQL mentioned above.

Why such a big difference?

2. In the last SQL, I am joining pg_pagenum with

the physaddr of sysptntab. Actually it should be

pg_physaddr joining with sysptntab.physaddr. However

the performance sucks. It takes ages to get the result.

That is because sysphyspaghdrs is indexed on pg_partnum

+ pg_pagenum. sysphyspaghdrs is a view on syspaghdrs

with the view conditions set to pg_pagenum = 0.

Now in sysphypaghdrs, pg_pagenum = pg_physaddr in almost

all cases. That is why substituted pg_pagenum with pg_physaddr

and got the result instantly.

So my question is, will it always be true that in sysphyspaghdrs,

pg_pagenum = pg_physaddr.

TIA.

Ravi.