When the hash will involve in explain plan?

Hi,

We runt he following question against both QA and Prod, but the sorting of
the data is different even though both server's sorting and table/index
structure are the same. (Region table -- clusterind:Region_number; Bank
table -- clusterind:BankID; Fileset table -- clisterind:filesetID)

select Region.*, bank.*, FileSet.*
from Region left join Bank on region.Region_Number = bank.Region_Number
left join FileSet on FileSet.bankID = Bank.bankID
where Region.Region_Number <> '00000'

In QA, the return record sorting is by Region.Region_number, In prod, the
sorting is by fileset.filesetid.

After examing the explain plan against QA and PROD, one step is different.
QA -- Nested Loop/Left join fileset on ..
Prod -- Hash Match/Left join fileset on ..

Not sure why QA and PROD use the different explain plan? Looks like
different explain plan will have different return record sorting. If I add
"order by region.region_number" in prod question, then the explain plan will
be the same as QA.

Please advise why this situation happen? And, in what situation, the Hash
Match will be used? Thank you.
--
Best Regards,

Lynn

Dejan,

thank you for your response. The statistics is all updated very recent. In
fact, PROD server is more up-to-date than QA.

--
Best Regards,

Lynn


"Lynn" wrote:

> Hi,
>
> We runt he following question against both QA and Prod, but the sorting of
> the data is different even though both server's sorting and table/index
> structure are the same. (Region table -- clusterind:Region_number; Bank
> table -- clusterind:BankID; Fileset table -- clisterind:filesetID)
>
> select Region.*, bank.*, FileSet.*
> from Region left join Bank on region.Region_Number = bank.Region_Number
> left join FileSet on FileSet.bankID = Bank.bankID
> where Region.Region_Number <> '00000'
>
> In QA, the return record sorting is by Region.Region_number, In prod, the
> sorting is by fileset.filesetid.
>
> After examing the explain plan against QA and PROD, one step is different.
> QA -- Nested Loop/Left join fileset on ..
> Prod -- Hash Match/Left join fileset on ..
>
> Not sure why QA and PROD use the different explain plan? Looks like
> different explain plan will have different return record sorting. If I add
> "order by region.region_number" in prod question, then the explain plan will
> be the same as QA.
>
> Please advise why this situation happen? And, in what situation, the Hash
> Match will be used? Thank you.
> --
> Best Regards,
>
> Lynn