Re: Performance problem

Brad Odland wrote in article <3435E27F.F309EDCE_at_cpm.com>...

>One it sounds like you really have a datamodel problem...

Not necessarily. We have 43-million rows table with avg_row_len=240 and it's blazingly fast. (A little hint: it's not really a table but a partition view over 44 tables).

>WHy would you want
>to join a table twice with such a large table....?? I would assume you
>have..

small lookup tables are very useful. You don't want to store, say, "Degeneral Motors", "Moobma Industries", etc similar strings in a table with 43 million rows. Better have a small ( ~ 1000 rows ) lookup table with these strings and join it with big table when needed.

>1. Made sure the indexes for the tables in question are being used during
>your operation.

make sure there are NO indexes on a 112-row table. To scan such table directly
is surely faster than having hit indexes and than go and read the whole table
anyway.. just because it fits on just a couple blocks.

Also you might consider doing the following on 112-row table: ALTER TABLE YOUR_TABLE_NAME CACHE. Another things one would consider doing to make things faster:

Do everything possible to reduce AVG_ROW_LEN. Make as many fields as possible
NUMBER rather than VARCHAR. Avoid using CHAR on a fields > 3 characters in length.
Move all fields which have many NULLs to the bottom of the table. Consider storing NULLs instead of 0 (Zero) in numeric fields. You can greatly
reduce the AVG_ROW_LEN if you manage to group NULLs together at the bottom of the table.
Put your database on a raw devices. I went from filesystem to using raw logical volumes on RS6000 box and the improvement in perfomance was striking. Especially when TEMP tablespace and redo logs were placed on raw partitions.

>2. Parallel Query options are set for each table envolved in the query.

Parallel Query will go south once you start using indexes. At least in Oracle 7.3.

Better place the table on RAID.

And finally, if your table has AVG_ROW_LEN > 500, then indeed you have datamodel problem..

Cordially,
Sam Volba

>Some thoughts:
>YOu should built a secondary structure that contains the information that
 is
>accessed frequently...
>
>Hope it helps....
>
>nmk_1970_at_hotmail.com wrote:
>
>> Hi netters,
>>
>> I have a table which consists of 24 M Rows with the data size of
>> 8 GB which is spread across 8*1 GB each. I have a need to join
>> these table with a small table of 112 rows . The small table has
>> to be joined twice with the big table to achieve the task. when I do
>> this it is really sucks. Also I have to do group by with 10
>> columns on this query. I have temp segment size of 1.2 GB .
>> Currently I am running oracle 7.3.2.3 on sun solaris 2.5.
>> Even after 12 hours I am not able to get the output.
>>
>> Any suggestions are welcome.
>>
>> Thanks in advance.
>>
>> /muralikrishna
>>
>> -------------------==== Posted via Deja News ====-----------------------
>> http://www.dejanews.com/ Search, Read, Post to Usenet
>
>
>
Received on Sun Oct 05 1997 - 00:00:00 CDT