Greetings,

No of records in TABLE1 = 46697622
No of records in TABLE2 = 9433275
No of records in TABLE3 = 9576297

SELECT TAB1.P_TDATE,
FLOOR(TAB1.STIME/100) T_TIME,
SUM (CASE WHEN TAB1.LIND <> 1
AND (TAB2.CNAME NOT IN ('PN','AR')
OR (NVL(TAB3.OMNI,0) <> 7)
OR (TAB1.TIF <> 3))
THEN 1
ELSE 0
END ) ORDPOSTMKT,
SUM (CASE WHEN (TAB2.CNAME IN ('PN','AR')
OR (TAB3.OMNI = 7)
OR (TAB1.TIF = 3))
THEN 1
ELSE 0
END ) ORDOARS
FROM TABLE1 TAB1
LEFT OUTER JOIN (
( SELECT ID,
P_TDATE,
SYMBOL,
REFNUMBER,
CNAME,
ROW_NUMBER() OVER(PARTITION BY ID ORDER BY REFNUMBER) RANK
FROM TABLE2
) TAB2
JOIN (SELECT ID,
P_TDATE,
SYMBOL,
OMNI,
REFNUMBER,
ROW_NUMBER() OVER(PARTITION BY ID ORDER BY REFNUMBER) RANK
FROM TABLE3
) TAB3
ON (
TAB2.P_TDATE =
TAB3.P_TDATE AND TAB2.SYMBOL =
TAB3.SYMBOL
AND TAB2.ID =
TAB3.ID
AND TAB2.REFNUMBER = TAB3.REFNUMBER
AND TAB3.RANK = 1
AND TAB2.RANK = 1)
)
ON (TAB1.P_TDATE =
TAB3.P_TDATE
AND TAB1.SYMBOL =
TAB3.SYMBOL
AND TAB1.ID =
TAB3.ID)
WHERE TAB1.OIND <> 0
AND TAB1.OTYPE IN
('MR','GMR')
GROUP BY TAB1.P_TDATE,
FLOOR(TAB1.STIME/100)
ORDER BY T_TIME

Currently the query is taking 2+ hrs to execute.

The query is taking records from TABLE2 and TABLE3 and LEFT OUTER JOIN
with TABLE1.
I know I am not giving much details abt each step and conditions used
here but the query is
running fine and just wanted to know whether the same query can be
written
in a more efficient manner.

Any help would be appreciated.

TIA

DB version Info:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production

pankaj_wolfhunter (AT) yahoo (DOT) co.in wrote:
Explain plan generated with DBMS_XPLAN please.

If you are not familiar with DBMS_XPLAN use the script
$ORACLE_HOME/rdbms/admin/utlxplp.sql
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

pankaj_wolfhunter (AT) yahoo (DOT) co.in wrote:
I agree with Daniel Mogan, an explain plan would be helpful to see what
is happening. Slightly reformatting your SQL statement:
SELECT
TAB1.P_TDATE,
FLOOR(TAB1.STIME/100) T_TIME,
SUM (CASE WHEN
TAB1.LIND <> 1
AND (TAB2.CNAME NOT IN ('PN','AR')
OR NVL(TAB3.OMNI,0) <> 7
OR TAB1.TIF <> 3)
THEN 1
ELSE 0
END) ORDPOSTMKT,
SUM (CASE WHEN
(TAB2.CNAME IN ('PN','AR')
OR TAB3.OMNI = 7
OR TAB1.TIF = 3)
THEN 1
ELSE 0
END) ORDOARS
FROM
TABLE1 TAB1
LEFT OUTER JOIN (
(SELECT
ID,
P_TDATE,
SYMBOL,
REFNUMBER,
CNAME,
ROW_NUMBER() OVER(PARTITION BY ID ORDER BY REFNUMBER) RANK
FROM
TABLE2) TAB2
JOIN
(SELECT
ID,
P_TDATE,
SYMBOL,
OMNI,
REFNUMBER,
ROW_NUMBER() OVER(PARTITION BY ID ORDER BY REFNUMBER) RANK
FROM
TABLE3) TAB3
ON (TAB2.P_TDATE=TAB3.P_TDATE
AND TAB2.SYMBOL = TAB3.SYMBOL
AND TAB2.ID = TAB3.ID
AND TAB2.REFNUMBER = TAB3.REFNUMBER
AND TAB3.RANK = 1
AND TAB2.RANK = 1))
ON (TAB1.P_TDATE = TAB3.P_TDATE
AND TAB1.SYMBOL = TAB3.SYMBOL
AND TAB1.ID = TAB3.ID)
WHERE
TAB1.OIND <> 0
AND TAB1.OTYPE IN ('MR','GMR')
GROUP BY
TAB1.P_TDATE,
FLOOR(TAB1.STIME/100)
ORDER BY
T_TIME;

You are perfoming two ROW_NUMBER analytical operations, and discarding
the results. The ROW_NUMBER operation is sorting quite a few rows and
this sort is likely hitting the temporary tablespace quite hard. Your
query includes TAB3.RANK = 1 and TAB2.RANK = 1, but not TAB3.RANK =
TAB2.RANK. The explain plan would indicate if you are performing a
full table scan on TABLE1's 46,697,622 rows.

