Re: Functional index on MOD function with binds

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