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 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). Table 10-14 lists the sp_iqestjoin parameters.
Name |
Data type |
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). |
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 |
