SQL & PL/SQL

Hello from Germany (Frankfurt) !


I am working on Oracle 9.2.0.8.0
and have to solve following problem:
Comparison of a date and char fields.
Now two ways to do it.
Either I compare char with char and convert date to char,
or I compare date with date and convert char to date.
Now the performace question. Which operation takes more effort for the database?
So why not to try and to see the results?

First create table:

CREATE TABLE TEST (
char_date VARCHAR2(8),
real_date DATE
)
NOLOGGING;

Then insert 1.000.000 rows

BEGIN

for x in 1..1000000
loop
insert into test (char_date, real_date) VALUES('19990101', TO_DATE('2006.01.01', 'YYYY.MM.DD'));
end loop;

COMMIT;

END;

Collect statistics

EXEC dbms_stats.gather_table_stats('TESTER', 'TEST');

Now run some selects for date to char conversion:

Elapsed: 00:00:00.00
SQL> select * from test t where TO_DATE(char_date, 'YYYYMMDD') > real_date;

no rows selected

Elapsed: 00:00:03.02
SQL> select * from test t where TO_DATE(char_date, 'YYYYMMDD') > real_date;

no rows selected

And some selects for char to date conversion:


Elapsed: 00:00:03.02
SQL> select * from test t where char_date > TO_CHAR(real_date, 'YYYYMMDD');

no rows selected

Elapsed: 00:00:02.05
SQL> select * from test t where char_date > TO_CHAR(real_date, 'YYYYMMDD');

no rows selected

Elapsed: 00:00:02.05
SQL>

As you see when I compare char with char and convert date to char it seems to be faster (almost 1 second)

Is the test correct?

I still not sure, what gets better performance...

Any idea?

Thanks!