Reloading a table with a lot of datas

[#1]

Hello every one I am trying to load a table with more than 20 Millions of rows via an ODBC connector.

The ODBC can't support this volumetric so I set filters for one day,like my script bellow:

F_CALL_INVOICE:

LOAD CALL_DATE,

date(CALL_DATE) as CALL_DATE_Day,

    DESTINATION,

    DURATION,

    ACCOUNT_ID,

    INVOICE_ID,

    PRODUCT_ID,

    TIMETOANSWER,

    STATUS;

   SQL SELECT

  CALL_DATE,

    DESTINATION,

     DURATION,

     STATUS,

     ACCOUNT_ID,

     PRODUCT_ID,

    INVOICE_ID,

   TIMETOANSWER

FROM REPORT."F_CALL_INVOICE"

where  (TO_CHAR(CALL_DATE,'DD/MM/YYYY')) ='01/09/2015';

The load  arrived to 380 000 rows and nothing is happening since 30'.

Could someone give me some advices to load these kind of table?

thank you

[#2]

Hi Like I said to jontydkpi, the query is running correctly when I had a filter on  the INVOICE_ID like this

"where  (TO_CHAR(CALL_DATE,'DD/MM/YYYY')) ='01/09/2015' and INVOIC_ID='43201569';"

[#3]

Hi,

No there are no join I have tried to load only this table It's all my script.

[rubenmarin]

Jonathan said to test the query in another app (toad, Sql server), this same query, without filtering by invoice_id. This way if still doesn't works in a dedicated app you can discard some qv or odbc problem.

If the problem exist on another app I'll check the indexes as Colin saids.

[cwolf · Accepted solution]

You should never use an expression like:

... where convert2external(field) = 'something'

in this case the database can't use any index.

Always use

... where field = convert2internal('something')

In your case:

... where CALL_DATE = TO_DATE('01/09/2015' ,'DD/MM/YYYY')

[#6]

Thank you so much it's working Christian. I still have another question. If I want to do this where clause for september, what will be the expression?

[cwolf]

where CALL_DATE >= TO_DATE('01/09/2015' ,'DD/MM/YYYY') and CALL_DATE <= TO_DATE('30/09/2015' ,'DD/MM/YYYY')

[#8]

Thanks a lot Christian

have a nice day

[cwolf]

I am glad if I could help.

But, I just see you use:

LOAD CALL_DATE,

date(CALL_DATE) as CALL_DATE_Day,

So, it seems your CALL_DATE field is in Oracle a timestamp. If it is so, you have to use

where CALL_DATE >= TO_DATE('01/09/2015' ,'DD/MM/YYYY') and CALL_DATE < TO_DATE('01/10/2015' ,'DD/MM/YYYY')

to get the full month. This work correct for date fields and timestamps.