Re: Sql with Connect By is Slow

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