Tom, thank you for your explanation. However this is a bit more tricky.

I have a Java client invoking a Stored Procedure (SP) using JCAPS. The SP receives a DATE parameter.

My NLS_DATE_FORMAT is RR.MM.DD

this SP in turns calls another SP that receives again a DATE parameter and does the following:

l_sql := to_char(p_date, 'YYYY-MM-DD');

when a client calls the main SP it gets the error:
ORA-01861: literal does not match format string

i confirmed that the error is hit when making the to_char() in the second SP.

the client is passing a java.sql.Date '1920-01-01'

i could only make the call work by putting this at the start of the:

execute immediate 'alter session set NLS_DATE_FORMAT=''RRRR-MM-DD''';

I can't find the logic behind this since the DATE type does not store any format like you said.

How come a DATE value passed by a Java client get into trouble when making a to_char ??

it is blowing my mind :|


1)
select to_char( to_date('01-01-1920', 'DD-MM-YYYY') , 'YYYY-MM-DD') from dual;

1920-01-01

2)
select to_char( to_date('01-01-1920', 'DD-MM-YYYY') , 'RRRR-MM-DD') from dual;

1920-01-01

3)
select to_char( to_date('01-01-1920', 'DD-MM-YYYY') , 'DD-MM-YYYY') from dual;

01-01-1920

4)
select to_char( to_date('1920-01-01', 'YYYY-MM-DD') , 'DD-MM-YYYY') from dual;

01-01-1920

5)
select to_char( to_date('1920-01-01', 'YYYY-MM-DD') , 'DD-MM-RRRR') from dual;

01-01-1920


but if i start making to_date(to_date())

1)
select to_date( to_date('1920-01-01', 'YYYY-MM-DD') , 'DD-MM-RRRR') from dual;

20-01-2001

2)
select to_date( to_date('1920-01-01', 'YYYY-MM-DD') , 'DD-MM-YYYY') from dual;

20-01-0001

what's the logic behind this? i can't seem to get it

Hi Tom,

the reason I am making to_date(to_date()) is to try to simulate the passing of a date type to the SP from the client.

this is because there is no problem whatsoever with my code in the SPs. if I make my tests in Sql*Plus it works as expected with no ORA-01861. i tried different date formats as input values.

but the client gets this error.

It is not the first time that this happens. another team has met a similar issue when receiving DATE arguments from a client in JAVA+JCAPS. the solution was to make in the SP a

to_date(date_param, 'YYYY-MM-DD')

where
date_param IN DATE

as strange as it sounds it worked.

in my case I am getting a somewhat different error because I get the error when doing

to_char(date_param, 'YYYY-MM-DD');

where
date_param IN DATE


the code is simply something like this:

create procedure sp2( p_date IN DATE )
is
l_sql varchar2(2000)
begin

(...)
l_sql := l_sql || to_char(p_date, 'YYYY-MM-DD');
(...)

end;

create procedure sp1( p_date IN DATE )
is
begin
(...)
sp2(p_date);
(...)

end;

i can only make this work with the client call if I apply this solution:

a)
at the beginning of SP1:
execute immediate 'alter session set NLS_DATE_FORMAT=''RRRR-MM-DD''';


the bogus thing is if I put this call to SP2:

sp2(to_date(p_date,'YYYY-MM-DD'));

the client gets:
ORA-01847: day of month must be between 1 and last day of month

given the submitted date '1920-01-01' I guess that in someway the date is being "read" in Oracle as DD-MM-YYYY.

if i call SP2 in the normal/initial form:

sp2(p_date);

the client gets:
ORA-01861: literal does not match format string

which is hit in SP2 with the instruction:
l_sql := l_sql || to_char(p_date, 'YYYY-MM-DD');


SO, i can't make any sense out of this. the only conclusion is that JCAPS has a bug in the way he sends DATEs to Oracle, but even then I can't see how Oracle gets confused with this parameter considering the tests I made above.

Tom, my apologies for this somewhat out-of-topic question but maybe it can help someone else that may be using JCAPS with Oracle.

I understood however your teachings:
1) a date does not have any format
2) doing to_date(to_date()) leads to implicit to_char() which can lead to some unexpected behaviors.
3) for best performance, in a query keep your date column intact and only mess with your input parameter:
where my_date_column = to_date(my_date_param)

create procedure sp2( p_date IN DATE )
is
  l_sql varchar2(2000)
