Performance or space problem

Hi,

Currently we have a very large table (60GB) which has been completely denormalized 5 years ago due to performance problems (Oracle 8.0.4 (!)).

That means, it contains data on city basis and the complete geographical

hierarchy is in the base table, i.e. columns like
SRC_CONTINENT,SRC_SUBCONTINENT,SRC_COUNTRY,SRC_SUBCOUNTY,SRC_CITY,
DST_CONTINENT,DST_SUBCONTINENT,DST_COUNTRY,DST_SUBCOUNTY,DST_CITY,BOOKINGS.

That makes the table huge as you can imagine, but fast when doing queries.

I tried to normalize the table by creating the geographical dimension and only keeping the src_city, dst_city and the booking column in the base table. But when querying by just joining we need about 10-20 times as long as before, which is unacceptable.

We now want to switch to a newer Oracle version (10 or 11). I'm now thinking of using several function-based bitmap indexes: For example f_cont(city VARCHAR2) will return the continent from the given city. The function should do this by executing a select against the geographical dimension table.

Is this possible? Or must such a function be of type RNDS?

What other possibilities do we have? Can we speed up a join in any other way?

We need a way to keep the performance while decreasing space requirements (due to cost reduction).

Best regards,
  Alex Received on Tue Mar 22 2005 - 02:35:36 CST