Jenny Zhang wrote: > For the following query: > sql_execute explain select ps_suppkey from partsupp where > ps_partkey in > (select p_partkey from part where p_name like 'rose%') > > The execution plan is: > 'DBT';'PART';'P_NAME';'RANGE CONDITION FOR INDEXED COLUMN';' > 1376';'*';'';'';'' > 'DBT';'PARTSUPP';'';'NO STRATEGY NOW (ONLY AT EXECUTION TIME)';' > 211';'';'';'';'' > 'DBT';'';'';' RESULT IS COPIED , COSTVALUE IS';' > 239';'';'';'';'' > > if it means that the subquery is executed first, then join with > ps_suppkey, why there is no temporary table here? > > Or if it means doing a nested loop join between ps_suppkey and > p_partkey, why there is no join strategy?
The above execution plan means that the subquery is executed via index strategy. The strategy for the main select couldn't be determined because at explain time the subquery result doesn't exists and so the optimizer couldn't decide if a access via ps_partkey would be the best choice. > > I think your second execution plan belongs to an select with > > subquery, from selects and/or set functions. > > Those temporary results are shown by the explain command. > > > It is a query with correlated subquery. How sapdb handles correlated > query? I have to stop my query after it executing for two > days. All my > tables are in data cache, and the task state is always running. I am > wondering what it is doing...I'd be happy to post more details. Could you please post or mail me the query and table structure like index and primary keys? How correlations in SAP DB are executed (in short): 1. main select without correlated subquery predicate 2. for every row in this select execute the subquery 3. evaluate qualification of the main select with subquery predicate BTW there is no query rewrite in SAP DB. So a correlation will never executed as join even if it would be possible and better. Regards, Holger SAP Labs Berlin _______________________________________________ sapdb.general mailing list [EMAIL PROTECTED] http://listserv.sap.com/mailman/listinfo/sapdb.general
