Hello, I generated explain plan for a simple query with a group by clause. I thought a SORT operator would show up in the explain plan because of group by clause but instead a TEMP operator is there. May I know the reason for this and which one if good.

Plan is as below
Thanks.

This query used an index to resolve the query, and indexes are already sorted. I don't see a group by.

db2udbgirl, and what is the problem? Total Cost is 0.386435. It shouldn't be a performance problem, is it?

Thanks for your comment. I do not face any performance problem but just trying to understand the explain plan.

Marcus, sorry I meant 'group by' clause by mistake instead of order by.

Next question: If TEMP operator appears on explain plan, does it imply that piped sort is happening internally? If not, how can I differentiate a piped/non-piped sort from explain plan.

Thanks again.

There is no sort. The data is accessed via the index, which is in the same order as the "order by" so no sort is needed.

ok, how can I identify whether a piped or non-piped sort is happening from explain plan ?

I am not 100% sure, but I think that DB2 sometimes decides that at execution time based on available memory.

If I remember correctly, then the plan indicates a piped sort in the detailed description to the nodes in the plan. For example, if you have a SORT operator (which you don't need here as Marcus_A explained), then look at the details of that SORT operator. It will tell you whether it is piped or not.

The exception would be when you encounter a sort overflow at runtime, which means the sort had to be materialized to a temp table.

The following plan uses a SORT operator but I do not see in explain plan any info about piped/non piped sort. Pls let me know if I'm missing something.

Original Statement:
------------------
select *
from smtumt.wmi_mapping
order by wmi desc
with ur

Plan
SORT Operation Details:

Since you don't do anything before/after the sort, I'm not very much surprised that you don't see any piping. You should add a UDF call or something else that consume the sorted data before it is returned to the client.

I generated the plan for this query
select TRANSACTION_ID, substr(VEH_IDENT_NBR,1,5), SRC_TXN_ID, SVC_LABR_OPRTN_CD, CAMPAIGN_NBR from card.transaction order by src_txn_id;

The plans remains the same but I see a line item named "SPILLED : (Pages spilled to bufferpool or disk)". Does this mean Non-piped sort is used ?