sql - Non-clustered join performs better than clustered -

i ran following query on clustered , non-clustered , observed results of trace , found non-clustered performed better clustered, when expecting query run on clustered perform better , wondering why non-clustered performed better clustered? clustered contained 2 tables , clustered index on column cid

non-clustered join query trace:

non-clustered join query explain plan:

clustered join query trace:

clustered join query explain plan:

create cluster sql:

create cluster booking_customer (cid number(16)) pctused 85 pctfree 5 size 5k; 

a cluster intended optimising nested loop joins, physically co-locating relevant rows in tables joined. therefore excellent @ retrieving small quantities of joined data, oltp system might require.

when equi-joining 2 complete tables, hash join efficient because cost doesn't exceed sum of table scans of 2 tables (as long join doesn't spill disk, in case equi-partitining can help), , full scan efficient way of reading entirety 2 tables.

the hash join inefficient on tables in cluster because 2 full table scans have 2 full cluster scans, , cluster more twice size of both tables added (due space wastage).

so hash join on 2 tables in cluster may less efficient nested loop join between them, , less efficient if tables stored in conventional non-clustered tables.