Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

how to improve performance when using "group by" ?

Posted on 2007-12-04
6
Medium Priority
?
2,008 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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 1500 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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

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…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

704 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