sp_iqestjoin procedure

Function

Estimates the space needed to create join indexes for the tables you specify.

Syntax

sp_iqestjointable1_name, table1_row_#, table2_name,
table2_row_#, relation, iq_page_size )

Permissions

DBA authority required. Users without DBA authority must be granted EXECUTE permission to run the stored procedure.

Description

Returns the amount of space a join index uses based on the tables being joined. This procedure assumes that the database was created with the default block size for the specified IQ page size (or else the estimate is incorrect).

If you specify unqualified table names, then ensure that you are the owner of the tables being joined. If you are not the table owner, then provide a qualified table name for each table, such as 'owner.tablename'.

Table 7-20 lists the sp_iqestjoin parameters.

Table 7-20: sp_iqestjoin parameters

Name

Datatype

Description

table1_name

char(256)

Name of the first table in the join.

table1_row_#

int

Number of rows in the first table that participates in the join.

table2_name

char(256)

Name of the second table in the join.

table2_row_#

int

Number of rows in the second table that participates in the join.

relation

char(9)

Type of join, which can be “one>>many” or “one>>one” (do not leave any spaces between the words and the operator). The default is “one>>many”.

iq_page_size

smallint

The page size defined for the IQ segment of the database (must be a power of 2 between 1024 and 524288; the default is 131072).

Example

call sp_iqestjoin ( 'Customers', 1500000, 'SalesOrders', 15000000, 'one>>many', 65536 )

Cases

Indexsize

Create time

Msg

Table1:Customers

Rows: 1500000

Columns:

8

Width:

223

Table2: SalesOrders

Rows: 15000000

Columns:

9

Width:

134

IQpagesize:

65536

Min Case

48001024

3h0m/CPU

Max Case

95449088

9h6m/CPU

Avg Case

70496256

5h53m/CPU