Home » RDBMS Server » Performance Tuning » Need suggestion for a better query.
Need suggestion for a better query. [message #335522] |
Tue, 22 July 2008 07:39 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
pravin3032
Messages: 51 Registered: November 2006 Location: eARTH
|
Member |
|
|
Database :Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
--- Create Table -----
create table GRP_TEMP
(
GROUP_ID NUMBER,
DEAL_NAME VARCHAR2(30),
FAC_ID NUMBER,
FAC_NAME VARCHAR2(30)
);
--- Insert -----
Insert into GRP_TEMP (GROUP_ID, DEAL_NAME, FAC_ID, FAC_NAME) Values (1, 'AAAAA', 1, 'DUEDKBNFYO');
Insert into GRP_TEMP (GROUP_ID, DEAL_NAME, FAC_ID, FAC_NAME) Values (1, 'AAAAA', 2, 'ZIXXOHYCYT');
Insert into GRP_TEMP (GROUP_ID, DEAL_NAME, FAC_ID, FAC_NAME) Values (1, 'AAAAA', 3, 'PIZBPWLOSW');
Insert into GRP_TEMP (GROUP_ID, DEAL_NAME, FAC_ID, FAC_NAME) Values (1, 'AAAAA', 4, 'KXKBYLRNOF');
Insert into GRP_TEMP (GROUP_ID, DEAL_NAME, FAC_ID, FAC_NAME) Values (2, 'CCCCC', 5, 'KVVLKKGQIH');
Insert into GRP_TEMP (GROUP_ID, DEAL_NAME, FAC_ID, FAC_NAME) Values (2, 'CCCCC', 6, 'IUDJQTRPRD');
Insert into GRP_TEMP (GROUP_ID, DEAL_NAME, FAC_ID, FAC_NAME) Values (2, 'CCCCC', 7, 'YYCJQYUOLD');
Insert into GRP_TEMP (GROUP_ID, DEAL_NAME, FAC_ID, FAC_NAME) Values (2, 'CCCCC', 8, 'NJXTFASGSY');
Insert into GRP_TEMP (GROUP_ID, DEAL_NAME, FAC_ID, FAC_NAME) Values (3, 'DDDDD', 9, 'DTJWFBQCRQ');
Insert into GRP_TEMP (GROUP_ID, DEAL_NAME, FAC_ID, FAC_NAME) Values (3, 'DDDDD', 10, 'OPLRPAUPAV');
Insert into GRP_TEMP (GROUP_ID, DEAL_NAME, FAC_ID, FAC_NAME) Values (3, 'DDDDD', 11, 'NTAGAOVWPI');
Insert into GRP_TEMP (GROUP_ID, DEAL_NAME, FAC_ID, FAC_NAME) Values (3, 'DDDDD', 12, 'RVIBZDZTMH');
Insert into GRP_TEMP (GROUP_ID, DEAL_NAME, FAC_ID, FAC_NAME) Values (4, 'EEEEE', 13, 'OJEWUCKSJG');
Insert into GRP_TEMP (GROUP_ID, DEAL_NAME, FAC_ID, FAC_NAME) Values (4, 'EEEEE', 14, 'WGASEEHESV');
Insert into GRP_TEMP (GROUP_ID, DEAL_NAME, FAC_ID, FAC_NAME) Values (4, 'EEEEE', 15, 'JIXZROTKLB');
Insert into GRP_TEMP (GROUP_ID, DEAL_NAME, FAC_ID, FAC_NAME) Values (4, 'EEEEE', 16, 'IJYFSIWKYK');
COMMIT;
--- Query -----
SELECT DENSE_RANK() OVER(ORDER BY PRE_SORT_COL ASC, A.GROUP_ID) RECORD_NUM,
A.RECORD_TOTAL,
A.GROUP_ID,
A.DEAL_NAME,
A.FAC_ID,
A.FAC_NAME
FROM (SELECT COUNT(DISTINCT V1.GROUP_ID) OVER() RECORD_TOTAL,
V1.*,
MIN(V1.FAC_NAME) OVER(PARTITION BY V1.GROUP_ID) PRE_SORT_COL
FROM GRP_TEMP V1) A
ORDER BY PRE_SORT_COL ASC, GROUP_ID, FAC_NAME ASC;
Output of above query:
RECORD_NUM RECORD_TOTAL GROUP_ID DEAL_NAME FAC_ID FAC_NAME
---------- ------------ ---------- ------------------------------ ---------- -----------
1 4 3 DDDDD 9 DTJWFBQCRQ
1 4 3 DDDDD 11 NTAGAOVWPI
1 4 3 DDDDD 10 OPLRPAUPAV
1 4 3 DDDDD 12 RVIBZDZTMH
2 4 4 EEEEE 14 DTJWFBQCRQ
2 4 4 EEEEE 16 IJYFSIWKYK
2 4 4 EEEEE 15 JIXZROTKLB
2 4 4 EEEEE 13 OJEWUCKSJG
3 4 1 AAAAA 1 DUEDKBNFYO
3 4 1 AAAAA 4 KXKBYLRNOF
3 4 1 AAAAA 3 PIZBPWLOSW
3 4 1 AAAAA 2 ZIXXOHYCYT
4 4 2 CCCCC 6 IUDJQTRPRD
4 4 2 CCCCC 5 KVVLKKGQIH
4 4 2 CCCCC 8 NJXTFASGSY
4 4 2 CCCCC 7 YYCJQYUOLD
16 rows selected.
SQL>
The above query produces the same result exactly what i needed.
In the above query i am sorting(ASC) on FAC_NAME column.
A GROUP_ID can have multiple FAC_ID.
For query perormance point of view i want to know wether i can get the same result by any other efficient query.
Please put your suggestion.
Thanks
Pravin.
|
|
|
Re: Need suggestion for a better query. [message #335658 is a reply to message #335522] |
Wed, 23 July 2008 03:19 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
Analytic functions aren't always the most efficient solution. The seem like they do a single pass of the data, but you can end up doing multiple passes for sorting in memory/TEMP.
Try:
WITH GRPS AS (
SELECT GROUP_ID, ROWNUM AS RECORD_NUM
FROM (
SELECT GROUP_ID, MIN(V1.FAC_NAME) AS MIN_FAC_NAME
FROM GRP_TEMP
GROUP BY GROUP_ID
ORDER BY 2, 1
)
)
SELECT A.RECORD_NUM, B.RECORD_TOTAL, C.*
FROM GRPS A
CROSS JOIN (SELECT COUNT(*) AS RECORD_TOTAL FROM GROUPS) B
JOIN GRP_TEMP C ON C.GROUP_ID = A.GROUP_ID
Ross Leishman
|
|
|
Re: Need suggestion for a better query. [message #336648 is a reply to message #335658] |
Mon, 28 July 2008 09:01 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
pravin3032
Messages: 51 Registered: November 2006 Location: eARTH
|
Member |
|
|
hi Ross,
thanks for the query that you have given.
and i am very sorry for not able to reply you immded.
I have done a small changes in that query to get a same output
as it was giving by my query. Also found your query work 2 times faster than my query.
Your Query(Modified):
WITH GRPS AS (
SELECT GROUP_ID, ROWNUM AS RECORD_NUM
FROM (
SELECT GROUP_ID, MIN( FAC_NAME) AS MIN_FAC_NAME
FROM GRP_TEMP
GROUP BY GROUP_ID
ORDER BY 2, 1
)
)
SELECT A.RECORD_NUM, B.RECORD_TOTAL, C.*
FROM GRPS A
CROSS JOIN (SELECT COUNT(*) AS RECORD_TOTAL FROM GRPS) B
JOIN GRP_TEMP C ON C.GROUP_ID = A.GROUP_ID
ORDER BY 1 , FAC_NAME ASC ; .
Instead of table GRP_TEMP i am using view which is based on no. of tables.i have attached its definition.
when i run the same (above)query against that view its take too much time approx 20 sec to shows 50 groups.
Below i have mention few details. Please suggest, what is wrong in the query/view.
1. Query
WITH GRPS AS(
SELECT GROUP_ID, ROWNUM AS RECORD_NUM
FROM (SELECT GROUP_ID, MIN(
case when v.TRADE_TYPE_IND='NORM' THEN V.DEAL_NAME
when v.TRADE_TYPE_IND IN ('SPLT', 'TRPL') AND V.IS_PARENT_FLAG='Y' THEN V.DEAL_NAME
ELSE NULL END ) AS MIN_FAC_NAME
FROM TEMP_VW V
GROUP BY GROUP_ID
ORDER BY 2 ASC , 1
))
SELECT
A.RECORD_NUM, B.RECORD_TOTAL, C.*
FROM GRPS A
CROSS JOIN (SELECT COUNT(*) AS RECORD_TOTAL FROM GRPS) B
JOIN TEMP_VW C ON C.GROUP_ID = A.GROUP_ID
WHERE RECORD_NUM BETWEEN 1 AND 50
ORDER BY 1, DEAL_NAME ASC ;
2.Tables Rows
SLT_SLIQ_TRADE =25000
SLT_SLIQ_TRADE_FACILITY=50000
SLT_SLIQ_PORTFOLIO =25000
SLT_E_SETTL_TRADE=25000
SLT_E_SETTL_FACILITY=50000
3. Explain plan.
Elapsed: 00:00:19.70
Execution Plan
----------------------------------------------------------
Plan hash value: 152901756
-------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5837 | 17M| | 6782 (2)| 00:01:22 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | | |
| 2 | LOAD AS SELECT | TEMP_VW | | | | | |
| 3 | COUNT | | | | | | |
| 4 | VIEW | | 764 | 9932 | | 761 (5)| 00:00:10 |
| 5 | SORT ORDER BY | | 764 | 31324 | | 761 (5)| 00:00:10 |
| 6 | SORT GROUP BY | | 764 | 31324 | | 761 (5)| 00:00:10 |
| 7 | VIEW | TEMP_VW | 764 | 31324 | | 759 (5)| 00:00:10 |
| 8 | UNION-ALL | | | | | | |
| 9 | NESTED LOOPS OUTER | | 249 | 19422 | | 286 (9)| 00:00:04 |
|* 10 | HASH JOIN | | 249 | 18426 | | 285 (9)| 00:00:04 |
|* 11 | TABLE ACCESS FULL | SLT_SLIQ_TRADE | 250 | 10500 | | 247 (6)| 00:00:03 |
| 12 | NESTED LOOPS | | 25000 | 781K| | 37 (25)| 00:00:01 |
| 13 | INDEX FAST FULL SCAN | SLT_SLIQ_PORTFOLIO_PK | 25000 | 390K| | 29 (4)| 00:00:01 |
|* 14 | INDEX RANGE SCAN | SLT_SLIQ_TRADE_FACILITY_PK | 1 | 16 | | 0 (0)| 00:00:01 |
|* 15 | INDEX RANGE SCAN | SLT_E_SETTL_TRADE_PK | 1 | 4 | | 0 (0)| 00:00:01 |
|* 16 | FILTER | | | | | | |
|* 17 | HASH JOIN | | 515 | 14420 | | 216 (7)| 00:00:03 |
|* 18 | TABLE ACCESS FULL | SLT_E_SETTL_TRADE | 263 | 6312 | | 175 (6)| 00:00:03 |
| 19 | INDEX FAST FULL SCAN | SLT_E_SETTL_FACILITY_PK | 50000 | 195K| | 39 (3)| 00:00:01 |
|* 20 | INDEX RANGE SCAN | SLT_SLIQ_TRADE_IE1 | 1 | 4 | | 1 (0)| 00:00:01 |
| 21 | SORT ORDER BY | | 5837 | 17M| 45M| 6021 (2)| 00:01:13 |
|* 22 | HASH JOIN | | 5837 | 17M| | 2234 (3)| 00:00:27 |
| 23 | NESTED LOOPS | | 764 | 29796 | | 4 (0)| 00:00:01 |
| 24 | VIEW | | 1 | 13 | | 2 (0)| 00:00:01 |
|* 25 | FILTER | | | | | | |
| 26 | SORT AGGREGATE | | 1 | | | | |
| 27 | VIEW | | 764 | | | 2 (0)| 00:00:01 |
| 28 | TABLE ACCESS FULL | SYS_TEMP_0FD9D680A_2E882A84 | 764 | 9932 | | 2 (0)| 00:00:01 |
|* 29 | VIEW | | 764 | 19864 | | 2 (0)| 00:00:01 |
| 30 | TABLE ACCESS FULL | SYS_TEMP_0FD9D680A_2E882A84 | 764 | 9932 | | 2 (0)| 00:00:01 |
| 31 | VIEW | TEMP_VW | 764 | 2286K| | 2230 (3)| 00:00:27 |
| 32 | UNION-ALL | | | | | | |
|* 33 | HASH JOIN OUTER | | 249 | 130K| | 1061 (3)| 00:00:13 |
|* 34 | HASH JOIN | | 249 | 127K| | 893 (3)| 00:00:11 |
|* 35 | HASH JOIN | | 498 | 204K| | 739 (4)| 00:00:09 |
|* 36 | TABLE ACCESS FULL | SLT_SLIQ_TRADE | 250 | 50000 | | 247 (6)| 00:00:03 |
| 37 | TABLE ACCESS FULL | SLT_SLIQ_TRADE_FACILITY | 50000 | 10M| | 490 (2)| 00:00:06 |
| 38 | TABLE ACCESS FULL | SLT_SLIQ_PORTFOLIO | 25000 | 2539K| | 153 (2)| 00:00:02 |
| 39 | TABLE ACCESS FULL | SLT_E_SETTL_TRADE | 26256 | 333K| | 167 (2)| 00:00:03 |
|* 40 | FILTER | | | | | | |
|* 41 | HASH JOIN | | 515 | 158K| | 653 (3)| 00:00:08 |
|* 42 | TABLE ACCESS FULL | SLT_E_SETTL_TRADE | 263 | 26563 | | 175 (6)| 00:00:03 |
| 43 | TABLE ACCESS FULL | SLT_E_SETTL_FACILITY | 50000 | 10M| | 476 (2)| 00:00:06 |
| 44 | TABLE ACCESS BY INDEX ROWID| SLT_SLIQ_TRADE | 1 | 12 | | 2 (0)| 00:00:01 |
|* 45 | INDEX RANGE SCAN | SLT_SLIQ_TRADE_IE1 | 1 | | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
10 - access("SLT_SLIQ_TRADE_FACILITY"."TRADE_RID"="SLT_SLIQ_TRADE"."TRADE_RID")
11 - filter(CASE WHEN ("SLT_SLIQ_TRADE"."CIRCLE_STATUS"='CNPERM' AND "SLT_SLIQ_TRADE"."IS_PARENT_FLAG"='Y')
THEN 1 WHEN ("SLT_SLIQ_TRADE"."CIRCLE_STATUS"='CNPERM' AND "SLT_SLIQ_TRADE"."IS_PARENT_FLAG"='N') THEN 2 WHEN
"SLT_SLIQ_TRADE"."CIRCLE_STATUS"='CAN' THEN 2 ELSE 1 END =1)
14 - access("SLT_SLIQ_PORTFOLIO"."TRADE_RID"="SLT_SLIQ_TRADE_FACILITY"."TRADE_RID" AND
"SLT_SLIQ_PORTFOLIO"."TRADE_FACILITY_ID"="SLT_SLIQ_TRADE_FACILITY"."TRADE_FACILITY_ID")
15 - access("SLT_E_SETTL_TRADE"."SLT_TRADE_ID"(+)="SLT_SLIQ_TRADE"."E_SETTL_TRADE_ID")
16 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM TESTSLT."SLT_SLIQ_TRADE" "SLT_SLIQ_TRADE" WHERE
"SLT_SLIQ_TRADE"."E_SETTL_TRADE_ID"=:B1))
17 - access("SLT_E_SETTL_TRADE"."SLT_TRADE_ID"="SLT_E_SETTL_FACILITY"."SLT_TRADE_ID")
18 - filter(CASE WHEN ("SLT_E_SETTL_TRADE"."CIRCLE_STATUS"='CNPERM' AND
"SLT_E_SETTL_TRADE"."IS_PARENT_FLAG"='Y') THEN 1 WHEN ("SLT_E_SETTL_TRADE"."CIRCLE_STATUS"='CNPERM' AND
"SLT_E_SETTL_TRADE"."IS_PARENT_FLAG"='N') THEN 2 WHEN "SLT_E_SETTL_TRADE"."CIRCLE_STATUS"='CAN' THEN 2 ELSE 1
END =1)
20 - access("SLT_SLIQ_TRADE"."E_SETTL_TRADE_ID"=:B1)
22 - access("C"."GROUP_ID"="A"."GROUP_ID")
25 - filter(1<=50)
29 - filter("A"."RECORD_NUM">=1 AND "A"."RECORD_NUM"<=50)
33 - access("SLT_E_SETTL_TRADE"."SLT_TRADE_ID"(+)="SLT_SLIQ_TRADE"."E_SETTL_TRADE_ID")
34 - access("SLT_SLIQ_PORTFOLIO"."TRADE_RID"="SLT_SLIQ_TRADE_FACILITY"."TRADE_RID" AND
"SLT_SLIQ_PORTFOLIO"."TRADE_FACILITY_ID"="SLT_SLIQ_TRADE_FACILITY"."TRADE_FACILITY_ID")
35 - access("SLT_SLIQ_TRADE_FACILITY"."TRADE_RID"="SLT_SLIQ_TRADE"."TRADE_RID")
36 - filter(CASE WHEN ("SLT_SLIQ_TRADE"."CIRCLE_STATUS"='CNPERM' AND "SLT_SLIQ_TRADE"."IS_PARENT_FLAG"='Y')
THEN 1 WHEN ("SLT_SLIQ_TRADE"."CIRCLE_STATUS"='CNPERM' AND "SLT_SLIQ_TRADE"."IS_PARENT_FLAG"='N') THEN 2 WHEN
"SLT_SLIQ_TRADE"."CIRCLE_STATUS"='CAN' THEN 2 ELSE 1 END =1)
40 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM TESTSLT."SLT_SLIQ_TRADE" "SLT_SLIQ_TRADE" WHERE
"SLT_SLIQ_TRADE"."E_SETTL_TRADE_ID"=:B1))
41 - access("SLT_E_SETTL_TRADE"."SLT_TRADE_ID"="SLT_E_SETTL_FACILITY"."SLT_TRADE_ID")
42 - filter(CASE WHEN ("SLT_E_SETTL_TRADE"."CIRCLE_STATUS"='CNPERM' AND
"SLT_E_SETTL_TRADE"."IS_PARENT_FLAG"='Y') THEN 1 WHEN ("SLT_E_SETTL_TRADE"."CIRCLE_STATUS"='CNPERM' AND
"SLT_E_SETTL_TRADE"."IS_PARENT_FLAG"='N') THEN 2 WHEN "SLT_E_SETTL_TRADE"."CIRCLE_STATUS"='CAN' THEN 2 ELSE 1
END =1)
45 - access("SLT_SLIQ_TRADE"."E_SETTL_TRADE_ID"=:B1)
Statistics
----------------------------------------------------------
114 recursive calls
66 db block gets
296430 consistent gets
57 physical reads
1468 redo size
34320 bytes sent via SQL*Net to client
502 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
53 rows processed
SQL>
-
Attachment: TEMP_VW.txt
(Size: 13.73KB, Downloaded 1310 times)
|
|
|
Re: Need suggestion for a better query. [message #336758 is a reply to message #336648] |
Mon, 28 July 2008 22:20 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
Let's just isolate what performance issues are due to the view, and those due to our SQL. How long does the following take?
SELECT *
FROM (
SELECT GROUP_ID, min(TRADE_TYPE_IND), min(DEAL_NAME)
FROM TEMP_VW V
GROUP BY GROUP_ID
ORDER BY 2 ASC , 1
)
WHERE ROWNUM > 1
Also see if this helps. It should avoid having to resolve the view twice:
WITH TMP AS (
SELECT GROUP_ID, TRADE_TYPE_IND, DEAL_NAME,
{.. any other required columns, NOT useless cols ..}
)
, GRPS AS (
SELECT GROUP_ID, ROWNUM AS RECORD_NUM
FROM (
SELECT GROUP_ID, MIN(...) AS MIN_FAC_NAME
FROM TMP
GROUP BY GROUP_ID
ORDER BY 2, 1
)
)
SELECT A.RECORD_NUM, B.RECORD_TOTAL, C.*
FROM GRPS A
CROSS JOIN (SELECT COUNT(*) AS RECORD_TOTAL FROM GRPS) B
JOIN TMP C ON C.GROUP_ID = A.GROUP_ID
ORDER BY 1 , FAC_NAME ASC ;
|
|
|
Re: Need suggestion for a better query. [message #336912 is a reply to message #336648] |
Tue, 29 July 2008 05:53 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
pravin3032
Messages: 51 Registered: November 2006 Location: eARTH
|
Member |
|
|
Thanks again for your help Ross.Definitely the problem seems to be with view and not with query.
But still I am not able to trace the problem in that view.
That view has 112 columns out of which 80 are shown on the screen.
Here are the statistics.
Query 1:
SELECT *
FROM (
SELECT GROUP_ID, min(TRADE_TYPE_IND), min(DEAL_NAME)
FROM TEMP_VW V
GROUP BY GROUP_ID
ORDER BY 2 ASC , 1
) WHERE ROWNUM > 1 ;
Elapsed: 00:00:03.33
Execution Plan
----------------------------------------------------------
Plan hash value: 1454345946
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 311 | 10885 | 508 (8)| 00:00:07 |
| 1 | COUNT | | | | | |
|* 2 | FILTER | | | | | |
| 3 | VIEW | | 311 | 10885 | 508 (8)| 00:00:07 |
| 4 | SORT ORDER BY | | 311 | 10885 | 508 (8)| 00:00:07 |
| 5 | HASH GROUP BY | | 311 | 10885 | 508 (8)| 00:00:07 |
| 6 | VIEW | TEMP_VW | 311 | 10885 | 506 (7)| 00:00:07 |
| 7 | UNION-ALL | | | | | |
| 8 | NESTED LOOPS OUTER | | 249 | 18426 | 286 (9)| 00:00:04 |
|* 9 | HASH JOIN | | 249 | 17430 | 285 (9)| 00:00:04 |
|* 10 | TABLE ACCESS FULL | SLT_SLIQ_TRADE | 250 | 9500 | 247 (6)| 00:00:03 |
| 11 | NESTED LOOPS | | 25000 | 781K| 37 (25)| 00:00:01 |
| 12 | INDEX FAST FULL SCAN| SLT_SLIQ_PORTFOLIO_PK | 25000 | 390K| 29 (4)| 00:00:01 |
|* 13 | INDEX RANGE SCAN | SLT_SLIQ_TRADE_FACILITY_PK | 1 | 16 | 0 (0)| 00:00:01 |
|* 14 | INDEX RANGE SCAN | SLT_E_SETTL_TRADE_PK | 1 | 4 | 0 (0)| 00:00:01 |
| 15 | NESTED LOOPS | | 62 | 1736 | 220 (5)| 00:00:03 |
|* 16 | HASH JOIN ANTI | | 32 | 768 | 188 (6)| 00:00:03 |
|* 17 | TABLE ACCESS FULL | SLT_E_SETTL_TRADE | 263 | 5260 | 175 (6)| 00:00:03 |
|* 18 | INDEX FAST FULL SCAN | SLT_SLIQ_TRADE_IE1 | 22137 | 88548 | 13 (8)| 00:00:01 |
|* 19 | INDEX RANGE SCAN | SLT_E_SETTL_FACILITY_PK | 2 | 8 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(ROWNUM>1)
9 - access("SLT_SLIQ_TRADE_FACILITY"."TRADE_RID"="SLT_SLIQ_TRADE"."TRADE_RID")
10 - filter(CASE WHEN ("SLT_SLIQ_TRADE"."CIRCLE_STATUS"='CNPERM' AND
"SLT_SLIQ_TRADE"."IS_PARENT_FLAG"='Y') THEN 1 WHEN ("SLT_SLIQ_TRADE"."CIRCLE_STATUS"='CNPERM' AND
"SLT_SLIQ_TRADE"."IS_PARENT_FLAG"='N') THEN 2 WHEN "SLT_SLIQ_TRADE"."CIRCLE_STATUS"='CAN' THEN 2
ELSE 1 END =1)
13 - access("SLT_SLIQ_PORTFOLIO"."TRADE_RID"="SLT_SLIQ_TRADE_FACILITY"."TRADE_RID" AND
"SLT_SLIQ_PORTFOLIO"."TRADE_FACILITY_ID"="SLT_SLIQ_TRADE_FACILITY"."TRADE_FACILITY_ID")
14 - access("SLT_E_SETTL_TRADE"."SLT_TRADE_ID"(+)="SLT_SLIQ_TRADE"."E_SETTL_TRADE_ID")
16 - access("SLT_SLIQ_TRADE"."E_SETTL_TRADE_ID"="SLT_E_SETTL_TRADE"."SLT_TRADE_ID")
17 - filter(CASE WHEN ("SLT_E_SETTL_TRADE"."CIRCLE_STATUS"='CNPERM' AND
"SLT_E_SETTL_TRADE"."IS_PARENT_FLAG"='Y') THEN 1 WHEN ("SLT_E_SETTL_TRADE"."CIRCLE_STATUS"='CNPERM'
AND "SLT_E_SETTL_TRADE"."IS_PARENT_FLAG"='N') THEN 2 WHEN "SLT_E_SETTL_TRADE"."CIRCLE_STATUS"='CAN'
THEN 2 ELSE 1 END =1)
18 - filter("SLT_SLIQ_TRADE"."E_SETTL_TRADE_ID" IS NOT NULL)
19 - access("SLT_E_SETTL_TRADE"."SLT_TRADE_ID"="SLT_E_SETTL_FACILITY"."SLT_TRADE_ID")
Statistics
----------------------------------------------------------
8 recursive calls
0 db block gets
102155 consistent gets
0 physical reads
0 redo size
397 bytes sent via SQL*Net to client
458 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
0 rows processed
SQL>
Query 2: Selected only 16 columns
WITH TMP AS (
SELECT group_id,
slt_trade_id,
slt_parent_trade_id,
is_parent_flag,
trade_type_ind,
trade_rid,
trade_id,
parent_trade_id,
cls_type_ind,
e_settl_trade_id,
trade_ref_id,
trade_lock_ind,
deal_name,
deal_cusip,
excp_type_code,
facility_name
from TEMP_VW V
),
GRPS AS (
SELECT GROUP_ID, ROWNUM AS RECORD_NUM
FROM (
SELECT GROUP_ID, MIN( facility_name ) AS MIN_FAC_NAME
FROM TMP
GROUP BY GROUP_ID
ORDER BY 2, 1
))
SELECT A.RECORD_NUM, B.RECORD_TOTAL, C.*
FROM GRPS A
CROSS JOIN (SELECT COUNT(*) AS RECORD_TOTAL FROM GRPS) B
JOIN TMP C ON C.GROUP_ID = A.GROUP_ID
WHERE RECORD_NUM BETWEEN 1 AND 50
ORDER BY 1 , facility_name ASC ;
Elapsed: 00:00:06.57
Execution Plan
----------------------------------------------------------
Plan hash value: 3325484232
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 205 | 1867 (3)| 00:00:23 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT | | | | | |
| 3 | VIEW | TEMP_VW | 764 | 123K| 1847 (3)| 00:00:23 |
| 4 | UNION-ALL | | | | | |
|* 5 | HASH JOIN OUTER | | 249 | 29382 | 936 (3)| 00:00:12 |
|* 6 | HASH JOIN | | 249 | 26145 | 768 (4)| 00:00:10 |
|* 7 | HASH JOIN | | 498 | 44322 | 739 (4)| 00:00:09 |
|* 8 | TABLE ACCESS FULL | SLT_SLIQ_TRADE | 250 | 14500 | 247 (6)| 00:00:03 |
| 9 | TABLE ACCESS FULL | SLT_SLIQ_TRADE_FACILITY | 50000 | 1513K| 490 (2)| 00:00:06 |
| 10 | INDEX FAST FULL SCAN| SLT_SLIQ_PORTFOLIO_PK | 25000 | 390K| 29 (4)| 00:00:01 |
| 11 | TABLE ACCESS FULL | SLT_E_SETTL_TRADE | 26256 | 333K| 167 (2)| 00:00:03 |
|* 12 | FILTER | | | | | |
|* 13 | HASH JOIN | | 515 | 36050 | 653 (3)| 00:00:08 |
|* 14 | TABLE ACCESS FULL | SLT_E_SETTL_TRADE | 263 | 12098 | 175 (6)| 00:00:03 |
| 15 | TABLE ACCESS FULL | SLT_E_SETTL_FACILITY | 50000 | 1171K| 476 (2)| 00:00:06 |
|* 16 | INDEX RANGE SCAN | SLT_SLIQ_TRADE_IE1 | 1 | 4 | 1 (0)| 00:00:01 |
| 17 | LOAD AS SELECT | | | | | |
| 18 | COUNT | | | | | |
| 19 | VIEW | | 1 | 13 | 8 (25)| 00:00:01 |
| 20 | SORT ORDER BY | | 1 | 30 | 8 (25)| 00:00:01 |
| 21 | SORT GROUP BY | | 1 | 30 | 8 (25)| 00:00:01 |
| 22 | VIEW | | 764 | 22920 | 6 (0)| 00:00:01 |
| 23 | TABLE ACCESS FULL | SYS_TEMP_0FD9D688C_2E882A84 | 764 | 123K| 6 (0)| 00:00:01 |
| 24 | SORT ORDER BY | | 1 | 205 | 12 (17)| 00:00:01 |
|* 25 | HASH JOIN | | 1 | 205 | 11 (10)| 00:00:01 |
| 26 | NESTED LOOPS | | 764 | 29796 | 4 (0)| 00:00:01 |
| 27 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
|* 28 | FILTER | | | | | |
| 29 | SORT AGGREGATE | | 1 | | | |
| 30 | VIEW | | 764 | | 2 (0)| 00:00:01 |
| 31 | TABLE ACCESS FULL | SYS_TEMP_0FD9D688D_2E882A84 | 764 | 9932 | 2 (0)| 00:00:01 |
|* 32 | VIEW | | 764 | 19864 | 2 (0)| 00:00:01 |
| 33 | TABLE ACCESS FULL | SYS_TEMP_0FD9D688D_2E882A84 | 764 | 9932 | 2 (0)| 00:00:01 |
| 34 | VIEW | | 764 | 123K| 6 (0)| 00:00:01 |
| 35 | TABLE ACCESS FULL | SYS_TEMP_0FD9D688C_2E882A84 | 764 | 123K| 6 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("SLT_E_SETTL_TRADE"."SLT_TRADE_ID"(+)="SLT_SLIQ_TRADE"."E_SETTL_TRADE_ID")
6 - access("SLT_SLIQ_PORTFOLIO"."TRADE_RID"="SLT_SLIQ_TRADE_FACILITY"."TRADE_RID" AND
"SLT_SLIQ_PORTFOLIO"."TRADE_FACILITY_ID"="SLT_SLIQ_TRADE_FACILITY"."TRADE_FACILITY_ID")
7 - access("SLT_SLIQ_TRADE_FACILITY"."TRADE_RID"="SLT_SLIQ_TRADE"."TRADE_RID")
8 - filter(CASE WHEN ("SLT_SLIQ_TRADE"."CIRCLE_STATUS"='CNPERM' AND
"SLT_SLIQ_TRADE"."IS_PARENT_FLAG"='Y') THEN 1 WHEN ("SLT_SLIQ_TRADE"."CIRCLE_STATUS"='CNPERM' AND
"SLT_SLIQ_TRADE"."IS_PARENT_FLAG"='N') THEN 2 WHEN "SLT_SLIQ_TRADE"."CIRCLE_STATUS"='CAN' THEN 2
ELSE 1 END =1)
12 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM TESTSLT."SLT_SLIQ_TRADE" "SLT_SLIQ_TRADE" WHERE
"SLT_SLIQ_TRADE"."E_SETTL_TRADE_ID"=:B1))
13 - access("SLT_E_SETTL_TRADE"."SLT_TRADE_ID"="SLT_E_SETTL_FACILITY"."SLT_TRADE_ID")
14 - filter(CASE WHEN ("SLT_E_SETTL_TRADE"."CIRCLE_STATUS"='CNPERM' AND
"SLT_E_SETTL_TRADE"."IS_PARENT_FLAG"='Y') THEN 1 WHEN
("SLT_E_SETTL_TRADE"."CIRCLE_STATUS"='CNPERM' AND "SLT_E_SETTL_TRADE"."IS_PARENT_FLAG"='N') THEN
2 WHEN "SLT_E_SETTL_TRADE"."CIRCLE_STATUS"='CAN' THEN 2 ELSE 1 END =1)
16 - access("SLT_SLIQ_TRADE"."E_SETTL_TRADE_ID"=:B1)
25 - access("C"."GROUP_ID"="A"."GROUP_ID")
28 - filter(1<=50)
32 - filter("A"."RECORD_NUM">=1 AND "A"."RECORD_NUM"<=50)
Statistics
----------------------------------------------------------
479 recursive calls
436 db block gets
106882 consistent gets
413 physical reads
3060 redo size
6361 bytes sent via SQL*Net to client
502 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
58 rows processed
SQL>
Query 3: Select 112 columns
WITH TMP AS (
SELECT * from TEMP_VW V
),
GRPS AS (
SELECT GROUP_ID, ROWNUM AS RECORD_NUM
FROM (
SELECT GROUP_ID, MIN( facility_name ) AS MIN_FAC_NAME
FROM TMP
GROUP BY GROUP_ID
ORDER BY 2, 1
))
SELECT A.RECORD_NUM, B.RECORD_TOTAL, C.*
FROM GRPS A
CROSS JOIN (SELECT COUNT(*) AS RECORD_TOTAL FROM GRPS) B
JOIN TMP C ON C.GROUP_ID = A.GROUP_ID
WHERE RECORD_NUM BETWEEN 1 AND 50
ORDER BY 1 , facility_name ASC ;
Elapsed: 00:00:15.60
Execution Plan
----------------------------------------------------------
Plan hash value: 2296946159
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3104 | 2123 (3)| 00:00:26 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT | | | | | |
| 3 | VIEW | TEMP_VW | 764 | 2286K| 1972 (3)| 00:00:24 |
| 4 | UNION-ALL | | | | | |
|* 5 | HASH JOIN OUTER | | 249 | 130K| 1061 (3)| 00:00:13 |
|* 6 | HASH JOIN | | 249 | 127K| 893 (3)| 00:00:11 |
|* 7 | HASH JOIN | | 498 | 204K| 739 (4)| 00:00:09 |
|* 8 | TABLE ACCESS FULL | SLT_SLIQ_TRADE | 250 | 50000 | 247 (6)| 00:00:03 |
| 9 | TABLE ACCESS FULL | SLT_SLIQ_TRADE_FACILITY | 50000 | 10M| 490 (2)| 00:00:06 |
| 10 | TABLE ACCESS FULL | SLT_SLIQ_PORTFOLIO | 25000 | 2539K| 153 (2)| 00:00:02 |
| 11 | TABLE ACCESS FULL | SLT_E_SETTL_TRADE | 26256 | 333K| 167 (2)| 00:00:03 |
|* 12 | FILTER | | | | | |
|* 13 | HASH JOIN | | 515 | 158K| 653 (3)| 00:00:08 |
|* 14 | TABLE ACCESS FULL | SLT_E_SETTL_TRADE | 263 | 26563 | 175 (6)| 00:00:03 |
| 15 | TABLE ACCESS FULL | SLT_E_SETTL_FACILITY | 50000 | 10M| 476 (2)| 00:00:06 |
|* 16 | INDEX RANGE SCAN | SLT_SLIQ_TRADE_IE1 | 1 | 4 | 1 (0)| 00:00:01 |
| 17 | LOAD AS SELECT | | | | | |
| 18 | COUNT | | | | | |
| 19 | VIEW | | 1 | 13 | 74 (5)| 00:00:01 |
| 20 | SORT ORDER BY | | 1 | 30 | 74 (5)| 00:00:01 |
| 21 | SORT GROUP BY | | 1 | 30 | 74 (5)| 00:00:01 |
| 22 | VIEW | | 764 | 22920 | 72 (2)| 00:00:01 |
| 23 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6892_2E882A84 | 764 | 2286K| 72 (2)| 00:00:01 |
| 24 | SORT ORDER BY | | 1 | 3104 | 77 (3)| 00:00:01 |
|* 25 | HASH JOIN | | 1 | 3104 | 76 (2)| 00:00:01 |
| 26 | NESTED LOOPS | | 764 | 29796 | 4 (0)| 00:00:01 |
| 27 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
|* 28 | FILTER | | | | | |
| 29 | SORT AGGREGATE | | 1 | | | |
| 30 | VIEW | | 764 | | 2 (0)| 00:00:01 |
| 31 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6893_2E882A84 | 764 | 9932 | 2 (0)| 00:00:01 |
|* 32 | VIEW | | 764 | 19864 | 2 (0)| 00:00:01 |
| 33 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6893_2E882A84 | 764 | 9932 | 2 (0)| 00:00:01 |
| 34 | VIEW | | 764 | 2286K| 72 (2)| 00:00:01 |
| 35 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6892_2E882A84 | 764 | 2286K| 72 (2)| 00:00:01 |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("SLT_E_SETTL_TRADE"."SLT_TRADE_ID"(+)="SLT_SLIQ_TRADE"."E_SETTL_TRADE_ID")
6 - access("SLT_SLIQ_PORTFOLIO"."TRADE_RID"="SLT_SLIQ_TRADE_FACILITY"."TRADE_RID" AND
"SLT_SLIQ_PORTFOLIO"."TRADE_FACILITY_ID"="SLT_SLIQ_TRADE_FACILITY"."TRADE_FACILITY_ID")
7 - access("SLT_SLIQ_TRADE_FACILITY"."TRADE_RID"="SLT_SLIQ_TRADE"."TRADE_RID")
8 - filter(CASE WHEN ("SLT_SLIQ_TRADE"."CIRCLE_STATUS"='CNPERM' AND
"SLT_SLIQ_TRADE"."IS_PARENT_FLAG"='Y') THEN 1 WHEN ("SLT_SLIQ_TRADE"."CIRCLE_STATUS"='CNPERM' AND
"SLT_SLIQ_TRADE"."IS_PARENT_FLAG"='N') THEN 2 WHEN "SLT_SLIQ_TRADE"."CIRCLE_STATUS"='CAN' THEN 2
ELSE 1 END =1)
12 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM TESTSLT."SLT_SLIQ_TRADE" "SLT_SLIQ_TRADE" WHERE
"SLT_SLIQ_TRADE"."E_SETTL_TRADE_ID"=:B1))
13 - access("SLT_E_SETTL_TRADE"."SLT_TRADE_ID"="SLT_E_SETTL_FACILITY"."SLT_TRADE_ID")
14 - filter(CASE WHEN ("SLT_E_SETTL_TRADE"."CIRCLE_STATUS"='CNPERM' AND
"SLT_E_SETTL_TRADE"."IS_PARENT_FLAG"='Y') THEN 1 WHEN
("SLT_E_SETTL_TRADE"."CIRCLE_STATUS"='CNPERM' AND "SLT_E_SETTL_TRADE"."IS_PARENT_FLAG"='N') THEN
2 WHEN "SLT_E_SETTL_TRADE"."CIRCLE_STATUS"='CAN' THEN 2 ELSE 1 END =1)
16 - access("SLT_SLIQ_TRADE"."E_SETTL_TRADE_ID"=:B1)
25 - access("C"."GROUP_ID"="A"."GROUP_ID")
28 - filter(1<=50)
32 - filter("A"."RECORD_NUM">=1 AND "A"."RECORD_NUM"<=50)
Statistics
----------------------------------------------------------
2416 recursive calls
2550 db block gets
111801 consistent gets
2494 physical reads
4684 redo size
35149 bytes sent via SQL*Net to client
502 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
58 rows processed
SQL>
Query 4: Select to TEMP_VW
SELECT * FROM TEMP_VW ORDER BY 1 ;
Elapsed: 00:03:55.39
Execution Plan
----------------------------------------------------------
Plan hash value: 3305677553
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 311 | 930K| | 1546 (3)| 00:00:19 |
| 1 | SORT ORDER BY | | 311 | 930K| 2504K| 1546 (3)| 00:00:19 |
| 2 | VIEW | TEMP_VW | 311 | 930K| | 1344 (4)| 00:00:17 |
| 3 | UNION-ALL | | | | | | |
|* 4 | HASH JOIN OUTER | | 249 | 130K| | 1061 (3)| 00:00:13 |
|* 5 | HASH JOIN | | 249 | 127K| | 893 (3)| 00:00:11 |
|* 6 | HASH JOIN | | 498 | 204K| | 739 (4)| 00:00:09 |
|* 7 | TABLE ACCESS FULL | SLT_SLIQ_TRADE | 250 | 50000 | | 247 (6)| 00:00:03 |
| 8 | TABLE ACCESS FULL | SLT_SLIQ_TRADE_FACILITY | 50000 | 10M| | 490 (2)| 00:00:06 |
| 9 | TABLE ACCESS FULL | SLT_SLIQ_PORTFOLIO | 25000 | 2539K| | 153 (2)| 00:00:02 |
| 10 | TABLE ACCESS FULL | SLT_E_SETTL_TRADE | 26256 | 333K| | 167 (2)| 00:00:03 |
| 11 | TABLE ACCESS BY INDEX ROWID| SLT_E_SETTL_FACILITY | 2 | 430 | | 3 (0)| 00:00:01 |
| 12 | NESTED LOOPS | | 62 | 19840 | | 283 (5)| 00:00:04 |
|* 13 | HASH JOIN ANTI | | 32 | 3360 | | 188 (6)| 00:00:03 |
|* 14 | TABLE ACCESS FULL | SLT_E_SETTL_TRADE | 263 | 26563 | | 175 (6)| 00:00:03 |
|* 15 | INDEX FAST FULL SCAN | SLT_SLIQ_TRADE_IE1 | 22137 | 88548 | | 13 (8)| 00:00:01 |
|* 16 | INDEX RANGE SCAN | SLT_E_SETTL_FACILITY_PK | 2 | | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("SLT_E_SETTL_TRADE"."SLT_TRADE_ID"(+)="SLT_SLIQ_TRADE"."E_SETTL_TRADE_ID")
5 - access("SLT_SLIQ_PORTFOLIO"."TRADE_RID"="SLT_SLIQ_TRADE_FACILITY"."TRADE_RID" AND
"SLT_SLIQ_PORTFOLIO"."TRADE_FACILITY_ID"="SLT_SLIQ_TRADE_FACILITY"."TRADE_FACILITY_ID")
6 - access("SLT_SLIQ_TRADE_FACILITY"."TRADE_RID"="SLT_SLIQ_TRADE"."TRADE_RID")
7 - filter(CASE WHEN ("SLT_SLIQ_TRADE"."CIRCLE_STATUS"='CNPERM' AND
"SLT_SLIQ_TRADE"."IS_PARENT_FLAG"='Y') THEN 1 WHEN ("SLT_SLIQ_TRADE"."CIRCLE_STATUS"='CNPERM' AND
"SLT_SLIQ_TRADE"."IS_PARENT_FLAG"='N') THEN 2 WHEN "SLT_SLIQ_TRADE"."CIRCLE_STATUS"='CAN' THEN 2 ELSE 1
END =1)
13 - access("SLT_SLIQ_TRADE"."E_SETTL_TRADE_ID"="SLT_E_SETTL_TRADE"."SLT_TRADE_ID")
14 - filter(CASE WHEN ("SLT_E_SETTL_TRADE"."CIRCLE_STATUS"='CNPERM' AND
"SLT_E_SETTL_TRADE"."IS_PARENT_FLAG"='Y') THEN 1 WHEN ("SLT_E_SETTL_TRADE"."CIRCLE_STATUS"='CNPERM' AND
"SLT_E_SETTL_TRADE"."IS_PARENT_FLAG"='N') THEN 2 WHEN "SLT_E_SETTL_TRADE"."CIRCLE_STATUS"='CAN' THEN 2
ELSE 1 END =1)
15 - filter("SLT_SLIQ_TRADE"."E_SETTL_TRADE_ID" IS NOT NULL)
16 - access("SLT_E_SETTL_TRADE"."SLT_TRADE_ID"="SLT_E_SETTL_FACILITY"."SLT_TRADE_ID")
Statistics
----------------------------------------------------------
8 recursive calls
0 db block gets
17506 consistent gets
0 physical reads
0 redo size
14136794 bytes sent via SQL*Net to client
23195 bytes received via SQL*Net from client
2068 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
31000 rows processed
SQL>
|
|
|
Re: Need suggestion for a better query. [message #337098 is a reply to message #336912] |
Tue, 29 July 2008 21:58 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
The final query is using a different plan for the view - strange - but that is not the one you are trying to tune (right?) so I'll ignore it.
I assume you are trying to tune the 3rd query.
I noticed that the view contains a bunch of scalar sub-queries in the SELECT clause. You need to get rid of them. See this article for more.
Ross Leishman
|
|
|
Re: Need suggestion for a better query. [message #338275 is a reply to message #337098] |
Mon, 04 August 2008 07:27 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
pravin3032
Messages: 51 Registered: November 2006 Location: eARTH
|
Member |
|
|
Thanks Ross,
The query you was provided,
i am sure it can not BE further tunned.
Though my query is taking time because of the view and i
am thinking of using flat table which will be populated based on that view. hope i am going in the right direction.
i have also done test, created single table based on that view and when fire the query against it, its just takes approx 2 sec.
CREATE TABLE FLAT_TAB AS SELECT * FROM TEMP_VW.
I have choose this approch bec
1. Cant reduce no. of tables in the view
2. Can avoide sub-queries and CASE and other functions.in the
view
3. Union ALL.
I am sorry to myself and You for getting again late to reply.
Regards
Pravin.
|
|
|
Goto Forum:
Current Time: Wed Sep 11 21:05:45 CDT 2024
|