sp_iqjoinindex procedure

Function

Displays information about join indexes.

Syntax

sp_iqjoinindex [ left-table-name ], [ left-column-name ], [ left-table-owner ], [ right-table-name ], [ right-column-name ], [ right-table-owner ]

Permissions

None required.

Usage

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. Note that 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.

Table 10-28: sp_iqjoinindex usage examples

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

See also

In Chapter 9, “System Tables”: “SYSIQJOININDEX system table”, “SYSIQJOINIXTABLE system table”, “SYSIQJOINIXCOLUMN system table”

CREATE JOIN INDEX statement

Chapter 6, “Using Sybase IQ Indexes” in the Sybase IQ System Administration Guide

Description

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:

Table 10-29: sp_iqjoinindex 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:

  • NATURAL: a natural join

  • KEY: a key join

  • ON: a left outer/right outer/full join

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

Examples

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 remarks
t1_t2_t3_join  DBA     t1              DBA              n1 
=         t2               DBA               n1                NATURAL 
Y    (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 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    (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 remarks
t1_t2_t3_join  DBA     t2              DBA              name 
=         t3               DBA               name              NATURAL 
Y    (NULL)