Conference orarep::nomahs::rdb_60

~Customer wants to know how to estimate the amount of SORTWORK space required
~on disk to build a sorted index with duplicates on a ~7-8 million row table
~(that will grow to 10 million).  Any ideas ?

The Rdb7b documentation contains a description of the "R" debug flag, you
might like to get the Tech Article on which the docs are based.  It will help
some in the estimation.
    
~    	Are there logicals other than RDMS$BIND_SORT_WORKFILES
~    	and RDMS$BIND_WORK_VM that should be used to minimize
~    	sorting overhead (especially on disk) ?

The logical RDMS$BIND_SORT_WORKFILES will only increase the sorting overhead,
it will use more work files...

The logical RDMS$BIND_WORK_VM is not used for sorting.

~Is there a way to determine exactly how many SORTWORK files it will use (I
~know it defaults to 2 and you can set it to 10, but it doesn't mean it will
~USE 10, right ?)
    
Yes it will use all the files you ask for, it may not utilize them all evenly
because of the ordering and nature of the data.

~I know the VMS sort used to have a simple algorithm to calculate the amount of
~virtual memory that would be consumed by a sort, which would have a bearing on
~this, but I'll be darned if I can find it at the moment.

Well did you try the SORT/MERGE manual?  Or maybe the SOR$ utility call
interface?

Ian

    
    	If I build an index with 10 million rows and it uses SORTWORK
    	files, would the same index built on only 1/10th the rows use
    	~1/10th as much SORTWORK disk space ?  1/25th ?  Is the
    	growth of SORTWORK space linear with respect to a growth in
    	rows ?  Can I build an index using 1/25th of the rows and
    	multiply by 25 to find out how much SORTWORK space the "full"
    	index would take ?
    
    	If growth is linear the debug flag "R" etc. might be very
    	interesting.
    
    	Note that the SORT/MERGE manual has been merged into the
    	VMS User's Manual and no longer exists.  The SOR$ documentation
    	is also not of much help.  Ironically the memory-usage information
    	could be found in the Rdb Guide to Performance and Tuning,
    	Volume 1 (p. 4-214 in the V7.0 manuals):  "The Sort Utility (SORT)... 
    	uses the difference between these two parameters [WSEXTENT and 
    	WSQUOTA] to allocate VM for scratch space." e.g. 
    	WSEXTENT - WSQUOTA = <allocated VM>.
    
    	Regards,
    	rcs
    

    
    Having just delivered the rdb/c course (v6.0) it does give a
    general calculation for sizing sortwork files for an index
    creation :
    
    vms blocks <= 1.4 * key size (bytes) x table cardinality / 512
    
    cheers,
    Steve

    
    Thank you very much, Steve !  Am I correct in assuming that the
    formula is executed as:
    
    vms blocks <= (1.4 * key size (bytes) ) x (table cardinality / 512)
    
    and NOT (1.4 * key size (bytes) x table cardinality) / 512 ?
    
    Were any other useful estimates in the same section ?  Exactly
    which course was that ?  It'd be nice to see that information
    in the Rdb documentation !
    
    I appreciate your help -- thanks !
    
    rcs
    

Richard,

>    vms blocks <= (1.4 * key size (bytes) ) x (table cardinality / 512)
>    
>    and NOT (1.4 * key size (bytes) x table cardinality) / 512 ?

Are these not equivalent?

G'day,

Mark.

    
    		; Are these not equivalent?
    
    	Gee, they are (blush) !  Maybe *I* was dropped on *my* head...
    
    	Thanks,
    	rcs

Richard,

>    	Gee, they are (blush) !  Maybe *I* was dropped on *my* head...

Coffee too strong? :-)

G'day,

Mark.