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

dexterward@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@x.washington.edu
(replace 'x' with 'u' to respond)