What is TO_CHAR()?
http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html
Maybe you need the DATE() and CURRENT_DATE() functions?
You have:
Select COUNT(DISTINCT T1.CIC_COU_CODE) P_CIC_COUNT_NUMB_D,
T1.CIC_ACCO_NUMB,
TO_CHAR(T1.CIC_DATE, 'DD/MM/RRRR') P_CIC_DATE
FROM CARD_INDICATOR_COUNTRY T1, CARD_INDICATOR_COUNTRY T2
WHERE TO_CHAR(T2.CIC_LAST_DATE,'DD/MM/RRRR') =
TO_CHAR(sysdate, 'DD/MM/RRRR')
And T2.CIC_BAN_CODE=1
AND T1.CIC_ACCO_NUMB= T2.CIC_ACCO_NUMB
AND TO_CHAR(T1.CIC_DATE, 'DD/MM/RRRR') =
TO_CHAR(T2.CIC_DATE, 'DD/MM/RRRR')
AND T1.CIC_FLAG_AUTH_TRAN='A'
GROUP BY T1.CIC_ACCO_NUMB,
TO_CHAR(T1.CIC_DATE, 'DD/MM/RRRR')
HAVING COUNT(DISTINCT T1.CIC_COU_CODE)>=2;
Do not hide indexed columns in function calls; the optimizer cannot see them. Example: Instead of
WHERE DATE(T2.CIC_LAST_DATE) = CURRENT_DATE()
do
WHERE T2.CIC_LAST_DATE >= CURRENT_DATE()
AND T2.CIC_LAST_DATE < CURRENT_DATE() + INTERVAL 1 DAY
To assist in analyzing slow SELECTs, please provide
* SHOW CREATE TABLE tbl\G -- engine, indexes
* SHOW TABLE STATUS LIKE 'tbl'\G -- sizes
* EXPLAIN SELECT ...\G -- clues of inefficiencies
* SHOW VARIABLES LIKE '%buffer%'; -- cache size
and surround them with [ code ] and [ / code ]
How much RAM do you have?
The other red flag is doing a GROUP BY after a JOIN. (But I don't understand the query well enough yet to have a suggestion.)