Re: Sql with Connect By is Slow

Hi Krishna,

This is where explaining the query comes in. Is the intention to run the connect by against every row in the table and then return results where the first level of the connect by fits
LIMIT_C = :B1
In that case you probably want to do it reverse- start with all the rows that match that predicate and check that they have row in the table that would link to it using an exists clause.

My bet is that you really want to start with that predicate and run the connect by from there, but that’s not what your query currently means.

Thanks,
Andy

On Fri, 18 Feb 2022 at 12:36, Krishnaprasad Yadav <chrishna0007_at_gmail.com> wrote:

> Hi Andy ,
> Thanks for reverting .
> we need to reduce the elapsed time for query , it executes in high
> concurrency .
> So any way to reduce the elapsed time will be helpful .
>
> So as per my understanding , since "where" clause is executed at last
> , then time spent mostly on connect clause ,any way to reduce the time
> spent on that .
> you mentioned about "with" clause is their is way to rewrite .
>
> Regards,
> Krishna
>
>
> On Fri, 18 Feb 2022 at 17:34, Andy Sayer <andysayer_at_gmail.com> wrote:
>
>> Do you need a
>> Starting with
>> Clause? The Where clause is always executed last in a connect by.
>>
>> It’d help if you explained the situation further. But I would assume
>> you’ve just missed what the clauses mean, see the docs
>> https://docs.oracle.com/database/121/SQLRF/queries003.htm#SQLRF52332
>>
>> Thanks,
>> Andy
>>
>> On Fri, 18 Feb 2022 at 11:15, Krishnaprasad Yadav <chrishna0007_at_gmail.com>
>> wrote:
>>
>>> Dear Experts ,
>>>
>>> we are facing slowness in below sql text , any way to tune the query as
>>> it is using ' CONNECT BY '.
>>>
>>> SELECT PREFIX
>>> FROM TEST1
>>> WHERE LIMIT_C = :B1
>>> AND LEVEL = 1
>>> CONNECT BY NOCYCLE PRIOR LIMIT_C =LIMIT_P
>>>
>>>
>>> Executoin Plan:
>>> =================
>>>
>>> --------------------------------------------------------------------------------------------------
>>> | Id | Operation | Name | Rows | Bytes
>>> | Cost (%CPU)| Time |
>>>
>>> --------------------------------------------------------------------------------------------------
>>> | 0 | SELECT STATEMENT | | |
>>> | 9914 (100)| |
>>> |* 1 | FILTER | | |
>>> | | |
>>> |* 2 | CONNECT BY WITHOUT FILTERING| | |
>>> | | |
>>> | 3 | TABLE ACCESS FULL | TEST1 | 1538K|
>>> 42M| 9914 (1)| 00:00:01 |
>>>
>>> --------------------------------------------------------------------------------------------------
>>>
>>> Predicate Information (identified by operation id):
>>> ---------------------------------------------------
>>>
>>> 1 - filter(("LIMIT_C"=:B1 AND LEVEL=1))
>>> 2 - access("LIMIT_P"=PRIOR NULL)
>>>
>>>
>>> Global Stats
>>>
>>> ==================================================================================================
>>> | Elapsed | Cpu | IO | Concurrency | Other | Buffer | Read
>>> | Read | Write | Write |
>>> | Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Gets | Reqs
>>> | Bytes | Reqs | Bytes |
>>>
>>> ==================================================================================================
>>> | 94 | 16 | 27 | 0.00 | 51 | 26M | 41898
>>> | 1GB | 1079 | 208MB |
>>>
>>> ==================================================================================================
>>>
>>> SQL Plan Monitoring Details
>>>
>>> ====================================================================================================================================================================================
>>> =====================================
>>> | Id | Operation | Name | Rows |
>>> Cost | Time | Start | Execs | Rows | Read | Read | Write |
>>> Write | Mem | Temp | Activity |
>>> Activity Detail | Progress |
>>> | | | | (Estim) |
>>> | Active(s) | Active | | (Actual) | Reqs | Bytes | Reqs |
>>> Bytes | | | (%) |
>>> (# samples) | |
>>>
>>> ====================================================================================================================================================================================
>>> =====================================
>>> | -> 0 | SELECT STATEMENT | | |
>>> | 55 | +44 | 1 | 1 | | | |
>>> | . | . | |
>>> | |
>>> | -> 1 | FILTER | | |
>>> | 55 | +44 | 1 | 1 | | | |
>>> | . | . | |
>>> | |
>>> | -> 2 | CONNECT BY WITHOUT FILTERING | | |
>>> | 98 | +1 | 1 | 1M | 46178 | 507MB | 1079 |
>>> 208MB | 117KB | 108MB | 94.12 | Cp
>>> u (59) | 256% |
>>> | | | | |
>>> | | | | | | | |
>>> | | | | di
>>> rect path read temp (21) | |
>>> | 3 | TABLE ACCESS FULL | TEST1 | 2M |
>>> 9914 | 21 | +2 | 1 | 3M | 831 | 800MB | |
>>> | . | . | 5.88 | Cp
>>> u (2) | 100% |
>>> | | | | |
>>> | | | | | | | |
>>> | | | | db
>>> file scattered read (3) | |
>>>
>>> ====================================================================================================================================================================================
>>> =====================================
>>>
>>>
>>> Regards,
>>> Krishna
>>>
>>

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