| Use of Index for group by caluse [message #21694] |
Fri, 23 August 2002 03:18  |
Dev
Messages: 28
Registered: May 2001
|
Junior Member |
|
|
Hi All,
I am using query
"select account_no,count(*) from
where
account_no in(select custid from
) and
inactive_dt is NULL
group by account_no" in a cusror in PL/SQL code. The table
has index on account_no field but still it does not uses any index while running the query.
Both tables are going for TABLE SCANs . How can I optimize the same. Does oracle ignores indexing for group by functions.
Tell me wherer can I get the information regarding the same form net. (Any Documnet etc..for tunning performances of query)
Regards,
Deven.
|
|
|
|
| Re: Use of Index for group by caluse [message #21703 is a reply to message #21694] |
Fri, 23 August 2002 09:44  |
andrew again
Messages: 2577
Registered: March 2000
|
Senior Member |
|
|
Just because it's not using an index doesn't mean it's not using the most efficient access path.
Assuming you are using the cost based optimizer - the first thing to do is to analyze the tables involved to collect stats. If that doesn't make it use the index - try adding hints to get CBO to use the index(es). If things don't improve the maybe the CBO does have the best access path. There is no algorithm which prevents the use of the indexes simply because you have a group by clause. Enable not null constraints on your table columns where you can. This can impact CBO plan.
You could also try re-writing the query as a join between the two tables.
|
|
|
|
| Re: Use of Index for group by caluse [message #21717 is a reply to message #21694] |
Sun, 25 August 2002 07:49 |
Su
Messages: 154
Registered: April 2002
|
Senior Member |
|
|
The NULL check is preventing your query to use an index for the data search. When there is a NULL included in your WHERE clause, then ORACLE uses FULL TABLE SCAN. So remove NULL check and try it out.
For better and quicker result, use COUNT(0) rather than COUNT(*).
The IN operator may also be preventing the INDEX usage. As andrew said, you better try to JOIN the tables rather than using IN operator for range check.
Check it out.
Good luck :)
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