begin 

  (...)
  l_sql := l_sql || to_char(p_date, 'YYYY-MM-DD');
  (...)

Tom, yes i know now the error I was making in trying to simulate the passing of a DATA parameter to my SP by making to_date(to_date()) and similar.

the client binds a DATE, or so it is supposed to when using JCAPS+Java. the SP receives a date. there are no to_char or to_date in between.

the date has no format, i get it. but the fact is this is not working. when I make the to_char(p_date) i get an ORA-01861. How can i get this error from a date parameter that has no format then?

yes, i know the to_char() will lead to a to_date() in execution time. in this case the situation is more complex than what i have shown and there is no time and human power to change this bit of the (bigger) query to use bind variables. trust me in this case.

the issue is i need to do a to_char() from a DATE variable and I can't without explicitly setting the DATE format on the session.

my intuition says that the problem is somehow related to the database date format being RR.MM.DD and the session format established by the client to be DD.MON.RR or something like that. this is why if i make a alter session the call runs fine. WHY this has a problem when using a DATE i don't know. it should be obvious but i don't get it.

Hi Tom,

Could you please let me know why does the following query default to 01-AUG-1998 00:00:00? Why August?

select  to_date( '1998', 'YYYY' ) from dual;

default year   = current year
default month  = current month
default day    = 1
default hour   = 0
default minute = 0
default second = 0


ops$tkyte%ORA10GR2> select sysdate, to_date( ' ', ' ' ) from dual;

SYSDATE              TO_DATE('','')
-------------------- --------------------
17-aug-2012 13:41:06 01-aug-2012 00:00:00

Tom,

Never mind my earlier question. It would default to 1st of the current month.

Thanks.

Hi Tom,

Being on-board with converting the parameter once and comparing it to the real dates, I'm curious about the following scenario.

I've run autotrace for each of the following queries:

select case when my_date = to_date('99991231','YYYYMMDD') then 'A' else 'B' end from my_table;
and
select case when to_char(my_date,'YYYYMMDD') = '99991231' then 'A' else 'B' end from my_table;

But it returns identical output:
7 recursive calls
52 db block gets
59 physical reads
139 redo size
351 bytes sent via SQL*Net to client
352 bytes received via SQL*Net from client
357 sorts (memory)
358 sorts (disk)

I would've expected to see a difference given the difference in where to_date() is. Or does it not matter because it will still do the conversion per row, regardless of whether it's the date or the constant?

Thank you as always.

ops$tkyte%ORA11GR2> declare
  2      l_date date := to_date('99991231','YYYYMMDD');
  3      l_str  varchar2(30) := '99991231';
  4  begin
  5      for x in (select case when created = l_date then 'A' else 'B' end
  6                  from big_table.big_table)
  7      loop
  8          null;
  9      end loop;
 10  
 11      for x in (select case when to_char(created,'YYYYMMDD') = l_str then 'A' else 'B' end
 12                  from big_table.big_table)
 13      loop
 14          null;
 15      end loop;
 16  end;
 17  /

PL/SQL procedure successfully completed.



SELECT CASE WHEN CREATED = :B1 THEN 'A' ELSE 'B' END FROM BIG_TABLE.BIG_TABLE

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch    10001      1.16       1.14      14529      24377          0     1000000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    10003      1.16       1.14      14529      24377          0     1000000
********************************************************************************
SELECT CASE WHEN TO_CHAR(CREATED,'YYYYMMDD') = :B1 THEN 'A' ELSE 'B' END FROM BIG_TABLE.BIG_TABLE

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch    10001      1.38       1.36      14529      24377          0     1000000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    10003      1.38       1.36      14529      24377          0     1000000

Hi Tom,

I've also run into some code that looks like this:

select ... from ... where my_date between
to_date(to_char(some_date,'YYYYMMDD') || '00:00:00','YYYYMMDD HH24:MI:SS') and
to_date(to_char(some_date,'YYYYMMDD') || '23:59:59','YYYYMMDD HH24:MI:SS')

