| how to avoid window sort? [message #673346] |
Fri, 16 November 2018 00:20  |
 |
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 #673360 is a reply to message #673358] |
Fri, 16 November 2018 03:36  |
cookiemonster
Messages: 13963
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 |
John Watson
Messages: 8964
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 |
cookiemonster
Messages: 13963
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
