Estimates the space needed to create join indexes for the tables you specify.
sp_iqestjoin ( table1_name, table1_row_#, table2_name, table2_row_#, relation, iq_page_size )
Returns the amount of space a join index will use 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 will be incorrect). Table 9-10 lists the 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 will participate 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 will participate 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). |
call sp_iqestjoin ( ‘customer’, 1500000, ‘orders’, 15000000, ‘one>>many’, 65536 )
Cases |
Indexsize |
Create time |
Msg |
|---|---|---|---|
Table1:customer |
|||
Rows: 1500000 |
|||
Columns: |
|||
8 |
|||
Width: |
|||
223 |
|||
Table2: orders |
|||
Rows: 15000000 |
|||
Columns: |
|||
9 |
|||
Width: |
|||
134 |
|||
IQpagesize: |
|||
65536 |
|||
Min Case |
48001024 |
3h0m/CPU |
|
Max Case |
95449088 |
9h6m/CPU |
|
Avg Case |
70496256 |
5h53m/CPU |
