Solved

how to improve performance when using "group by" ?

Posted on 2007-12-04
6
1,963 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 73

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 73

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 73

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
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 setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now