Re: design for query performances on large tables

dexterward_at_despammed.com wrote:

> Hi all,
>
> SCENARIO:
> - oracle 9i RAC installation on linux RedHat, with partitioning enabled
>
>
> the system has to correlate records coming from table in sqlserver and
> from other 2 in oracle db.
> the tables records are generated at a very fast rate, around 1M
> records/day
> they are coming from text files, imported to the DB
> to handle complexity, range partitioning is used in oracle, but is
> unavailable in sqlserver.
>
> the records are going to be correlated via primary key, which is the
> same in a 1-1 relationship
> a set of queryies with group function are going to be runned nightly on
> the correlated data
>
> the solution we are projecting are the following:
>
> 1) import the SQLserver records in Oracle, too , and correlate using a
> view and optimize it.
> that woud be the best, but info duplication and tuning activities are
> required
>
> 2) write a service that create a table with the correlated info in
> sqlserver, so joins are not required
> and performances would be less demanding
>
> which would be the best approach in tour opinion?
> any help would be appreciated
> vl

Bringing the SQL Server records into Oracle is definitely the best possible idea. Some of what you will next want to do involves functionality not available in SQL Server.

I'd forget the view ... at least based on what you wrote ... as it seems to provide no real value: Maybe in SQL Server but in Oracle I don't see the point.

Forget the SQL Server nonsense, sorry but it really is nonsense, creating another table. Create the appropriate constraints and indexes and just query it.

As long as this is RAC, though, I would strongly urge you to look at turning those inevitable SQL Server surrogate key identities into a REVERSE KEY index in Oracle to minimize block contention across the interconnect.

For more information on REVERSE KEY indexes go to http://tahiti.oracle.com and http://www.psoug.org where you should click on Morgan's Library and then on Indexes.

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