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@xxxxxxxxx> 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@xxxxxxxxx>
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
