Hi all, I want to copy data from 1 table to another, after some processing. The source table has 4 million reocrds and after the processing, it gets reduced to 1 million and I want to put them in the destination table. I tried using BULK COLLECT - FORALL and also COPY command, they are all slow and I hence believe that the bottleneck is in the filtering clause. Here is the query and table structure:

select util.entry, port.eqp_id, port.port_id,
util.router, util.if_name, util.inbitssec, util.outbitssec
from bam_util util, tg_eqp_slot_port port, tg_eqp eqp
where ltrim(rtrim(upper(util.router))) = ltrim(rtrim(upper(eqp.router_name))) and
eqp.eqp_id = port.eqp_id and
ltrim(rtrim(upper(util.if_name))) = ltrim(rtrim(upper(port.interfacename)));


BAM_UTIL :
Name Null? Type
----------------------------------------- -------- ----------------------------
ENTRY NOT NULL DATE
ROUTER NOT NULL VARCHAR2(30)
IF_NAME NOT NULL VARCHAR2(30)
INBITSSEC NUMBER(38)
OUTBITSSEC NUMBER(38)
INOCTETS NUMBER
OUTOCTETS NUMBER

This is the source table, having 4 million entries. I have 2 indexes on this table, one on the following columns

ENTRY ASC,
ROUTER ASC,
IF_NAME ASC,
INBITSSEC ASC,
OUTBITSSEC ASC

and another primary constraint on
ENTRY , ROUTER and IF_NAME .
TG_EQP_SLOT_PORT has around 20,000 records with one unique index on port_id.
TG_EQP has 800 records with one unique index on eqp_id.

Also, the tables are in NOLOGGING option and I have disabled all the constraints/indexes on the destination table.
Do I have to use any of those PARALLEL / special big rollback segment features ?
What else do I need to add to make this query faster. Is filtering the real culprit here or am I overlooking something ? Any insight is greatly appreciated !!

I am using Oracle 8.1.7 on Solaris.

Shiva.

[Edited by Shiva on 05-09-2002 at 11:43 AM]