|
View previous topic :: View next topic
|
| Author |
Message |
vinay_care
New User
Joined: 22 Sep 2006
Posts: 17
|
|
|
|
Hi,
I have a query where in which its taking a longer CPU time, need to cut shot the execution time, the query goes like th's
CURSOR:
SELECT DISTINCT
STORE
, ITEM
, TRANS
, SUB_TRANS
, VEND
, VENDOR_NAME
, STORE_NAME
, STORE_INVDATE
, SUM (QTY)
FROM VIN_TRANSIT, VAZWHSE,
VAZVEND, SESSION.STRINVDATES
WHERE POS_TRANS =: P
AND POS_STORE = TAB_STORE_NBR
AND POS_ADD_CLEAR_DATE IS NULL
AND POS_ITEM = ITEM
AND WHSE = TAB_STORE_WHSE
AND VEND = VENDOR
AND POS_FLAG IN (:B, :Y)
GROUP BY POS_STORE, POS_ITEM, POS_TRANS, POS_SUB_TRANS,
VEND, VENDOR_NAME,
TAB_STORE_NAME, TAB_STORE_INVDATE
now since its grouping a set of variables and then finding the disticnt value its taking a longer time, please let me know hw could i fine tune above query or is anyway i can split the query but maintain the same end result?
plz suggest, let me know if its confusing.....
Cheers,
vinay |
|
| Back to top |
|
 |
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
I imagine the fact that it is a 4 table join has more to do with the execution time than the distinct.
have you run an explain on the query? if not, do so. |
|
| Back to top |
|
|
Prajesh_v_p
Active User
Joined: 24 May 2006
Posts: 133
Location: India
|
|
|
|
Do an explain of the query..Check how index is being used.. Bring in more columns if possible to increse the index match in where clause..
Are u sure you need a distint cluse out there?.. Group by and sum fuction retrieves only sum for all distict values of columns selected. Since there are some diffrence in the select clause and group by clause..you need to check it out!
Qry seems a bit confusing as you have not used any alias and I am not sure which columns comes from which table.. |
|
| Back to top |
|
|
vinay_care
New User
Joined: 22 Sep 2006
Posts: 17
|
|
|
|
| Prajesh_v_p wrote: |
Do an explain of the query..Check how index is being used.. Bring in more columns if possible to increse the index match in where clause..
Are u sure you need a distint cluse out there?.. Group by and sum fuction retrieves only sum for all distict values of columns selected. Since there are some diffrence in the select clause and group by clause..you need to check it out!
Qry seems a bit confusing as you have not used any alias and I am not sure which columns comes from which table.. |
I did do the explain but it shows scan being performed on tablespace and not on index |
|
| Back to top |
|
|
Prajesh_v_p
Active User
Joined: 24 May 2006
Posts: 133
Location: India
|
|
|
|
Vinay,
I am not clear on whats wrong with this as I dont have any info regarding the index and columns of the tables.. Can you provide these details also... |
|
| Back to top |
|
|
vinay_care
New User
Joined: 22 Sep 2006
Posts: 17
|
|
|
|
| Apart from POS_FLAG all other fields are set with index |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
try changing from this:
| Code: |
...
AND VEND = VENDOR
AND POS_FLAG IN (:B, :Y)
GROUP BY POS_STORE, POS_ITEM, POS_TRANS, POS_SUB_TRANS,
VEND, VENDOR_NAME,
TAB_STORE_NAME, TAB_STORE_INVDATE
|
to this:
| Code: |
AND VEND = VENDOR
GROUP BY POS_STORE, POS_ITEM, POS_TRANS, POS_SUB_TRANS,
VEND, VENDOR_NAME,
TAB_STORE_NAME, TAB_STORE_INVDATE
HAVING POS_FLAG IN (:B, :Y)
|
|
|
| Back to top |
|
|
|
|
