Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Re: Bulk DML question ...

Re: Bulk DML question ...

From: <sergey_s_at_my-deja.com>
Date: Thu, 02 Nov 2000 23:01:16 GMT
Message-ID: <8tsrnn$1t$1@nnrp1.deja.com>

> write a c program and interface using oci.
>

Why C and not PL/SQL?

> <sergey_s_at_my-deja.com> wrote in message news:8tqg61
 $15b$1_at_nnrp1.deja.com...
> > Let's say I have two tables:
> >
> > CREATE TABLE1
> > (
> > TIME_KEY NUMBER,
> > ID NUMBER,
> > NAME VARCHAR2(20),
> > AMOUNT NUMBER(8,2),
> > COUNT NUMBER,
> > ...
> > );
> >
> > CREATE TABLE2
> > (
> > ID NUMBER,
> > GROUP_CODE NUMBER,
> > ...
> > );
> >
> > TABLE1 has approx. 3 billion rows (almost 200 Gig.). TABLE2 has
 approx.
> > 3 million rows. TABLE1 is partitioned by TIME_KEY (partitions have
> > anywhere from 50 to 100 million rows each). I looked into making
> > partitions smaller based on additional criteria, but the next
 candidate
> > column has only about 500 unique values.
> >
> > Every row in TABLE1 has a match in TABLE2. In other words, I have no
> > choice but to do full table scans. I already looked into business
> > process, and there is no time or money to change it at this point.
> >
> > So, here's the query I need to run:
> >
> > CREATE TABLE TEMP_TABLE PARALLEL (DEGREE 8 INSTANCES 1) NOLOGGING AS
> > SELECT /*+ ORDERED FULL(A) FULL(B) USE_HASH(B) */
> > A.GROUP_CODE, B.NAME, SUM(B.AMOUNT), SUM(B.COUNT)
> > FROM TABLE2 A, TABLE1 B
> > WHERE A.ID = B.ID
> > GROUP BY A.GROUP_CODE, B.NAME;
> >
> > This query finishes in about 30 hours when the machine is pretty
 much
> > dedicated to my process (needless to say, it's too long for just the
> > first step). I am looking at PL/SQL and bulk DML, but I am not sure
 yet
> > how I could apply it in my case. Maybe, there are ways besides bulk
 DML
> > that I could try. If you have any suggestions/thoughts etc., please
 let
> > me know.
> >
> > I have Oracle 8.0.6.0.0 With Partitioning and Objects Options;
> > SunOS 5.7 on 8 processors (400 Mz each), 80 Gig Memory, RAID 5 (66
> > disks in total I think, but not sure).
> >
> > Here's some db settings:
> >
> > parallel_transaction_resource_timeout 300
> > shared_pool_size 209715200
> > shared_pool_reserved_size 37847040
> > shared_pool_reserved_min_alloc 5K
> > large_pool_size 0
> > large_pool_min_alloc 16K
> > disk_asynch_io TRUE
> > dbwr_io_slaves 0
> > lgwr_io_slaves 4
> > arch_io_slaves 0
> > db_file_direct_io_count 64
> > db_block_buffers 48000
> > db_block_checksum FALSE
> > db_block_size 16384
> > db_block_checkpoint_batch 8
> > db_block_lru_statistics FALSE
> > db_block_lru_extended_statistics 0
> > db_writer_processes 4
> > db_block_lru_latches 8
> > db_block_max_dirty_target 4294967294
> > db_file_simultaneous_writes 4
> > db_file_multiblock_read_count 64
> > mts_servers 1
> > mts_max_servers 10
> > mts_max_dispatchers 10
> > local_listener
> > mts_listener_address (ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY))
> > mts_multiple_listeners FALSE
> > sort_area_size 10485760
> > sort_area_retained_size 65536
> > sort_direct_writes TRUE
> > sort_write_buffers 4
> > sort_write_buffer_size 32768
> > sort_spacemap_size 512
> > sort_read_fac 5
> > parallel_min_percent 0
> > parallel_default_max_instances 0
> > parallel_min_servers 7
> > parallel_max_servers 32
> > parallel_server_idle_time 3
> > parallel_execution_message_size 2148
> > parallel_min_message_pool 225540
> > hash_join_enabled TRUE
> > hash_area_size 13107200
> > hash_multiblock_io_count 4
> >
> > Thank you very much!
> > Sergey
> >
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Nov 02 2000 - 17:01:16 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US