Cluster index scan with different cost

Question

from the below, the cluster index scan was on the same table, but why one cost is 99 and another one is 0 ?

Answer 1

If you want a good factual answer, you need to post the full plan, preferably in XML format.

But I would guess that the optimizer estimates that the Nested Loop join above filters out most of the rows from the spool operator, so that there will be fewer scans. Or the operator to the left that you did not include is a hash/merge join, so that scan is only done once. Or that table is simply a lot smaller.

Answer 2

but why one cost is 99 and another one is 0 ?

May be because the one table is large and the other very small? How many rows do each table have?

Answer 3

Hi @sakuraime ,

a nested loops join compares each row from one table (known as the outer table) to each row from the other table (known as the inner table) looking for rows that satisfy the join predicate. It will scan the table many times, this may cause many cost. Please refer to Nested Loops Join.

Best regards,
Cathy

---

If the response is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.