| Author |
Message |
    
Minneapolis
Kurra Bewarse
Username: Minneapolis
Post Number: 878
Registered: 04-2005
Posted From: 167.79.91.20
| | Posted on Tuesday, July 18, 2006 - 1:22 pm: |    |
Tmbuff mama thanks very much for helping.
Nuvvu cheppina query perfect ga work chestundhi..it is returning the exact expected set of results..
Thanks once again to PD and Penkonda mamas also.. |
Tmbuff
Pilla Bewarse
Username: Tmbuff
Post Number: 153
Registered: 04-2004
Posted From: 159.153.4.51
| | Posted on Tuesday, July 18, 2006 - 1:04 pm: | |
oops ..
here is correct SQL
SELECT END_DT as END_DT,
STRT_DT as STRT_DT,
PERS_ID as PERS_ID
from (SELECT END_DT, STRT_DT, PERS_ID, MAX(STRT_DT) OVER (PARTITION BY PERS_ID) AS MAX_ST_DT
FROM WDW.DLT_TEMP)
WHERE MAX_ST_DT =STRT.DT |
Minneapolis
Kurra Bewarse
Username: Minneapolis
Post Number: 876
Registered: 04-2005
Posted From: 167.79.91.21
| | Posted on Tuesday, July 18, 2006 - 1:01 pm: | |
Tmbuff mama nuvvu icchina query run chesthe ee following error throw chestundhi...do u have any idea what changed need to be done to the query..
ORA-00923: FROM keyword not found where expected, Batch 1 Line 4 Col 63
after showing the error the cursor is being placed in between partition and OVER.... |
Proofdadaa
Pilla Bewarse
Username: Proofdadaa
Post Number: 264
Registered: 06-2006
Posted From: 129.138.18.85
| | Posted on Tuesday, July 18, 2006 - 12:18 pm: | |
one person ki fast gane vastundi mama..say 1 sec.
for 30k persons 30k seconds..i.e 8 hrs
PLAN_TABLE ki write access kavaali mama..mee DBA neeku aa privilege iste explain plan vastundi..
nenu choostunna query ela marcha vachaa ani |
Tmbuff
Pilla Bewarse
Username: Tmbuff
Post Number: 152
Registered: 04-2004
Posted From: 159.153.4.51
| | Posted on Tuesday, July 18, 2006 - 12:17 pm: | |
Minneapolis mama --
that query was poorly written. You can try this. This should give you better performance. I didn't validate the Query. If you are not getting correct results let me know.
SELECT END_DT as END_DT,
STRT_DT as STRT_DT,
PERS_ID as PERS_ID
from (SELECT END_DT, STRT_DT, PERS_ID, MAX(STRT_DT) PARTITION OVER BY (PERS_ID) AS MAX_ST_DT
FROM WDW.DLT_TEMP)
WHERE MAX_ST_DT =STRT.DT |
Minneapolis
Kurra Bewarse
Username: Minneapolis
Post Number: 875
Registered: 04-2005
Posted From: 167.79.91.21
| | Posted on Tuesday, July 18, 2006 - 12:12 pm: | |
PD mama nenu execution plan kosam a query koduthunte insufficient privileges antundhi..
so maa DBA ni adigithe query execution plan isthundhaa?
nuvvu cheppina logic ne mostly i m trying to implemement actually.. for each person ther will be 3 or 4 records with different start dates and end dates..all i want to do is get the max start date for each person return that record...
so when i run the query for only one person it is returning the exact results with in no time..but nenu cheppina logic ni inthakante better ga rayochha.. |
Proofdadaa
Pilla Bewarse
Username: Proofdadaa
Post Number: 261
Registered: 06-2006
Posted From: 129.138.18.85
| | Posted on Tuesday, July 18, 2006 - 12:04 pm: | |
say emp a has 10 strt_dates.
adi 10 dates ki max calculate chesi aa date tho malli 10 dates ni compare chesi equal aina dates istundi. For this case, 10 logical reads + grouping overhead.
ikkade potundi..
 |
Proofdadaa
Pilla Bewarse
Username: Proofdadaa
Post Number: 260
Registered: 06-2006
Posted From: 129.138.18.85
| | Posted on Tuesday, July 18, 2006 - 11:56 am: | |
SQL>
EXPLAIN PLAN
SET statement_id = <statement_identifier>
FOR
<SQL statement here>;
SQL>
SELECT LPAD(' ',2*(level-1)) || operation || ' ' || options ||' ' || object_name || ' ' ||
DECODE(id,0,'Cost = ' || position) QUERY_OUTPUT
FROM plan_table
START WITH id = 0
AND statement_id = 'abc'
CONNECT BY PRIOR id = parent_id
AND statement_id = 'abc';
neeku PLAN_TABLE ki write access vundaali..query plan choodataaniki..first step lo SET daggara nuvvu nee query statement id ni table liki rastunnav..mee DBA ni adugu istaadu.. |
Penkonda_tiger
Bewarse
Username: Penkonda_tiger
Post Number: 1645
Registered: 06-2005
Posted From: 193.178.209.212
| | Posted on Tuesday, July 18, 2006 - 11:55 am: | |
 I am leaving my office now in Brussels .. catch you back in 2 hours when back in Amsterdam |
