Re: Huge difference between sqlplus and sqldeveloper - sorting in memory vs disk

From: Sidney Chen <huanshengchen_at_gmail.com>
Date: Tue, 15 Nov 2011 23:42:27 +0800
Message-ID: <CAM_ddu96DLO4=qk4hMTZxJpg8YuuUEMLy_wwRn35ffhijAFkeA_at_mail.gmail.com>



Denis
rule of thumb, always avoid implicit type conversion.

it's dangerous when implicit conversion happen. 1. incorrect result, as this example show. 2. performance penalty, if the conversion happen on the index key, the index can't be used and bad execution will be probably generated.

you can check the nls_date_parametes in both tools by select value from NLS_SESSION_PARAMETERS where parameter='NLS_DATE_FORMAT';

in this case, if you simply change the format from 'yyyy-mm-dd HH24:mi:ss' to 'yyyy-mm-dd', the sql will fail. so avoid such random result at the first place.

oe_at_CS10G> create table t (m_when date, padding varchar2(100));

Table created.

oe_at_CS10G>
oe_at_CS10G> insert into t values(to_date('13-NOV-2011','dd-MON-yyyy'), lpad('x',100,'x'));

1 row created.

oe_at_CS10G> commit;

Commit complete.

oe_at_CS10G> alter session set nls_date_format='yyyy-mm-dd HH24:mi:ss';

Session altered.

oe_at_CS10G> select m_when from t where m_when between '14-Sep-2011' and
'14-Nov-2011';

no rows selected

oe_at_CS10G> alter session set nls_date_format='yyyy-mm-dd';

Session altered.

oe_at_CS10G> select m_when from t where m_when between '14-Sep-2011' and
'14-Nov-2011';

select m_when from t where m_when between '14-Sep-2011' and '14-Nov-2011'

                                          *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
-- 
Regards
Sidney Chen


--
http://www.freelists.org/webpage/oracle-l
Received on Tue Nov 15 2011 - 09:42:27 CST

Original text of this message