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 )
DBA authority required. Users without DBA authority must be granted EXECUTE permission to run the stored procedure.
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-22 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 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 ( '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 |
