Re: Estimations are as good as it can be, what else can be done here?

Thank you so much Jonathan , Andy and Mohamed.

_at_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_at_gmail.com> 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_at_gmail.com>
> 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)
>>
>>
>>
>>

--
http://www.freelists.org/webpage/oracle-l