Thank you so much Jonathan , Andy and Mohamed.
@Andy , the filter is on one column only (COL_PVDP_CD) of table TAB_PP_TD
(which is one of the table inside the VIEW) and others are JOIN conditions.
But that itself filters out to ~2million rows and all the rows being
satisfied by all the other JOIN criteria(some being outer JOINS) from other
tables. So the result set is basically not decreasing anyway till the last
JOIN. UI need to show the TOP ~N rows based on certain sorting/ordering
criteria out of those ~2million rows. But again the sorting not spilling to
temp but happen fully in memory.
So in above situation, as you mentioned putting "driving filter" early, it
will still results into ~2million rows without decreasing the result set
further. Please correct me here.
Also i am wondering, how can we put the "distinct and Order by" as soon as
possible in the query? as because ordering of data has to happen after all
the matching results comes out from the complete execution of the query,
same holds for distinct clause too. So, not able to fully understand your
thoughts here. Can you please explain a bit more, how we can achieve that
even with restructuring the table design?
And just a general thought comes to my mind, in case of a requirement like
such, where the query runs on one table or couple of table JOIN resulting
it into Millions of rows which has to be sorted and shown the top N rows to
the frontend/UI , what kind of design will be helpful for such use cases?
On Fri, 10 Nov, 2023, 3:18 pm Jonathan Lewis, <jlewisoracle@xxxxxxxxx>
wrote:
Mohamed,
Thanks for reminding me that I ought to check the manuals occasionally.
(v$sq_monitor in Database Reference 19c:
https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/V-SQL_MONITOR.html
)
It's worth pointing out, however, that I made the comment I did only
because I got DONE (FIRST N ROWS) after hitting Ctrl-C.
Off-hand I can think two possible explanations
1) I'm running command line SQL*Plus in Unix on the server; if you are
running Windows O/S as a network client the handling of ctrl-c may be
different.
2) You hit ctrl-c while Oracle was in a wait for PL/SQL timer, I had "set
pause on" and hit ctrl-C while Oracle was waiting for "SQL*Net message from
client".
Bottom line: SQL*Plus sessions can give different ending values for status
in response to the same client action.
Regards
Jonathan Lewis
On Fri, 10 Nov 2023 at 08:39, Mohamed Houri <mohamed.houri@xxxxxxxxx>
wrote:
Jonathan,
*I've learned something new today - I hadn't realised that the SQL
Monitor would report "DONE (FIRST N ROWS)" if you interrupted a query after
the first fetch, I would have guessed that a ctrl-C from SQL*Plus would
have resulted in "DONE (ERROR)". So the 'first N rows' really could be the
first N rows fetched out of 2M. Given that it's a sort unique (due to the
distinct combined with the "order by" and the optimizer getting clever) the
preceding hash join and entire 2M row sort must have completed before the
first row was sent to the user.*
You’re right to say that ctrl-C from SQL*Plus would have resulted in
"DONE (ERROR)”. The DONE (FIRST N ROWS) status is different. It appears
not, when the query itself is abruptly stopped, but when the client that
activated the query terminates the query before all rows are fetched. The
Oracle definition is crystal clear
· DONE (FIRST N ROWS) - Execution terminated by the application
before all rows were fetched
Simply put, if I launch a query from SQLPLUS, wait until a couple of
lines are fetched, and then:
· Issue a Ctrl-C --> I will get DONE (ERROR)
· Close the SQL*PLus window --> I will get DONE (FIRST N ROWS)
