Re: Functional index on MOD function with binds

Hi Jonathan,

Thanks for your reply .

You are correct , the actual query is different and comes from Procedure . Thanks for suggestion , i will check the feasibility of restructure the code using : dbms_parallel_execute

Thanks,
Krishna

On Fri, 29 Mar 2024 at 14:49, Jonathan Lewis <jlewisoracle_at_gmail.com> wrote:

> You are correct - you cannot create a function-based index that would
> handle the predicate.
>
> The query looks as if it came from a PL/SQL block using pl/sql variables
> (it's block capitals, and the bind variables are of the form :Bnnn). It
> also looks as if the real query is more complex than the one supplied since
> your bind variable names start at B3.
>
> It seems a little odd to use an expression of this sort to extract rows -
> are you trying to cater for manual coded parallelism where a variable
> number of batch jobs might be started simultaneously and the number of
> concurrent tasks appears as :B4 and each task gets its "task id" as :B3? If
> so, could you restructure the code to take advantage of the
> dbms_parallel_execute package? Among other things it might avoid wasting
> time in buffer busy waits and read-consistency work.
>
>
> Regards
> Jonathan Lewis
>
>
>
> On Fri, 29 Mar 2024 at 08:29, Krishnaprasad Yadav <chrishna0007_at_gmail.com>
> wrote:
>
>> Dear Gurus,
>>
>> Need to optimise the below query , since it has mod function is used , it
>> is possible to have functional index onit , as we could see that their is
>> bind present in mod function , for which i doubt and may not allow to
>> create it
>>
>> SELECT *
>> FROM EXAM ATT
>> where MOD(ATT.STUDENT_ID, :B4 ) = :B3
>>
>> Please let me know your thoughts on this and any other way to tune it
>>
>> Regards,
>> Krishna
>>
>

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