Solved

how to improve performance when using "group by" ?

Posted on 2007-12-04
6
1,976 Views
Last Modified: 2013-12-19
the below query never returns (atleast for 3 minutes), there are about 1.2 million rows on which the above  pl/sql is grouping by.
actual std_deatails contains about 25 million rows.
 
std_id, std_name, std_key, std_cat (all have indexes on them).
 
should I add an composite index for the key (std_key & std_cat) to improve this query? how can I improve the performance of
the above query ?


 
select std_id,std_name, std_key||'.'||std_cat as std_cat_key, fees_cat from (
select  std_id, std_name, std_key, std_cat, sum(fees) as fees_cat
from std_details_f 
  inner join cat_users on std_details.category_id = cat_users.category_id 
  where cat_users.auth_user = 'testuser'
group by std_id, std_name, std_key , std_cat) A

Open in new window

0
Comment
Question by:tech_question
  • 3
  • 3
6 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 20403767
composite index on auth_user and category_id for the cat_users table.

index on category_id for std_details
0
 

Author Comment

by:tech_question
ID: 20406551
I am sorry category_id index on std_details was already present. I did add a composite key for auth_user and category_id for cat_users , still not getting the results in 3 minutes or less.
0
 

Author Comment

by:tech_question
ID: 20424826
one more important thing is that the table std_details_f is partitioned based on fiscal_key. does this make any difference on the query ?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 74

Expert Comment

by:sdstuber
ID: 20424865
no, syntax wise it makes no difference.

performance wise, it could,  you can get parallel threads searching each partition, which may help you.  But your partition key isn't part of your query so you won't get any partition pruning.

In the grand schema of things, no, it probably won't make much of a visible difference.
0
 

Author Comment

by:tech_question
ID: 20424876
should I do an ETL do get the group by results into one more table and run the ETL every night so that I can query this table directly. Our team is disinclined to use Materialized views. So I guess the only option left is an ETL or are there any other options ?
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 20429739
"ETL do get the group by results into one more table and run the ETL every night so that I can query this table directly."

That's a materialzed view.  Maybe you're not using Oracle's built in functionality but that's what it is.

Is cat_users acting as a filter or as a lookup in this query?

Which columns are coming from which table?

0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question