Voltar
-
Título
Is it possible to combine optimization and SQL statement to get explain plan? -
Descrição
Is it possible to put optimization and SQL together and get the explain plan? i.e.
"set current query optimization = 7;
select count(*) from table_a chr
inner join V_tab1 chi on chr.CHIMP_ID = chi.ID
inner join V_tab2 ch on chi.CHARG_ID = ch.ID
where CHRG_col like '%test%'; " -
Causa
The set current query optimization statement is not taken into account in the Toad SQL editor when the Access Path on the SQL Editor Explain Plan tab is generated -
Resolução
WORKAROUND 1:If you have Toad DB2 Xpert edition, you use the SQL Optimizer.Select the SET statement and the SELECT statement and invoke the SQL optimizer (or run SQL Optimizer and put sql script into it).Click the "show plan" button in SQL Optimizer.Execution plan taking into account the "current query optimization" will be shown with no errors.WORKAROUND 2:If you do not have SQL Optimizer, for each statement in the Toad SQL Editor a separate ‘explain plan n’ tab will open within the ‘Explain Plan’ tab - see the example in the work_around.docx. An error in ‘Explain Plan 1’ tab is expected because "set ..." is not explainable statement. In the ‘Explain Plan 2’ tab there is an explain plan for the select statement.STATUS:Enhancement Request TDB-2654 has been implemented in Toad for DB2 version 6.5. The latest version can be downloaded here. -
ID do defeito
TDB-2654 -
Anexos