Re: Bulk DML question ...

write a c program and interface using oci.

<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.
Received on Wed Nov 01 2000 - 19:47:55 CST