in

Description

Identifies the location of a table that is specified in a subquery or view.

Syntax

( in ( [ subq subquery_id | view view_name ] )
)

Parameters

subq subquery_id

is an integer identifying a subquery. In abstract plans, subquery numbering is based on the order of the leading open parentheses for the subqueries in a query.

view view_name 

is the name of a view. The specification of database and owner name in the abstract plan must match the usage in the query in order for plan association to be performed.

Examples

Example 1

create view v1 as 
select * from t1

select * from v1

( t_scan ( table t1 ( in ( view v1 ) ) ) )

Identifies the view in which table t1 is used.

Example 2

select * 
from t2 
where c21 
in (select c12 from t1)

( g_join 
    ( t_scan t2 ) 
    ( t_scan ( table t1 ( in ( subq 1 ) ) ) ) 
) 

Identifies the scan of table t1 in subquery 1.

Example 3

create view v9    
as 
select *
from t1
where c11 in (select c21 from t2)

create view v10
as
select * from v9
where c11 in (select c11 from v9)

select * from v10, t3
where c11 in 
        (select c11 from v10 where c12 = t3.c31)

( g_join 
( t_scan t3 ) 
( i_scan i_c21 ( table t2 ( in ( subq 1 ) ( view v9 ) ( view v10 )))) 
( i_scan i_c11 ( table t1 ( in ( view v9 ) ( view v10 )))) 
( i_scan i_c11 ( table t1 ( in ( view v9 ) ( view v10 ) ( subq 1 )))) 
( i_scan i_c11 ( table t1 ( in ( view v9 ) ( subq 1 ) ( view v10 )))) 
( i_scan i_c21 ( table t2 ( in ( subq 1 ) ( view v9 ) ( subq 1 ) ( view v10 )))) 
( i_scan i_c11 ( table t1 ( in ( view v9 ) ( subq 1 ) ( view v10 ) ( subq 1 )))) 
( i_scan i_c21 ( table t2 ( in ( subq 1 ) ( view v9 ) ( view v10 ) ( subq 1 )))) 
( i_scan i_c21 ( table t2(in( subq 1 )( view v9 )( subq 1 )( view v10 ) ( subq 1)))) 
) 

An example of multiple nesting of views and subqueries.

Usage

See also

nested, subq, table, view