sql - Cast date to int4 -

i using netezza , have 2 separate tables join date. in first table, date stored type "date" (e.g., 2014-09-10) while in second table, date stored type "int4" (20140910). i've tried joining tables date:

select *  table1 inner join table2 b       on date(a.start_date) = to_date(b.start_date, 'yyyymmdd') 

this runs slow. it's been recommended me comparison might faster if cast date in table1 int4 , compare int4's. however, couldn't find way or if best way.

here query:

select *  table1 inner join      table2 b       on date(a.start_date) = to_date(b.start_date, 'yyyymmdd'); 

in general, databases have hard time joins on different types of columns or on joins functions. reason twofold: function make hard (or impossible) use indexes. statistics on columns of different types incompatible.

however, if move functions 1 side, engine might able something. instance:

select *  table1 inner join      table2 b       on b.start_date = cast(to_char(a.start_date, 'yyyymmdd') int); 

this conversion on 1 side. if have index on b.start_date, then can scan table1 , corresponding values in index. so, version might run faster, not if tables used same types.