Oh yes... lenght() function would ensure you do Full Table Scan on the target table plus adding the overhead of a function call...

Actually, Oracle 7 is still alive and well 😀

Trace the query.

Compare execution plans, wait events.

First the good news...

Described scenario is a text book case of a single star-schema datamart with a single FACT table in the center surrounded by DIMension tables.

Now the not so...

Partitioning is a useful resource if properly done -if not it has the potential of becoming you worst nightmare.

Partitioning strategy should at least help in one of the cases below:

1)...

Hard to follow...

Do you really want to create partitions using pieces of different tables?

Imagine TableA and TableB are actually a piece of Genoa Salami and a piece of Pepperoni.

When...

Yes!

Here is how you do it.

1- Pre-create the database in your "target" server -so you make all decisions about storage.

2- Force-restore over the pre-created database -so datafiles go to the...

Look at "log shipping" -that's a SQL Server managed process that would do exactly what you are looking for.

Are you prefixing the named-instance properly?

Connection string for named instances should look like HOST_NAME\INSTANCE_NAME

Are jobs running T-SQL scripts? look at the code, specifically "use" statements.

Brad Allison (5/28/2009)

---

I need to create INSERT scripts to get data from one table to another totally different database on a different server. I understand the INSERT INTO statement,...

If query takes only 4 seconds on Oracle side I would say connectivity is the issue.

Here is how you can trace Oracle side of the query...

Set your environment... e.g. ORACLE_HOME...

:w00t: flashback!

Brilliant post!...

Shall I understand query takes 4 seconds on Oracle but 1 minute 45 seconds to return when over a link?

How much data does the query returns?

I personally don't like surrogate keys; when possible I prefer to use meaningful -natural keys.