Solved

how to improve performance when using "group by" ?

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

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle sql query 7 59
Oracle - SQL Parse String 5 33
Where Does Time Value Come From for Database Insert or Update 4 26
Oracle collections 15 17
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

785 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