Check the size of your SORT_AREA_SIZE. Try rewriting the SQL statement
into an alternate form, similar to this:
SELECT
TAB1.P_TDATE,
FLOOR(TAB1.STIME/100) T_TIME,
SUM (CASE WHEN
TAB1.LIND <> 1
AND (TAB2.CNAME NOT IN ('PN','AR')
OR NVL(TAB3.OMNI,0) <> 7
OR TAB1.TIF <> 3)
THEN 1
ELSE 0
END) ORDPOSTMKT,
SUM (CASE WHEN
(TAB2.CNAME IN ('PN','AR')
OR TAB3.OMNI = 7
OR TAB1.TIF = 3)
THEN 1
ELSE 0
END) ORDOARS
FROM
TABLE1 TAB1,
TABLE2 TAB2,
TABLE3 TAB3
WHERE
TAB1.P_TDATE = TAB3.P_TDATE(+)
AND TAB1.SYMBOL = TAB3.SYMBOL(+)
AND TAB1.ID = TAB3.ID(+)
AND TAB3.P_TDATE = TAB2.P_TDATE(+)
AND TAB3.SYMBOL = TAB2.SYMBOL(+)
AND TAB3.ID = TAB2.ID(+)
AND TAB3.REFNUMBER = TAB2.REFNUMBER(+)
AND TAB3.RANK = TAB2.RANK(+)
AND TAB3.RANK(+) = 1
AND TAB2.RANK(+) = 1
AND TAB1.OIND <> 0
AND TAB1.OTYPE IN ('MR','GMR')
GROUP BY
TAB1.P_TDATE,
FLOOR(TAB1.STIME/100)
ORDER BY
T_TIME;

Do you need the outer join? If possible, remove that and performance
may improve considerably. I could be wrong, but I believe that the
CASE statements may not be functioning as expected if an outer join
would be required to retrieve all rows, for instance: TAB2.CNAME NOT IN
('PN','AR') - this may need to be rewritten as NVL(TAB2.CNAME,'PN')
NOT IN ('PN','AR') to obtain the expected results.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc.

Charles Hooper wrote:

Thanks for the replies.

Here's the explain plan generated by the query:

Execution Plan
----------------------------------------------------------

-------------------------------------------------------------------------------
-------------------------------

Bytes | Cost | Pstart| Pstop |

-------------------------------------------------------------------------------
-------------------------------

51M| 520K| | |

51M| 520K| | |

51M| 520K| | |

103M| 520K| | |

1248K| 251K| | |

4529K| 251K| | |

539M| 79490 | | |

246M| 79490 | | |

246M| 10858 | 28 | 28 |

246M| 10858 | 1 | 4 |

246M| 10858 | 109 | 112 |

611M| 109K| | |

224M| 109K| | |

224M| 44772 | 29 | 29 |

224M| 44772 | 1 | 4 |

224M| 44772 | 113 | 116 |

52M| 269K| 30 | 30 |

52M| 269K| 1 | 16 |


-------------------------------------------------------------------------------
-------------------------------


Note
-----
- 'PLAN_TABLE' is old version


Statistics
----------------------------------------------------------
8199 recursive calls
8695 db block gets
1568210 consistent gets
1429061 physical reads
0 redo size
10581 bytes sent via SQL*Net to client
1203 bytes received via SQL*Net from client
36 SQL*Net roundtrips to/from client
51 sorts (memory)
2 sorts (disk)
514 rows processed

pankaj_wolfhunter (AT) yahoo (DOT) co.in wrote:
Something that I missed in the original post that was made clear by the
explain plan: you have full table scans on TABLE2 and TABLE3. All rows
of TABLE2 are retrieved into a view (data is 539MB in size), and all
rows of TABLE3 are retrived into a view (data is 611MB in size), and
then the contents of these views are joined together using a hash join.
Only 1.2MB makes it out of the hash join, which is then joined with
TABLE1. The views that are generated are likely a result of the
(SELECT ... TABLE2) TAB2 syntax that you used. You have two sorts to
disk, which is possibly significant. You also have a high percentage
of physical reads compared to logical (consistent gets) reads. It is
unclear whether these physical reads are a result of the sort to disk.

Compare the explain plan of your query with the explain plan for the
modified query that I posted.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc.

Charles Hooper wrote:
And, in addition, do your future explain plans using DBMS_XPLAN ... not
whatever script you are using. DBMS_XPLAN will give the amount of TEMP
space used and other valuable information. Look at
$ORACLE_HOME/rdbms/admin/utlxplp.sql.

Note also the "note" about plan_table being the old version. You should
run catplan.sql (if available) and use the newest version.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Pre-build the sums in a matview.



pankaj_wolfhunter (AT) yahoo (DOT) co.in wrote:

EscVector wrote:

Charles, thanks for the efforts. I still have to compare the xplan
results.

Daniel, I'll keep that thing in mind.

EscVector, as u suggested for matview.
I was not familiar with that. I did some googling on that and found it
can greatly increase performance in
terms of querying large tables.

Some question, how can I apply concept of matview here?
I mean I have two "SUM's" here. Do I have to have to create two matview
in this case?
Will the whole "FROM" clause condition needs to specify in matview?
Is there something needs to be taken into consideration while using
matview?

I'll be helpful if u ppl can guide me here?

TIA

pankaj_wolfhunter (AT) yahoo (DOT) co.in wrote:
I suggest that you implement the other advice given here. You are
currently asking Oracle to do work that it does not need to do - the
advice provided will help you remove the unnecessary work. If the
performance is not acceptable, and you have the Enterprise Edition of
Oracle, take a look at materialized views.

Jonathan Lewis recently placed on his website an article about
DBMS_XPLAN. See:
http://jonathanlewis.wordpress.com/2...-10g/#comments

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc.