This content has been marked as final. Show 7 replies
If you are licensed to use parallelism can you try this: (I know this would be strongly opposed by other posters as Oracle knows the best and hinting is overriding it but I just want this to be given a try)
Note: Again that 8 number can vary based on your init ora prmts.
SELECT /*+ PARALLEL(t,8)*/ u.location, COUNT (tweet_id) tweet_count FROM tweet_msg_fact t INNER JOIN user_profile u ON t.user_profile_sur_key = u.user_profile_sur_key GROUP BY u.location;
Edited by: Added Note.
I don't see any indications that your query needs tuning. You are retrieving a large number of rows non-selectively, and according to the plan it only costs you 1 I/O per row. It's a good deal, take it.
P.S. It's not a good idea to name your tables such that they look like Oracle data dictionary views (USER_PROFILE).
975028 wrote:As Nikolay said, you are joining all the rows of one table with all the rows of another. It will take some time.
i have set the p.k & f.k as you said but it is still taking 65.71 sec.
Although PK and FK are good to have anyway, they will not help in this case.
As another poster suggested, parallelism may help but be careful it does not eat the resources on your
Another suggestion is to use a materialized view to hold your count by location.
BTW: please use international English on this forum, 'lacs' is a local term. Please use thousands/millions etc.
Edited by: Paul Horth on Dec 5, 2012 7:54 AM
Edited by: Paul Horth on Dec 5, 2012 8:31 AM
Edited by: Paul Horth on Dec 5, 2012 8:32 AM
Interesting to see how Oracle rewrite the query. Wondering why not use nested loop when the outer source uses a lot temp space and the inner table has very selective index on the join key (almost unique).
With that said, it is worth to test it out. Including column u.location with key in the index also helps.
select /*+ ordered use_nl(u) */ u.location, sum(v.cnt) from (select t.user_profile_sur_key, count(*) cnt from tweet_msg_fact t group by t.user_profile_sur_key) v inner join user_profile u on v.user_profile_sur_key=u.user_profile_sur_key group by u.location ;