Is this not an implicit conversion here? I would've thought it'd be better to do
where my_date >= some_date and my_date < some_date +1
(because, clearly, some_date doesn't have a time component), but the predicate information on explain plan seems to be the same in both cases. Am I missing something here?

Thanks as always.

Thanks Tom.
Didn't realise (for the first question) that it's CPU that would matter between the queries.

For the second one, when I said the dates had no time component, I meant that the data coming in does not provide a logical time (i.e., everything will be midnight in the dataase).

When you say the code could've done
"where my_date >= trunc(some_date,'d') and my_date < trunc(some_date+1,'d')"
would this not have a negative effect on partition pruning because of the truncate in front of the date?

Hi,

There is a certain code that we are using to convert a date from one timezone to another -

<u>Code Snippet 1-</u>
SELECT 
TO_DATE (
                TO_CHAR (
                         FROM_TZ (
                                  CAST (last_upd AS TIMESTAMP)
                                , 'PST'
                                 )
                            AT TIME ZONE ('EST')
                       , 'MM/DD/YYYY HH:MI:SS AM'
                        )
              , 'MM/DD/YYYY HH:MI:SS AM'
               )
          last_upd
  FROM <<table_name>> a
 WHERE <<conditions>>;

Now I tested the performance of the same code as-

<u> Code Snippet 2-</u>
SELECT  CAST(
                         FROM_TZ (
                                  CAST (last_upd AS TIMESTAMP)
                                , 'PST'
                                 )
                            AT TIME ZONE ('EST') AS DATE)
          last_upd
  FROM <<table_name>> a
 WHERE <<conditions>>;

The Code Snippet 2 seems to be working faster as compared to Code Snippet 1 almost always ( I did flush the Buffer_cache as well as Shared_Pool just to ensure that there are no other factors affecting the Query Performance).

Here is the AUtotrace output of the two Queries-
 
When Using CAST -->
Query -
SQL> insert into testp
  2  SELECT  CAST(
  3                           FROM_TZ (
  4                                    CAST (last_upd AS TIMESTAMP)
  5                                  , 'PST'
  6                                   )
  7                              AT TIME ZONE ('EST') AS DATE)
  8            last_upd
          FROM <<table_name>> a
         WHERE <<conditions>>;

2000135 rows created.

Elapsed: 00:01:14.54

Statistics
----------------------------------------------------------
       3187  recursive calls
      58417  db block gets
      15731  consistent gets
         54  physical reads
   38151784  redo size
        717  bytes sent via SQL*Net to client
       1080  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
         21  sorts (memory)
          0  sorts (disk)
    2000135  rows processed


When Using TO_DATE(TO_CHAR -->
Query-
SQL> insert into testp
  2  SELECT
  3  TO_DATE (
  4                  TO_CHAR (
  5                           FROM_TZ (
  6                                    CAST (last_upd AS TIMESTAMP)
  7                                  , 'PST'
  8                                   )
  9                              AT TIME ZONE ('EST')
 10                         , 'MM/DD/YYYY HH:MI:SS AM'
 11                          )
 12                , 'MM/DD/YYYY HH:MI:SS AM'
 13                 )
 14            last_upd
          FROM <<table_name>> a
         WHERE <<conditions>>;

2000135 rows created.

Elapsed: 00:01:22.25
Statistics
----------------------------------------------------------
       3106  recursive calls
      58448  db block gets
      15560  consistent gets
          2  physical reads
   38156380  redo size
        716  bytes sent via SQL*Net to client
       1235  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
         11  sorts (memory)
          0  sorts (disk)
    2000135  rows processed

Table is created simply as-
create table testp ( last date);


Database Version -
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production


Does this anyway denotes that in such a scenario CAST is performance wise better that "TO_DATE(TO_CHAR" or there is a better way to do this.

Thanks Tom!! I'll avoid flushing but while going through your article in Oracle Magazine I have a quick question - Do we have both the Shared pool as well as the Database Buffer cache cached by the OS?

Also we are facing an issue with the functions in SQL statements. When we use FROM_TZ (CAST (my_date AS TIMESTAMP), SOURCE_TIMEZONE)
AT TIME ZONE (TARGET_TIMEZONE) AS DATE)
directly in the Query it works almost twice as fast when called from a function. I understand that there will be a time lag but I was not expecting such a huge difference.

Code for same -

CREATE TABLE my_table_1
(
column_2 NUMBER NOT NULL,
column_1 VARCHAR2(250),
my_date_1 DATE,
my_date_2 DATE,
my_date_3 DATE,
my_date_4 DATE,
my_date_5 DATE,
my_date_6 DATE,
my_date_7 DATE NOT NULL);

ALTER TABLE my_table_1 ADD ( CONSTRAINT PK_MY_TABLE_1 PRIMARY KEY (COLUMN_2) USING INDEX PK_MY_TABLE_1);

CREATE TABLE my_table_2
(
column_2 NUMBER NOT NULL,
column_1 VARCHAR2(250),
my_date_1 DATE,
my_date_2 DATE,
my_date_3 DATE,
my_date_4 DATE,
my_date_5 DATE,
my_date_6 DATE,
my_date_7 DATE NOT NULL);

ALTER TABLE my_table_2 ADD ( CONSTRAINT PK_MY_TABLE_2 PRIMARY KEY (COLUMN_2) USING INDEX PK_MY_TABLE_2);

DECLARE
v_sys_dt DATE := TO_DATE('06-NOV-2012 00:00:00', 'DD-MON-YYYY HH24:MI:SS');
BEGIN

for i in 1..2000135
LOOP
insert into my_table_1
(column_1
,column_2
,my_date_1
,my_date_2
,my_date_3
,my_date_4
,my_date_5
,my_date_6
,my_date_7
)
VALUES
(i
,'LOOP'
,v_sys_dt + (1*i/86400)
,v_sys_dt + (2*i/86400)
,v_sys_dt + (3*i/86400)
,v_sys_dt + (1*i/86400)
,v_sys_dt + (2*i/86400)
,v_sys_dt + (3*i/86400)
,v_sys_dt + (1*i/86400)
);

--DBMS_OUTPUT.PUT_LINE(i);
END LOOP;
COMMIT;

END;
/

Function used:
CREATE OR REPLACE FUNCTION fn_timezone_conversion
(
p_date DATE,
p_source_timezone VARCHAR2,
p_target_timezone VARCHAR2
)
RETURN DATE
AS
v_date DATE := p_date;
v_new_date TIMESTAMP(0);
v_source_timezone VARCHAR2(100):= p_source_timezone;
v_target_timezone VARCHAR2(100):= p_target_timezone;
BEGIN
v_new_date := CAST(FROM_TZ(CAST(v_date AS TIMESTAMP),
v_source_timezone) AT TIME ZONE (v_target_timezone) AS DATE);
RETURN v_new_date;
END;
/

Function case 1:
CREATE OR REPLACE PROCEDURE my_procedure_case1 (
p_my_fetch_limit IN NUMBER DEFAULT 1000)
IS
TYPE column_1 IS TABLE OF my_table_1.column_1%TYPE;
TYPE column_2 IS TABLE OF my_table_1.column_2%TYPE;
TYPE my_date_1 IS TABLE OF my_table_1.my_date_1%TYPE;
TYPE my_date_2 IS TABLE OF my_table_1.my_date_2%TYPE;
TYPE my_date_3 IS TABLE OF my_table_1.my_date_3%TYPE;
TYPE my_date_4 IS TABLE OF my_table_1.my_date_4%TYPE;
TYPE my_date_5 IS TABLE OF my_table_1.my_date_5%TYPE;
TYPE my_date_6 IS TABLE OF my_table_1.my_date_6%TYPE;
TYPE my_date_7 IS TABLE OF my_table_1.my_date_7%TYPE;

v_column_1_array column_1;
v_column_2_array column_2;
v_my_date_1_array my_date_1;
v_my_date_2_array my_date_2;
v_my_date_3_array my_date_3;
v_my_date_4_array my_date_4;
v_my_date_5_array my_date_5;
v_my_date_6_array my_date_6;
v_my_date_7_array my_date_7;

CURSOR c_my_cursor
IS
SELECT column_1,
column_2,
CAST (FROM_TZ (CAST (my_date_1 AS TIMESTAMP), 'PST') AT TIME ZONE ('EST') AS DATE),
CAST (FROM_TZ (CAST (my_date_2 AS TIMESTAMP), 'PST') AT TIME ZONE ('EST') AS DATE),
CAST (FROM_TZ (CAST (my_date_3 AS TIMESTAMP), 'PST') AT TIME ZONE ('EST') AS DATE),
CAST (FROM_TZ (CAST (my_date_4 AS TIMESTAMP), 'PST') AT TIME ZONE ('EST') AS DATE),
CAST (FROM_TZ (CAST (my_date_5 AS TIMESTAMP), 'PST') AT TIME ZONE ('EST') AS DATE),
CAST (FROM_TZ (CAST (my_date_6 AS TIMESTAMP), 'PST') AT TIME ZONE ('EST') AS DATE),
CAST (FROM_TZ (CAST (my_date_7 AS TIMESTAMP), 'PST') AT TIME ZONE ('EST') AS DATE)
FROM my_table_1;
BEGIN
OPEN c_my_cursor;

LOOP
FETCH c_my_cursor
BULK COLLECT INTO v_column_1_array,
v_column_2_array,
v_my_date_1_array,
v_my_date_2_array,
v_my_date_3_array,
v_my_date_4_array,
v_my_date_5_array,
v_my_date_6_array,
v_my_date_7_array
LIMIT p_my_fetch_limit;

FORALL i IN 1 .. v_column_1_array.COUNT
INSERT INTO my_table_2 (column_1,
column_2,
my_date_1,
my_date_2,
my_date_3,
my_date_4,
my_date_5,
my_date_6,
my_date_7)
VALUES (v_column_1_array (i),
v_column_2_array (i),
v_my_date_1_array (i),
v_my_date_2_array (i),
v_my_date_3_array (i),
v_my_date_4_array (i),
v_my_date_5_array (i),
v_my_date_6_array (i),
v_my_date_7_array (i));

COMMIT;
EXIT WHEN c_my_cursor%NOTFOUND;
END LOOP;

COMMIT;

EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE('SQLERRM: ' || SQLERRM);

END my_procedure_case1;
/

CREATE OR REPLACE PROCEDURE my_procedure (
p_my_fetch_limit IN NUMBER DEFAULT 1000)
IS
TYPE column_1 IS TABLE OF my_table_1.column_1%TYPE;
TYPE column_2 IS TABLE OF my_table_1.column_2%TYPE;
TYPE my_date_1 IS TABLE OF my_table_1.my_date_1%TYPE;
TYPE my_date_2 IS TABLE OF my_table_1.my_date_2%TYPE;
TYPE my_date_3 IS TABLE OF my_table_1.my_date_3%TYPE;
TYPE my_date_4 IS TABLE OF my_table_1.my_date_4%TYPE;
TYPE my_date_5 IS TABLE OF my_table_1.my_date_5%TYPE;
TYPE my_date_6 IS TABLE OF my_table_1.my_date_6%TYPE;
TYPE my_date_7 IS TABLE OF my_table_1.my_date_7%TYPE;

v_column_1_array column_1;
v_column_2_array column_2;
v_my_date_1_array my_date_1;
v_my_date_2_array my_date_2;
v_my_date_3_array my_date_3;
v_my_date_4_array my_date_4;
v_my_date_5_array my_date_5;
v_my_date_6_array my_date_6;
v_my_date_7_array my_date_7;

CURSOR c_my_cursor
IS
SELECT column_1,
column_2,
fn_timezone_conversion (my_date_1),
fn_timezone_conversion (my_date_2),
fn_timezone_conversion (my_date_3),
fn_timezone_conversion (my_date_4),
fn_timezone_conversion (my_date_5),
fn_timezone_conversion (my_date_6),
fn_timezone_conversion (my_date_7)
FROM my_table_1
WHERE column_1 = 'DUMMY';
BEGIN
OPEN c_my_cursor;

LOOP
FETCH c_my_cursor
BULK COLLECT INTO v_column_1_array,
v_column_2_array,
v_my_date_1_array,
v_my_date_2_array,
v_my_date_3_array,
v_my_date_4_array,
v_my_date_5_array,
v_my_date_6_array,
v_my_date_7_array
LIMIT p_my_fetch_limit;

FORALL i IN 1 .. v_column_1_array.COUNT
INSERT INTO my_table_2 (column_1,
column_2,
my_date_1,
my_date_2,
my_date_3,
my_date_4,
my_date_5,
my_date_6,
my_date_7)
VALUES (v_column_1_array (i),
v_column_2_array (i),
v_my_date_1_array (i),
v_my_date_2_array (i),
v_my_date_3_array (i),
v_my_date_4_array (i),
v_my_date_5_array (i),
v_my_date_6_array (i),
v_my_date_7_array (i));

COMMIT;
EXIT WHEN c_my_cursor%NOTFOUND;
END LOOP;

COMMIT;
END my_procedure;
/

exec my_procedure;
exec my_procedure_case1;

Please can you help me understand why it is so above.

ops$tkyte%ORA11GR2> select to_date( '&1', 'mon-yyyy')+level-1
  2    from dual
  3  connect by level <= 15;

TO_DATE('OCT-1582','
--------------------
01-oct-1582 00:00:00
02-oct-1582 00:00:00
03-oct-1582 00:00:00
04-oct-1582 00:00:00
15-oct-1582 00:00:00
16-oct-1582 00:00:00
17-oct-1582 00:00:00
18-oct-1582 00:00:00
19-oct-1582 00:00:00
20-oct-1582 00:00:00
21-oct-1582 00:00:00
22-oct-1582 00:00:00
23-oct-1582 00:00:00
24-oct-1582 00:00:00
25-oct-1582 00:00:00

15 rows selected.

Thanks Tom!! I now understand - "the shared pool is just a memory structure, the OS in no way would cache it."

Regarding the second part -
1)We cannot use a direct Insert (actually DML error logging) because our architects believe that in certain environments rollback segment may not be large enough to accommodate all the data (we have a migration project and the code may run on Servers which may be inaccessible to us).
2)The code mentioned above is temporary - just to replicate the issue. The actual code has a certain amount of logic in between the fetch and the Inserts. While tuning - we found out that if we remove only the function call with the actual statement (CAST...) the time taken by the procedure is almost 25% (reduced by 75% ) of the original time. The existing logic is as follows -

