Re: Performance or space problem

Alex Schonlinner wrote:

> 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

Look into partitioning by range or list.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)