The following table identifies those differences:
IDS node name | Common node name | Description |
---|---|---|
AUTOINDEX PATH | SIX | A temporary index is created. You can usually improve performance by creating an index for the table. |
DYNAMIC HASH JOIN | HSJOIN | A hash join is used on the preceding tables. The output includes a list of the filters that is used to join the tables. If DYNAMIC HASH JOIN is followed by (BUILD OUTER), the build phase occurs on the first table. Otherwise, the build occurs on the second table. Hash joins generally have a higher cost to retrieve the first row than nested-loop joins do. However, total query time is sometimes faster if the database server uses a hash join. |
FRAGMENTATION | PARTITION | The data server separates one data stream into multiple data streams. |
INDEX PATH | IXSCAN, MIXSCAN, or VIRTIX | Scans one or more indexes. VII index keys might be used if they are available. |
NESTED LOOP | NLJOIN | A join that scans the inner table once (usually with an index scan) for each row of the outer table. |
QUERYBLOCK | QB | A subquery that is a SELECT, INSERT, UPDATE, DELETE, UNION, or UNION ALL. |
ROWIDSCAN | DFETCH | The physical location of one or more rows is directly read. |
SEQUENTIAL SCAN | TBSCAN | Rows are read in sequence. |
SORT MERGE JOIN | MSJOIN | A join used when no index is available on the join columns of the joining tables. The database server first applies filters that might exist, and then sorts the rows for each table on the join column. |
UNION ALL | UNIONA | A union of the results from two SELECT statements to form a single result table that might contain duplicate rows. |
VTI TABLE | VIRTTB | A virtual table, which is not a general work file. |