DECLARE
Cursor to fetch rows ...
BEGIN
<<Initialize>>
LOOP
Bulk Fetch ……
Insert Into Main Table <<forall>>
Insert into Log table<<forall>>
If rowcount = 100K; Commit;
End loop;
End;


3)We have almost 7 date columns (I’m excluding the other columns) which we fetch through a DB Link; we are migrating the columns from one timezone to another using the function.
4)We are doing a commit every 500 records as of now. I plan to change that to 100K. I assume the consistency (in case the procedure fails mid-way and we have to rerun) is maintained using the log table. We simultaneously insert into a log table as we need the Primary keys of the existing & migrated records and in case of a failure, during rerun we check whether the record exists in the log table before trying to insert the record into the final table.
5)We are using a function and not the direct CAST(..) to avoid redundancies as the same conversion is used in almost all subprograms.

Here are the few problems I am facing–
a) Since we are fetching across DBLinks, the Optimizer is not able to guess the no. of rows correctly. At places the number of rows need to be 2Million and the Optimizer assumes them to be 5K. Is it due to the DB Link or I'm missing something?
b)I am also facing issues with Scalar Subquery Unnesting & Nesting. Even after all the tables being analyzed (using DBMS_STATS) there were cases wherein I had to add a Hint (e.g no_unnest) and the Query time was reduced from 10 mins to 45 secs; Or we had to use an Inline view so that a Scalar Subquery could be joined to the main tables.
c)I tried Append_Values hint but I suppose it does not works with Exception handling (Probably for the same reason Append does not works with constraints)

