| Optimization Required for this query [message #372116] |
Wed, 10 January 2001 18:35  |
B.Sivakumar
Messages: 2
Registered: January 2001
|
Junior Member |
|
|
Hi Oracle SQL-Experts,
I have the following query.
select distinct substr(sl_no,5,4), material_number
from usl_table
where material_number in (select distinct material_number from material_group where material_group in ('1201','1120','1102')
and substr(material_number,7,1)='-' and length(material_number) <= 16)
group by substr(sl_no,5,4), material_number;
Usl_table has 70 Million records.
I have executed this query in the morning still the satus show active for the past 9 hours.
Is usage of larger rollback segment specific to this
query will help, to get the results faster?
Can someone suggest what best we can do to optimize this?
Appreciate your assistance.
Regards
Siva/-
|
|
|
|
| Re: Optimization Required for this query [message #372117 is a reply to message #372116] |
Thu, 11 January 2001 03:22  |
John R
Messages: 156
Registered: March 2000
|
Senior Member |
|
|
A few options:
Why are you doing a distinct AND a group by? One or the other should give the same results
Check that the Material_Group table has an index on material_group
Check that usl_table has an index on material_number
Check that both these tables and their indexes have been analyzed recently
Try replacing the subquery with a 'Where exists' subquery.
If this doesn't help, consider building a temporary table containing the raw output of the query and performing the group by operation on this table. This can produce significant time savings at the cost of a lot of disk space.
If this doesn't help, could you post the explain plan results so we can see what's going on.
Hope this helps.
|
|
|
|
| Re: Optimization Required for this query [message #372118 is a reply to message #372116] |
Thu, 11 January 2001 03:24 |
John R
Messages: 156
Registered: March 2000
|
Senior Member |
|
|
A few options:
Why are you doing a distinct AND a group by? One or the other should give the same results
Check that the Material_Group table has an index on material_group
Check that usl_table has an index on material_number
Check that both these tables and their indexes have been analyzed recently
Try replacing the subquery with a 'Where exists' subquery.
If this doesn't help, consider building a temporary table containing the raw output of the query and performing the group by operation on this table. This can produce significant time savings at the cost of a lot of disk space.
If this doesn't help, could you post the explain plan results so we can see what's going on.
Hope this helps.
|
|
|
|
| Re: Optimization Required for this query [message #372119 is a reply to message #372116] |
Thu, 11 January 2001 03:30 |
John R
Messages: 156
Registered: March 2000
|
Senior Member |
|
|
A few options:
Why are you doing a distinct AND a group by? One or the other should give the same results
Check that the Material_Group table has an index on material_group
Check that usl_table has an index on material_number
Check that both these tables and their indexes have been analyzed recently
Try replacing the subquery with a 'Where exists' subquery.
If this doesn't help, consider building a temporary table containing the raw output of the query and performing the group by operation on this table. This can produce significant time savings at the cost of a lot of disk space.
If this doesn't help, could you post the explain plan results so we can see what's going on.
Hope this helps.
|
|
|
|
| Re: Optimization Required for this query [message #372120 is a reply to message #372116] |
Thu, 11 January 2001 03:33 |
John R
Messages: 156
Registered: March 2000
|
Senior Member |
|
|
A few options:
Why are you doing a distinct AND a group by? One or the other should give the same results
Check that the Material_Group table has an index on material_group
Check that usl_table has an index on material_number
Check that both these tables and their indexes have been analyzed recently
Try replacing the subquery with a 'Where exists' subquery.
If this doesn't help, consider building a temporary table containing the raw output of the query and performing the group by operation on this table. This can produce significant time savings at the cost of a lot of disk space.
If this doesn't help, could you post the explain plan results so we can see what's going on.
Hope this helps.
|
|
|
|
| Re: Optimization Required for this query [message #372121 is a reply to message #372116] |
Thu, 11 January 2001 07:19 |
me
Messages: 66
Registered: August 2000
|
Member |
|
|
As John R suggested check that you have all the required indexes and and try using a where exist statement. If that does not help try this query:
select distinct substr(sl_no,5,4), material_number
from usl_table a,
(select distinct material_number
from material_group
where material_group in ('1201','1120','1102')
and substr(material_number,7,1)='-'
and length(material_number) <= 16) b
where a.material_number = b.material_number
|
|
|
|
| Re: Optimization Required for this query [message #372124 is a reply to message #372120] |
Thu, 11 January 2001 12:22 |
B.Sivakumar
Messages: 2
Registered: January 2001
|
Junior Member |
|
|
Hi John,
Thanks for the suggestion.
I am currently using 8.0.6. Do we have function based indexes on this. My worry is the indexing of sl_no may not yield anything (though its already available), as it uses function as
substr(sl_no,5,4).
Appreciate your views.
Regards
Siva/-
|
|
|
|
| Re: Optimization Required for this query [message #372133 is a reply to message #372120] |
Fri, 12 January 2001 07:14 |
me
Messages: 66
Registered: August 2000
|
Member |
|
|
Adding a function based index on sl_no will not help since it is only used in the select clause. Not in the where clause.
The only indexes needed are:
"Check that the Material_Group table has an index on material_group
Check that usl_table has an index on material_number"
|
|
|
|
| Re: Optimization Required for this query [message #372148 is a reply to message #372120] |
Tue, 16 January 2001 06:41 |
John R
Messages: 156
Registered: March 2000
|
Senior Member |
|
|
Function based indexes are in 8.1
Firstly, the sl_no isn't used in any of the where clauses, so indexing it wouldn't achieve anything.
Secondly, as the Material_number field is directly referenced by the where clause (as well as being used in functions in the where clause), a normal index will do.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
