Re: Sql with Connect By is Slow

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