0

I recently converted some "Date" columns to "Timestamp(6) with local time zone" in order to use oracle localization functionality, without modify existing enterprise applications.

Everything works as expected: client sessiontimezone has an impact on the "hours" of data.

My problem is that the ORACLE Cost-Based Optimizer looks to not understand the date format we use anymore, and compute a wrong cardinality. Can you help us?

To reproduce the problem:

ALTER SESSION  SET TIME_ZONE = dbtimezone ;

create table TEST_CBO_TSWLT as (
select 
level as pk,
to_date('01/01/2000','DD/MM/YYYY') + (level/24) as col_date,
to_date('01/01/2000','DD/MM/YYYY') + (level/24) as col_ts_wlt
from dual
connect by level < ((365.25)*24)*10
);

create index TEST_CBO_TSWLT_INDX_DATE on TEST_CBO_TSWLT (col_date);
create index TEST_CBO_TSWLT_INDX_TSWLT on TEST_CBO_TSWLT (col_ts_wlt);

ALTER TABLE TEST_CBO_TSWLT  MODIFY (COL_TS_WLT TIMESTAMP(6) with local time zone );

execute DBMS_STATS.GATHER_TABLE_STATS (OWNNAME =>'MYSCHEMA',TABNAME =>'TEST_CBO_TSWLT');

Then, I copy database NLS parameters to my session in order to simplify the understanding.

select * from nls_session_parameters 
where parameter in ('NLS_DATE_FORMAT','NLS_TIMESTAMP_FORMAT','NLS_TIMESTAMP_TZ_FORMAT');
-->NLS_DATE_FORMAT  DD/MM/RR HH24:MI:SS
-->NLS_TIMESTAMP_FORMAT DD/MM/RR HH24:MI:SSXFF
-->NLS_TIMESTAMP_TZ_FORMAT  DD/MM/RR HH24:MI:SSXFF TZR

ALTER SESSION  SET NLS_DATE_FORMAT = 'DD/MM/RR HH24:MI:SS' ;
ALTER SESSION  SET NLS_TIMESTAMP_FORMAT =  'DD/MM/RR HH24:MI:SSXFF' ;
ALTER SESSION  SET NLS_TIMESTAMP_TZ_FORMAT =  'DD/MM/RR HH24:MI:SSXFF TZR' ;

Test_1 is ok

select * from TEST_CBO_TSWLT
where col_date >= '01/01/02 00:00:00'
and col_date < '02/01/02 00:00:00';
--CBO compute a cardinality of 24
--24 rows returned

Test_2 is ko

select * from TEST_CBO_TSWLT
where col_ts_wlt>= '01/01/02 00:00:00'
and col_ts_wlt< '02/01/02 00:00:00';
--CBO compute a cardinality of 219
--24 rows returned

Test_3 is ko

select * from TEST_CBO_TSWLT
where col_ts_wlt>= '01/01/02 00:00:00,000000000'
and col_ts_wlt< '02/01/02 00:00:00,000000000';
--CBO compute a cardinality of 219
--24 rows returned

Which FORMAT do I have to use to have correct cardinality on CBO?

PS: the query tools I use (Business Objects) is not able to specify functions like "to_date" or "cast" on right sides of comparisons, that's why I have to manage characters.

Jason Aller
  • 3,541
  • 28
  • 38
  • 38
T.TAV
  • 1
  • 1
    Why are you using text literals `'01/01/02 00:00:00,000000000'` and relying on implicit conversion using the NLS settings rather than using a timestamp literal `TIMESTAMP '2002-01-01 00:00:00.000'`? Does Business Objects not support those either? – MT0 May 16 '18 at 12:51
  • Thank for your answer. Sql queries are generated by a business intelligence tool (Business Objects). The tool does not know if an object of type "DATE" is stored as oracle DATE or oracle TIMESTAMP WITH LOCAL TIMEZONE. BO will generate a unique kind of SQL, that why text literals have to be similar. – T.TAV May 16 '18 at 13:15

0 Answers0