No question at this time
DBA Top 10
1 A. Kavsek 8400
2 B. Vroman 5900
3 M. Cadot 5400
4 P. Wisse 4800
5 T. Boles 3700
6 J. Schnackenberg 2100
7 . Lauri 1500
8 R. Pattyn 700
9 T. P 600
9 J. PĂ©ran 600
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48407
Total active users1518
Act. users last 24h5
Act. users last hour0
Registered user hits last week275
Registered user hits last month927
Go up

Long running query without rownum filter in where-clause
Next thread: Broker syntax configuration for standby
Prev thread: ORA-00600: internal error code, arguments: [KGL-heap-size-exceeded],

Message Score Author Date
Hi, I inherited a query that seems to run "inde...... Lauri Jul 25, 2019, 08:24
Hi Lauri, It seems there's an index supporting ...... Philip Wisse Jul 25, 2019, 08:50
Hi Philip, Thanks! I will come back... I still ...... Lauri Jul 25, 2019, 08:53

Follow up by mail Click here


Subject: Long running query without rownum filter in where-clause
Author: Lauri, Netherlands
Date: Jul 25, 2019, 08:24, 25 days ago
Os info: All
Oracle info: 12cR1 or R2
Error info: none
Message: Hi,

I inherited a query that seems to run "indefinitely", but it I add a condition like rownum < 1000000, the it executes in few seconds.
The query has groupings, distincts and unions.
It seems to me a memory shortage for aggregating and sorting (I don't post it here because this is along piece of text), but I do not get any error message.
Does someone know how I could investigate futher?

Kind Regards
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Long running query without rownum filter in where-clause
Author: Philip Wisse, Netherlands
Date: Jul 25, 2019, 08:50, 25 days ago
Message: Hi Lauri,

It seems there's an index supporting your query but Oracle is ignoring it.
You might get the same effect with a FIRST_ROWS hint.

Note: Oracle might still be right to ignore the index if the purpose is to get ALL the rows, if there are many.

The answer to this behaviour is in the statistics.

Regards, Philip
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re-Long running query without rownum filter in where-clause
Author: Lauri, Netherlands
Date: Jul 25, 2019, 08:53, 25 days ago
Message: Hi Philip,

Thanks! I will come back... I still need the credential to log onto the database... I am waiting for it.

Kind Regards
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here