Penkonda_tiger
Bewarse
Username: Penkonda_tiger
Post Number: 1644
Registered: 06-2005
Posted From: 193.178.209.212
| | Posted on Tuesday, July 18, 2006 - 11:53 am: | |
mamma ...
at SQL prompt type in the following
SQL>ALTER SESSION SET SQL_TRACE = TRUE
SQL> yourSQL ;
SQL>ALTER SESSION SET SQL_TRACE = FALSE
CMD promt loo TKPROF seyeee ... for the tkprof you need the trace file located on the Oracle server instance |
Minneapolis
Kurra Bewarse
Username: Minneapolis
Post Number: 874
Registered: 04-2005
Posted From: 167.79.91.21
| | Posted on Tuesday, July 18, 2006 - 11:51 am: | |
PD mama...motham table lo 130k records vunnayi...kaani aa query return cheyyalsindhi only around 40k ..endhukante distinct pers_id vacchesi only 40k vunnayi kabhatti..
ayithe ippudu date fields join ki round about emayinaa vundhaa.. |
Proofdadaa
Pilla Bewarse
Username: Proofdadaa
Post Number: 259
Registered: 06-2006
Posted From: 129.138.18.85
| | Posted on Tuesday, July 18, 2006 - 11:48 am: | |
date fields meeda general gaa index vundavu mama..nee query lo date fields meeda join vundi..so akkada slow avutundi anukunta..
asaalu table lo enni records vunnayi mottam?? |
Minneapolis
Kurra Bewarse
Username: Minneapolis
Post Number: 873
Registered: 04-2005
Posted From: 167.79.91.21
| | Posted on Tuesday, July 18, 2006 - 11:44 am: | |
Penkonda mama nenu vaade tool lo Execution plan elaa vadaalo teliyadam ledhu..i have been trying to get it..permissions vunnayo ledho teleedhu firs thing...but SQL plus nundi elaa execution plan generate cheyyoccho telusaa mama neeku..so that i can send it to u.. |
Babu
Desanike Pedda Bewarse
Username: Babu
Post Number: 8881
Registered: 06-2004
Posted From: 65.208.22.26
| | Posted on Tuesday, July 18, 2006 - 11:24 am: | |
prob cud be u r using same table mama and with nested loops, it is going to take some time..
one suggestion check whether the indexes r defined and statistics r updated accordingly
other thing is see the explain table along with statistics u get to know what is that subquery that is taking more load
final suggestion is split the query and then execute and deduce which one is taking longer..
like check exists part and see |
Penkonda_tiger
Bewarse
Username: Penkonda_tiger
Post Number: 1643
Registered: 06-2005
Posted From: 193.178.209.212
| | Posted on Tuesday, July 18, 2006 - 11:24 am: | |
Minneapolis mamma ...
can you make an SQL TRACE , and TKPROF it and send the Execution plan to zimchak@gmail.com |
Minneapolis
Kurra Bewarse
Username: Minneapolis
Post Number: 872
Registered: 04-2005
Posted From: 167.79.91.21
| | Posted on Tuesday, July 18, 2006 - 11:22 am: | |
Hemanth mama SQL tho ekkuva nuvve invlove avuthaavu kadhaa..but no problem anyways..
evarayinaa help chesthe that would be a lot to me..ikkade aagi poyindhi naa work anthaa |
Hemanth
Bewarse ke Bewarse!
Username: Hemanth
Post Number: 18391
Registered: 03-2004
Posted From: 65.196.167.82
| | Posted on Tuesday, July 18, 2006 - 11:19 am: | |
naku anthaga theliyadu mama.  |
Minneapolis
Kurra Bewarse
Username: Minneapolis
Post Number: 871
Registered: 04-2005
Posted From: 167.79.91.21
| | Posted on Tuesday, July 18, 2006 - 11:14 am: | |
mama ee query ni evarayina optimize cheyyagalaraa..
its taking forever and ever..return chese records emi anni ekkuva levu..any suggestions to make it better. DBA daggara ki velli adugudhamani vundhi..kaani intha chinna query ki adigithe dobbutharemo ani doubt..
SELECT a.END_DT as END_DT,
a.STRT_DT as STRT_DT,
a.PERS_ID as PERS_ID
FROM
WDW.DLT_EMP a
WHERE EXISTS(SELECT 1 FROM WDW.DLT_EMP b
GROUP BY PERS_ID HAVING MAX(b.STRT_DT) =a.STRT_DT AND
b.PERS_ID =a.PERS_ID) |
