Re: Query runtime is slow in view

Hi,

first of all Sql Devloper will only retrieve the first 50 (by default) rows. Timing in SQL Developer can not be trusted if the result is > 50 rows.  From your description I do not know if whatever you query in SQL Developer is really the equivalent of selecting from the (tuned) view. There is a good chance that you are dealing with two diffent queries. The best way simulating a not yet created view is a subquery in the from clause. Even than it is not sure that we really get the same execution plan when we query the stored view.
We would need run time statistics to determine what is going on. Working without exact measurement is unscientific.

Regards

Lothar

Am 01.02.2021 um 18:14 schrieb Amit Saroha:
> Good Morning, Everyone,
>
> I am in need of your expert suggestions for a weird situation and any
> pointers are appreciated.
>
> The situation is - There’s a view containing a UNION query, which I
> tuned and results are coming in less than a second when running in SQL
> developer. But, when I replaced the existing view query with the tuned
> query and select it from the view then it started taking more than 2
> minutes.
>
> I have never seen such a situation before when the query is running
> within a second but when used inside view starts taking the time.
>
>
> Best Regards,
> Amit

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