The SQL consists of a sub-query, which unions all base data, and an outer join, where I join a specific ID to all base datasets.
No - there is NO outer join in that query. And we can't tell what columns you are adding since you are using * to select them all from the first table; that could be one column or 100.
In the other thread you were ask about the execution plan - what does it show for the query?
Any large dataset requires memory - check the amount of memory your client has, how much is being used, how much swap space is available and how much is being used.
What testing/troubleshooting have you been doing in the hours since you posted your question? Post info about the tests you conducted and the results.
Restrict the query to return only ten thousand rows and test the results. Test with fifty thousand rows and get the results.
Well, "outer" wasnt a good choice to use. I meant the "outer" SQL, the sql outside of the parentheses.
Does it matter if it were oder 100 collumns? If yes why (looking on CPU usage).
I did not look on the execution plan, yet. I am not going for it because I now recognized the same behavior for other SQLs at well.
Memory is on 80% use and does not change when executing the SQL and even not when moving (or try it) to the end of resultlist.
The client got 2GB RAM and one 2.4Ghz CPU. Where can I see the amount of swap space? Is it the "kernel-memory paged"? Paged are 232 and 46 not paged.
Well I tried the inside-sql wich returned within 25 seconds, moving to end of resultlist -> fine
Trying the outside-sql crashed my client about 8 times now. I gave up to test it further. All time CPU goes immediately on 100% (it has max 10% idle).
It doenst matter if the client ist started long or short time ago. All other applications were closed.
Since I do not really know what the problem is (thats my main question here) I also dont know even what I should test to get it.
I am now relatively sure, thats its not the SQL.
How do I let SQL developer show only 50k datasets?
Using order by or where rownum <= will work but only show first 500 (maximum settable in sql developer as far as i know) results.
Another observation i made is: when clicking [CTRL] + [End] and then look on the progress bar showing me that its loading. this bar immediately lags. Even at start it is not fluent. A second later taskmanager shows 100% CPU.
I am going to do two things: request more CPU for client and request reinstallation of SQL Developer.
For other colleagues now I know the SQL works fine moving to end of resultlist. So its definately not a SQL problem.
As rp0428 suggests, by asking the tool to hold a large query result set all in memory for display, eventually that resource will become a bottleneck. As SQL Developer runs inside a Java virtual machine, eventually the JVM's garbage collector will start to spend all its time trying to garbage collect unused objects as its heap memory allocation reaches the Xmx limit -- 100% cpu (with luck, only 100% of 1 cpu core) to gc, and 0% to doing any useful work.
The question you ultimately must ask yourself: what is the business requirement for scrolling through 100K+ rows? If you want to read more on this topic, here are a couple of earlier discussions from this forum:
Your question on how to restrict the number of rows is best asked on the SQL and PL/SQL forum
SQL Developer Team