|
|
|
|
| Re: Window Sort [message #329339 is a reply to message #329324] |
Wed, 25 June 2008 02:48   |
ArvindBhope
Messages: 55
Registered: June 2008
|
Member |
|
|
When I see in my Plan I see the Window sort is happening.
Iam not whether its for a good thing to be there in the query or not!! Or is there any best sort which can be used other than WINDOW SORT or WINDOW SORT PUSHED RANK.
Ideally is Window sort best or is any thing else can be forced to ?
Arvind
|
|
|
|
| Re: Window Sort [message #329343 is a reply to message #329339] |
Wed, 25 June 2008 02:56 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
You get a WINDOW SORT as a result of using Analytic Functions (OVER clause). Remove the function, the WINDOW SORT disappears.
Ross Leishman
|
|
|
|
| Re: Window Sort [message #329352 is a reply to message #329324] |
Wed, 25 June 2008 03:15 |
ArvindBhope
Messages: 55
Registered: June 2008
|
Member |
|
|
Yeah that can be done!! But the Function cannot be removed as application demands it.Please let me know the best possibilities in this case.
Thanks,
Arvind
|
|
|
|
|
|
|
|
|
|
| Re: Window Sort [message #329374 is a reply to message #329324] |
Wed, 25 June 2008 04:24 |
ArvindBhope
Messages: 55
Registered: June 2008
|
Member |
|
|
I meant Application demands the analytic function and that can't be avoided at any case. But the cost at the Window Join is very high and iam unable to reduce taht.
Any suggestions in the plan here mostly at that key area "Window ...."
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2240 | 743K| | 84967 (1)| 00:17:00 |
| 1 | SORT ORDER BY | | 2240 | 743K| 1640K| 84967 (1)| 00:17:00 |
|* 2 | HASH JOIN RIGHT OUTER | | 2240 | 743K| | 84802 (1)| 00:16:58 |
|* 3 | VIEW | | 4480 | 266K| | 42401 (1)| 00:08:29 |
| 4 | WINDOW SORT | | 4480 | 192K| | 42401 (1)| 00:08:29 |
|* 5 | TABLE ACCESS BY INDEX ROWID | XXX_MAGNET_XXX | 4480 | 192K| | 42399 (1)| 00:08:29 |
| 6 | BITMAP CONVERSION TO ROWIDS | | | | | | |
| 7 | BITMAP OR | | | | | | |
| 8 | BITMAP CONVERSION FROM ROWIDS| | | | | | |
|* 9 | INDEX RANGE SCAN | CRYO_FUNC1 | | | | 89 (0)| 00:00:02 |
| 10 | BITMAP CONVERSION FROM ROWIDS| | | | | | |
| 11 | SORT ORDER BY | | | | | | |
|* 12 | INDEX RANGE SCAN | PQR_MAGNET_ABC | | | | 40 (0)| 00:00:01 |
|* 13 | VIEW | | 2240 | 610K| | 42401 (1)| 00:08:29 |
|* 14 | WINDOW SORT PUSHED RANK | | 2240 | 107K| | 42401 (1)| 00:08:29 |
|* 15 | TABLE ACCESS BY INDEX ROWID | XXX_MAGNET_XXX | 2240 | 107K| | 42400 (1)| 00:08:29 |
| 16 | BITMAP CONVERSION TO ROWIDS | | | | | | |
| 17 | BITMAP OR | | | | | | |
| 18 | BITMAP CONVERSION FROM ROWIDS| | | | | | |
|* 19 | INDEX RANGE SCAN | CRYO_FUNC1 | | | | 89 (0)| 00:00:02 |
| 20 | BITMAP CONVERSION FROM ROWIDS| | | | | | |
| 21 | SORT ORDER BY | | | | | | |
|* 22 | INDEX RANGE SCAN | VVV_MAGNET_VVV | | | | 40 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------
Thanks,
Arvind
|
|
|
|
| Re: Window Sort [message #329412 is a reply to message #329374] |
Wed, 25 June 2008 06:34 |
 |
Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Nothing to do from the information you gave.
By the way, please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Use the "Preview Message" button to verify.
Regards
Michel
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
