Re: 11g query taking 24 seconds, same query was instant in 9i.

From: gs <gs_at_gs.com>
Date: Wed, 26 May 2010 20:06:14 GMT
Message-ID: <WafLn.5034$z%6.2843_at_edtnps83>



ddf wrote:
> Comments embedded.
>
> On May 26, 1:35 pm, gs <g..._at_gs.com> wrote:
>> <snip>
>>
>> thanks...
>>
>> stat's are fresh on 11g (I'll have to check the 9i, since its an old
>> test instance) - just reran the query again, both return 30 rows, and
>> data is the same. 9i this time took .98 and 11g took 10.73. 9i sorts by
>> date field by default

>
> No, it doesn't, that's a happy accident of the SORT GROUP BY used in
> 9iR2 and earlier releases. You should not rely on 'default'
> behaviour as it can change with no apparent notice. Since 10gR1 the
> group by operation migrated to a HASH GROUP BY mechanism, and the
> results do have a default order, by hash key, which doesn't match up
> to any column in your output. Had you coded ORDER BY .... in the
> original application query you wouldn't be hearing user complaints of
> 'the data isn't sorted any more'.
>

  I had already passed that along to the developer when I noticed there was no order by clause in it. I was just curious as to why, now I know.

thanks Received on Wed May 26 2010 - 15:06:14 CDT

Original text of this message