| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Join Performance in Materialized View
I have a join creating a materialized view. I have been using use_hash hint
and parallelism to tune part of the query (join that involves 2 large
tables). Thanks for the info. on setting hash_area_size explictly it made a
big difference.
I am using Direct IO and - Wow! at least 40% perf. increase.
Here is the rub .....I am somewhat limited in what I can do if I want to use refresh fast in MV creation.
I have a number of small codetables to join into the 2 large tables doing a hash join. Also, I am using outerjoins and hitting the same codetables with aliases more than once.
I created all small tables as IOT which is part of outer join predicate per suggestion from another colleague and friend.
This is for a 16 cpu server with 20Gb rams. It is on the largest table with 9M rows so the results in the MV will be 9M rows. Just the two tables took 33 minutes adding these smaller tables results in time taking about 2 hours...hmmm.
Like,
create materialized view....
partition....
select /*+USE_HASE(crt) PARALLEL(crt,8)*/
Adding these small table joins along with the hint above causes the CBO to want to do hash outer joins on everything which subsequently results in writing to temporary tablespace every setting sort_area_size, sort_area_retained_size and hash_area_size very high. I have allocated a special temporary tablespace with large extent sizes and LMT.
Since my sort_area is so large it would be silly to set temporary tablespace as a multiple of it but what are the implications of it being smaller.
Doing a lot of I/O waits on temporary tablespace even yet just by adding these small tables.
Seems I would be better off joining the large tables then using nested materialized view to join the rest but how deep can I get with these MV's or is there another way I can accomplish getting the result set for the first two large tables then the smaller tables by using hints (ordered or use_merge). Inline views are not supported in refresh options of MV.
Any ideas?
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: Paula_Stankus_at_doh.state.fl.us
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists --------------------------------------------------------------------To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Wed Jun 12 2002 - 19:18:25 CDT
 |
 |