Re: Functional index on MOD function with binds

You might want to look into partitioning the table, use ora_hash function and explore concurrency using dbms parallel execute

On Fri, Mar 29, 2024, 16:47 Krishnaprasad Yadav <chrishna0007_at_gmail.com> wrote:

> 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