Re: Query runtime is slow in view

Did you place this as a database idea? (I might have even voted for it and not remember..)

Am 03.02.2021 um 10:00 schrieb Noveljic Nenad:
>
> >which makes a rewrite cumbersome in some cases.
>
> A reason more to implement SSQ unnesting as an optimizer feature.
>
> Best regards,
>
> Nenad
>
> *From:*Lothar Flatz <l.flatz_at_bluewin.ch>
> *Sent:* Mittwoch, 3. Februar 2021 09:54
> *To:* Noveljic Nenad <nenad.noveljic_at_vontobel.com>; Laurentiu Oprea
> <laurentiu.oprea06_at_gmail.com>; eramitsaroha_at_gmail.com
> *Cc:* ORACLE-L (oracle-l_at_freelists.org) <oracle-l_at_freelists.org>
> *Subject:* Re: Query runtime is slow in view
>
> Quite true. An Index is the quick fix. Rewrite is better because it
> allows the optimizer for more options.
> Thanks for pointing at the higher perspective, which also shows that
> indexing is not everything and maybe developers skills  are still
> needed when automatic indexing is in place.
> Generally you just have to consider that a column value select is an
> implicit outer join which makes a rewrite cumbersome in some cases.
>
> Am 03.02.2021 um 09:40 schrieb Noveljic Nenad:
>
> >As the activity column in the sql Monitor suggests, time is almost
> exclusively spent in this column value subquery:
>
>
> Scalar subquery (SSQ) in the select clause is generally a problem
> with large driving row sources. Unlike SQL Server, Oracle
> optimizer doesn’t unnest them, even in cases when that would be
> feasible.
>
> Many times, I got much better performance by manually rewriting
> such queries by replacing SSQ with either semi or outer join. It
> could be a better solution also in your case.
>
> Examples are listed here:
>
> https://nenadnoveljic.com/blog/correlated-subqueries-in-the-select-clause/
> <https://nenadnoveljic.com/blog/correlated-subqueries-in-the-select-clause/>
>
> https://nenadnoveljic.com/blog/correlated-subqueries-in-the-select-clause-2/
> <https://nenadnoveljic.com/blog/correlated-subqueries-in-the-select-clause-2/>
>
> Best regards,
>
> Nenad
>
> ____________________________________________________
>
> Please consider the environment before printing this e-mail.
>
> Bitte denken Sie an die Umwelt, bevor Sie dieses E-Mail drucken.
>
>
> Important Notice
>
> This message is intended only for the individual named. It may
> contain confidential or privileged information. If you are not the
> named addressee you should in particular not disseminate,
> distribute, modify or copy this e-mail. Please notify the sender
> immediately by e-mail, if you have received this message by
> mistake and delete it from your system.
> Without prejudice to any contractual agreements between you and us
> which shall prevail in any case, we take it as your authorization
> to correspond with you by e-mail if you send us messages by
> e-mail. However, we reserve the right not to execute orders and
> instructions transmitted by e-mail at any time and without further
> explanation.
> E-mail transmission may not be secure or error-free as information
> could be intercepted, corrupted, lost, destroyed, arrive late or
> incomplete. Also processing of incoming e-mails cannot be
> guaranteed. All liability of Vontobel Holding Ltd. and any of its
> affiliates (hereinafter collectively referred to as "Vontobel
> Group") for any damages resulting from e-mail use is excluded. You
> are advised that urgent and time sensitive messages should not be
> sent by e-mail and if verification is required please request a
> printed version.
> Please note that all e-mail communications to and from the
> Vontobel Group are subject to electronic storage and review by
> Vontobel Group. Unless stated to the contrary and without
> prejudice to any contractual agreements between you and Vontobel
> Group which shall prevail in any case, e-mail-communication is for
> informational purposes only and is not intended as an offer or
> solicitation for the purchase or sale of any financial instrument
> or as an official confirmation of any transaction.
> The legal basis for the processing of your personal data is the
> legitimate interest to develop a commercial relationship with you,
> as well as your consent to forward you commercial communications.
> You can exercise, at any time and under the terms established
> under current regulation, your rights. If you prefer not to
> receive any further communications, please contact your client
> relationship manager if you are a client of Vontobel Group or
> notify the sender. Please note for an exact reference to the
> affected group entity the corporate e-mail signature. For further
> information about data privacy at Vontobel Group please consult
> www.vontobel.com <https://www.vontobel.com>.
>
>
> Important Notice
>
> This message is intended only for the individual named. It may contain
> confidential or privileged information. If you are not the named
> addressee you should in particular not disseminate, distribute, modify
> or copy this e-mail. Please notify the sender immediately by e-mail,
> if you have received this message by mistake and delete it from your
> system.
> Without prejudice to any contractual agreements between you and us
> which shall prevail in any case, we take it as your authorization to
> correspond with you by e-mail if you send us messages by e-mail.
> However, we reserve the right not to execute orders and instructions
> transmitted by e-mail at any time and without further explanation.
> E-mail transmission may not be secure or error-free as information
> could be intercepted, corrupted, lost, destroyed, arrive late or
> incomplete. Also processing of incoming e-mails cannot be guaranteed.
> All liability of Vontobel Holding Ltd. and any of its affiliates
> (hereinafter collectively referred to as "Vontobel Group") for any
> damages resulting from e-mail use is excluded. You are advised that
> urgent and time sensitive messages should not be sent by e-mail and if
> verification is required please request a printed version.
> Please note that all e-mail communications to and from the Vontobel
> Group are subject to electronic storage and review by Vontobel Group.
> Unless stated to the contrary and without prejudice to any contractual
> agreements between you and Vontobel Group which shall prevail in any
> case, e-mail-communication is for informational purposes only and is
> not intended as an offer or solicitation for the purchase or sale of
> any financial instrument or as an official confirmation of any
> transaction.
> The legal basis for the processing of your personal data is the
> legitimate interest to develop a commercial relationship with you, as
> well as your consent to forward you commercial communications. You can
> exercise, at any time and under the terms established under current
> regulation, your rights. If you prefer not to receive any further
> communications, please contact your client relationship manager if you
> are a client of Vontobel Group or notify the sender. Please note for
> an exact reference to the affected group entity the corporate e-mail
> signature. For further information about data privacy at Vontobel
> Group please consult www.vontobel.com <https://www.vontobel.com>.
>

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