Hi Tom,

In continuation to the discussion of converting timezone using the FROM_TZ function, the time taken by the SQL with and without the timezone conversion for 87 Million rows is of the order of 38 minutes and 6 minutes respectively.

Since timezone conversion is a must for us, can you suggest if there is a workaround to bring down the processing time with timezone conversion.

Thanks,
Ankit

Thanks Tom for the prompt response.

We have a requirement to migrate billions of rows from tables RANGE partitioned on a date column and HASH subpartitioned on an ID column.
The source table has multiple(upto 8) date columns which need to be converted into a timezone different from source table timezone.
The source and target timezones can differ from table to table (but would remain the same for a table).

Each subpartition of the source table is expected to have approximately 200 Million rows.
Our current design (still under discussion) is as follows:
1. Disable constraints on the target table
2. Extract the Subpartition information of the source table and store it in a table
OR
2. Create chunks of the data from source table on basis of ROWID using DBMS_EXECUTE_PARALLEL
3. Loop through each subpartition / chunk and migrate data for each subpartition / chunk in source using "INSERT AS SELECT" with PARALLEL hint in SELECT statement.
4. Enable the constraints and resolve the discrepancies

In the test environment, we have ~87 Million rows in the source table with 108 Subpartitions.
In Step# 3, using Subpartition Approach, following are the Elapsed time results:
A> When timezone conversion is carried out on 5 columns in source table using: "CAST(FROM_TZ (CAST (date_column_1 AS TIMESTAMP), 'GMT') AT TIME ZONE ('EST') AS TIMESTAMP(0))", time taken by the process is: 38 minutes
B> When no timezone conversion is carried out, then the time taken by the process is: 6 minutes.

