Correlated Sub-queries: CSQ Flattening

QUERY:

update orders set ship_charge = 0

where exists (

select "X" from customer c

where c.customer_num = orders.customer_num

and c.state = "MD” )

1) informix.c: SEQUENTIAL SCAN

Filters: informix.c.state = 'MD'

2) informix.orders: INDEX PATH

(1) Index Keys: customer_num

Lower Index Filter:

orders.customer_num = c.customer_num

NESTED LOOP JOIN

An index could be created on state to avoid the sequential scan.

Previous slide

Next slide

Back to first slide

View graphic version