Displays information about join indexes.
sp_iqjoinindex [ left-table-name ], [ left-column-name ], [ left-table-owner ], [ right-table-name ], [ right-column-name ], [ right-table-owner ]
DBA authority required. Users without DBA authority must be granted EXECUTE permission to run the stored procedure.
left-table-name The name of the table that forms the left side of the join operation.
left-column-name The name of the column that is part of the left side of the join.
left-table-owner The owner of the table that forms the left side of the join operation.
right-table-name The name of the table that forms the right side of the join operation.
right-column-name The name of the column that is part of the right side of the join.
right-table-owner The owner of the table that forms the right side of the join operation.
The sp_iqjoinindex procedure can be invoked without any parameters. If no parameters are specified, sp_iqjoinindex displays information about all join indexes on IQ base tables. Join index tables are always IQ base tables. Join index tables cannot be temporary tables, remote tables, or proxy tables.
If you do not specify any of the first five parameters, but
specify the next parameter in the sequence, you must substitute
NULL for the omitted parameters. For example, sp_iqjoinindex
NULL, NULL, NULL, t2, n2, DB’ and sp_iqjoinindex
t1, NULL, NULL, t2.
Syntax |
Output |
|---|---|
sp_iqjoinindex |
Displays information about all the join indexes |
sp_iqjoinindex t1, NULL, DBA |
Displays information about all join indexes in which t1 owned by DBA forms the left side of the operation |
sp_iqjoinindex t2, n1, DBA |
Displays join index information with column n1 of table t2 owned by DBA as left side of the join |
sp_iqjoinindex NULL, NULL, DBA, NULL, NULL, DBA |
Displays information about all join indexes in which the left and right side tables are owned by DBA |
sp_iqjoinindex NULL, NULL, NULL, t2, NULL, NULL |
Displays information about all join indexes in which the table t2 is on the right side of the join operation |
sp_iqjoinindex t1, n1, DBA, t2, n1, DBA |
Displays information about join indexes in which the left side is column n1 of table t1 owned by DBA and the right side is column n1 of table t2 owned by DBA |
sp_iqjoinindex non_existing_table |
No rows returned, as the table non_existing_table does not exist |
sp_iqjoinindex NULL, NULL, non_existing_user |
No rows returned, as the user non_existing_user does not exist |
CREATE JOIN INDEX statement in Reference: Statements and Options
Chapter 6, “Using Sybase IQ Indexes” in the System Administration Guide: Volume 1
The sp_iqjoinindex stored procedure displays information about join indexes in a database. If you specify one or more parameters, the result is filtered by the specified parameters. For example, if left-table-name is specified, sp_iqjoinindex displays all the join indexes in which that table forms the left side of the join. If left-table-owner is specified, sp_iqjoinindex only returns join indexes in which the left table is owned by the specified owner. If no parameters are specified, sp_iqjoinindex displays information about all join indexes in the database.
The sp_iqjoinindex procedure returns information in the following columns:
Column name |
Description |
|---|---|
joinindex_name |
The name of the join index. |
creator |
The owner of the join index. |
left_table_name |
The name of the table that forms the left side of the join operation. |
left_table_owner |
The name of the owner of the table that forms the left side of the join operation. |
left_column_name |
The name of the column that is part of the left side of the join. |
join_type |
The only currently supported value is “=”. |
right_table_name |
The name of the table that forms the right side of the join operation. |
right_table_owner |
The name of the owner of the table that forms the right side of the join operation. |
right_column_name |
The name of the column that is part of the right side of the join. |
key_type |
Defines the type of join on the keys:
|
valid |
Indicates whether this join index needs to be synchronized. ‘Y’ means that it does not require synchronization; ‘N’ means that it does require synchronization. |
remarks |
A comment string. |
dbspace_id |
Name of the dbspace in which specified join indexes reside. |
Displays information about the join index in which table t1 forms the left side of the join operation:
sp_iqjoinindex t1 joinnidex_name creator left_table_name left_table_owner left_column_name join_type right_table_name right_table_owner right_column_name key_type valid dbspace_id remarks t1_t2_t3_join DBA t1 DBA n1 = t2 DBA n1 NATURAL Y 16387 (NULL)
Displays information about the join index in which table t2 forms the left side of the join operation:
sp_iqjoinindex t2 joinnidex_name creator left_table_name left_table_owner left_column_name join_type right_table_name right_table_owner right_column_name key_type valid dbspace_id remarks t1_t2_t3_join DBA t2 DBA n1 = t3 DBA n1 NATURAL Y (NULL) t1_t2_t3_join DBA t2 DBA name = t3 DBA name NATURAL Y 16387 ((NULL)
Displays information about join indexes in which the left side is column name of table t2 owned by DBA and the right side is column name of table t3 owned by DBA:
sp_iqjoinindex t2, name, DBA, t3, name, DBA joinindex_name creator left_table_name left_table_owner left_column_name join_type right_table_name right_table_owner right_column_name key_type valid dbspace_id remarks t1_t2_t3_join DBA t2 DBA name = t3 DBA name NATURAL Y 16387 ((NULL)