Each of the date columns can have different values. The FROM_TZ function that is getting executed 5 times for each row(in test table) seems to pose a huge overhead in our case.
Unfortunately, timezone conversion is a must meet requirement.

Data in each of the date columns in a row can have different values. This would mean that I would not be able to subtract/add a fixed number of hours to each date column of a row.
Also, as mentioned earlier, I have to do the timezone conversion for multiple tables, and source and target timezones can differ from table to table.

Right ... each row has few timestamp fields and each timestamp field within a row can have different values which means that the values in same row can fall within different Day Light Saving date range.

select cast('TIMESTAMP'' 2019-06-29 00:00:00''' as date) from dual;

Why / How does that work and successfully converts a VARCHAR2 to a DATE ?

Just curious, what format mask is being used to convert that string to a date?

For info, that string literal comes from HIGH_VALUE on USER_TAB_PARTITIONS which is being processed inside PL/SQL, so the LONG column is returned as a VARCHAR2. And it's for a table which is INTERVAL partitioned by day.

select cast(TIMESTAMP ' 2019-06-29 00:00:00' as date) from dual;

Thanks Connor.

The snippet of code came from code for converting system generated names for an INTERVAL partitioned table;

    for c_rec in (select partition_name, high_value
                  from   user_tab_partitions
                  where  table_name = 'TEST'
                  and    partition_name like 'SYS%'
                 )
    loop
      execute immediate 'select cast(:hv as date) from dual' into l_highvalue using c_rec.high_value;
      execute immediate 'alter table '||'TEST'||' rename partition '||c_rec.partition_name||' to '||'P_'||to_char(l_highvalue,'YYYYMMDD');                            
    end loop;

