-
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]
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|