Jenny Zhang wrote: > > The manual explains how join is executed in sapdb. > http://www.sapdb.org/htmhelp/00/37fe3b34334a7be10000000a114084 > /frameset.htm > > In summary, sapdb creates temporary results table for the first join > table(sorted by the join columns) and link the temporary > table with the > second join table. > > But the temporary table does not always show up in the > execution plan. > For example, the execution plan for query: > sql_execute explain select o_orderkey, c_name from > orders,customer where > o_custkey=c_custkey and o_orderkey=100 > > 'DBT';'ORDERS';'';'EQUAL CONDITION FOR KEY COLUMN';' > 31581';'';'';'';'' > '';'';'O_ORDERKEY';' (USED KEY COLUMN)';'';'';'';'';'' > 'DBT';'CUSTOMER';'C_CUSTKEY';'JOIN VIA KEY COLUMN';' > 4520';'';'';'';'' > 'DBT';'';'';' RESULT IS COPIED , COSTVALUE IS';' > 2';'';'';'';' > > There is no temporary table created. I have o_orderkey and > c_custkey as > primary key for order and customer. I am guessing if the > join column is > primary key, then no temp table is created? > > Also for the execution plan: > 'DBT';'SUPPLIER';'';'TABLE SCAN';' 480';'';'';'';'' > 'DBT';'NATION';'N_NATIONKEY';'JOIN VIA KEY COLUMN';' > 192';'';'';'';'' > 'INTERNAL';'TEMPORARY RESULT';'';'EQUAL CONDITION FOR KEY > COLUMN';' 500';'';'';'';'' > '';'';'N_NATIONKEY';' (USED KEY COLUMN)';'';'';'';'';'' > 'DBT';'PARTSUPP';'';'TABLE SCAN';' 211';'';'';'';'' > 'INTERNAL';'TEMPORARY RESULT';'';'EQUAL CONDITION FOR KEY > COLUMN';' 500';'';'';'';'' > '';'';'PS_PARTKEY';' (USED KEY COLUMN)';'';'';'';'';'' > 'DBT';'PART';'';'TABLE SCAN';' 5025';'';'';'';'' > 'DBT';'LINEITEM';'';'NO STRATEGY NOW (ONLY AT EXECUTION TIME)';' > 155270';'';'';'';'' > 'INTERNAL';'TEMPORARY RESULT';'';'TABLE SCAN';' 500';'';'';'';'' > 'DBT';'';'';' RESULT IS COPIED , COSTVALUE IS';' > 167700';'';'';'';'' > > Does it mean that the first temp table is created for table 'nation'? > or it is created for something else? >
The temporary join results are never shown by the explain command but they always exists in every join strategy also in key equal joins. 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. HTH. Kind regards, Holger SAP Labs Berlin _______________________________________________ sapdb.general mailing list [EMAIL PROTECTED] http://listserv.sap.com/mailman/listinfo/sapdb.general