I was surprised when the "cast(:hv as date)" worked as I thought inside PL/SQL, the LONG column (HIGH_VALUE) was being treated as a VARCHAR2.

I used it instead of below to avoid hard parsing, and I was thinking if I was performing some dodgy conversion;

execute immediate 'select '||c_rec.highvalue||' from dual' into l_highvalue; 

SQL> create table t ( c long );

Table created.

SQL> insert into t values ('date ''2019-12-12''');

1 row created.

SQL> select * from t;

C
--------------------------------------------------------------------------------
date '2019-12-12'

1 row selected.

SQL>
SQL> set serveroutput on
SQL> declare
  2    v long;
  3    d date;
  4  begin
  5    select c into v from t;
  6    execute immediate 'select cast(:hv as date) from dual' into d using  v;
  7    dbms_output.put_line(d);
  8  end;
  9  /
12-DEC-19

PL/SQL procedure successfully completed.

SQL>
SQL> delete t;

1 row deleted.

SQL> insert into t values ('to_date(''12-NOV-2019'',''dd-mon-yyyy'')');

1 row created.

SQL> select * from t;

C
--------------------------------------------------------------------------------
to_date('12-NOV-2019','dd-mon-yyyy')

1 row selected.

SQL>
SQL> declare
  2    v long;
  3    d date;
  4  begin
  5    select c into v from t;
  6    execute immediate 'select cast(:hv as date) from dual' into d using  v;
  7    dbms_output.put_line(d);
  8  end;
  9  /
declare
*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at line 6


SQL>
SQL>

Hi Guys,
I’ve a column of timestamp type for unkown reason also has a FBI trunc(col_timestamp,’mi’). Then via a plsql code we are using a date var and comparing agains the column but no index gets used.
The query is something like:
select count(1) from tab where trunc(col_timestamp,’mi’) = trunc(:var_date,’mi’);
The problem is the query always does a full scan on That big table.

SQL> create table t as select * from v$active_session_history;

Table created.

SQL> create index IX on T ( trunc(sample_time,'MI'));

Index created.

SQL> explain plan for select count(1) from t where trunc(sample_time,'mi') = trunc(:var_date,'mi');

Explained.

SQL> @exp

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     9 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |      |     1 |     9 |            |          |
|*  2 |   INDEX RANGE SCAN| IX   |    52 |   468 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access(TRUNC(INTERNAL_FUNCTION("SAMPLE_TIME"),'fmmi')=TRUNC(TO_NU
              MBER(:VAR_DATE),TO_NUMBER('mi')))

15 rows selected.