Re: Huge difference between sqlplus and sqldeveloper - sorting in memory vs disk
- From: Sidney Chen <huanshengchen@xxxxxxxxx>
- To: denis.sun@xxxxxxxxx
- Date: Tue, 15 Nov 2011 23:42:27 +0800
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@CS10G> create table t (m_when date, padding varchar2(100));
Table created.
oe@CS10G>
oe@CS10G> insert into t values(to_date('13-NOV-2011','dd-MON-yyyy'),
lpad('x',100,'x'));
1 row created.
oe@CS10G> commit;
Commit complete.
oe@CS10G> alter session set nls_date_format='yyyy-mm-dd HH24:mi:ss';
Session altered.
oe@CS10G> select m_when from t where m_when between '14-Sep-2011' and
'14-Nov-2011';
no rows selected
oe@CS10G> alter session set nls_date_format='yyyy-mm-dd';
Session altered.
oe@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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
