Home » RDBMS Server » Performance Tuning » how to avoid window sort? (oracle 12c release 2)
how to avoid window sort? [message #673346] Fri, 16 November 2018 00:20 Go to next message
vishwaBhushan
Messages: 3
Registered: November 2018
Junior Member
can i avoid window sort in below query ? And also giving explain plan.
 SELECT COUNT(DISTINCT ETS_SITE_ID) OVER (PARTITION BY EXTERNAL_INVOICE_ID) COL_DISP_CNT,
      SUM(TOTAL_AMOUNT) OVER (PARTITION BY EXTERNAL_INVOICE_ID) COL_DISP_TTL_AMT,
      AVG(TOTAL_AMOUNT) OVER (PARTITION BY EXTERNAL_INVOICE_ID) COL_DISP_AVG_AMT
    FROM  TXN_INVOICE_DTLS
        WHERE  APPROVAL_FLAG = -1
            AND EXTERNAL_INVOICE_ID IS NOT NULL
            AND CIRCLE_ID=24
            AND BILL_MONTH BETWEEN 201801 AND 201811;


Execution Plan
----------------------------------------------------------
Plan hash value: 2929361700
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 174 | 5394 | 631 (1)| 00:00:01 |
| 1 | WINDOW SORT | | 174 | 5394 | 631 (1)| 00:00:01 |
|* 2 | INDEX FAST FULL SCAN| IDX_INVOICEDTLS_EXTINVOICEID | 174 | 5394 | 630 (1)| 00:00:01 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("APPROVAL_FLAG"=(-1) AND "BILL_MONTH">=201801 AND "CIRCLE_ID"=24 AND
"EXTERNAL_INVOICE_ID" IS NOT NULL AND "BILL_MONTH"<=201811)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2397 consistent gets
0 physical reads
0 redo size
395 bytes sent via SQL*Net to client
489 bytes received via SQL*Net from client
1 SQL*Net round trips to/from client
1 sorts (memory)
0 sorts (disk)
0 rows processed
Re: how to avoid window sort? [message #673348 is a reply to message #673346] Fri, 16 November 2018 01:03 Go to previous messageGo to next message
John Watson
Messages: 8075
Registered: January 2010
Location: Global Village
Senior Member
To eliminate the window sort operation, you would have to rewrite the code to avoid the use of the analytic function.
Re: how to avoid window sort? [message #673349 is a reply to message #673348] Fri, 16 November 2018 01:08 Go to previous messageGo to next message
vishwaBhushan
Messages: 3
Registered: November 2018
Junior Member
This query is sub part of a query, so analytic function can't be avoided.
That means window sort can't be avoided without removing analytic function?
Re: how to avoid window sort? [message #673353 is a reply to message #673349] Fri, 16 November 2018 01:20 Go to previous messageGo to next message
Michel Cadot
Messages: 66714
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

The analytic function leads to this operation, this operation is part of the implementation of the analytic function.

Re: how to avoid window sort? [message #673356 is a reply to message #673353] Fri, 16 November 2018 02:38 Go to previous messageGo to next message
vishwaBhushan
Messages: 3
Registered: November 2018
Junior Member
can cost of window sort be minimized?
Re: how to avoid window sort? [message #673358 is a reply to message #673356] Fri, 16 November 2018 02:41 Go to previous messageGo to next message
John Watson
Messages: 8075
Registered: January 2010
Location: Global Village
Senior Member
Er.... THe cost of the window sort is 1, out of a total of 631. So I would say that it is pretty well minimized already.
Re: how to avoid window sort? [message #673360 is a reply to message #673358] Fri, 16 November 2018 03:36 Go to previous messageGo to next message
cookiemonster
Messages: 13747
Registered: September 2008
Location: Rainy Manchester
Senior Member
The query you've posted is, according to the explain plan, really fast.

If you think it's causing problems in a larger query then I suggest you post the larger query and the explain plan (or better yet execution plan) for that.
Re: how to avoid window sort? [message #673361 is a reply to message #673360] Fri, 16 November 2018 03:42 Go to previous messageGo to next message
John Watson
Messages: 8075
Registered: January 2010
Location: Global Village
Senior Member
I think it could be faster. 2397 consistent gets for just 174 rows is not good. It is scanning a composite index of at least six columns (the two projected columns plus the four filtering columns). Possibly the column order could be changed to get a range scan rather than a (fast) full scan. Or perhaps the composite b-tree index could be replaced with a set of single column bitmap indexes.
In general, when I see such a wide composite index I get worried. They are often created for one purpose without much thought, and it actually causes trouble later.

Re: how to avoid window sort? [message #673362 is a reply to message #673361] Fri, 16 November 2018 03:46 Go to previous message
cookiemonster
Messages: 13747
Registered: September 2008
Location: Rainy Manchester
Senior Member
For clarity to the OP - you're talking about speeding up retrieving data via the index, not speeding up the sort.
The sort doesn't need speeding up, oracle can sort 174 rows lighting fast.
Previous Topic: Optimal Chunk size for update of 50M record table
Next Topic: table Partition when values are unknown
Goto Forum:
  


Current Time: Fri Dec 06 14:43:32 CST 2019