And since the real query is probably more complex, I'd suggest starting
from the SQL optimization process first (maybe bigger restructuring is not
needed at all):
*Measure* where most of your query execution *time* is spent and *do it
less*.
What you'd need to do less and how entirely depends on what the 1st step -
measuring execution time - shows. You can measure with
GATHER_PLAN_STATISTICS hint or by enabling statistics_level=all or SQL
Trace in your session and looking into resulting execution plans (with
DBMS_XPLAN.DISPLAY_CURSOR(format=>'ALLSTATS LAST')).
For example, if you are looking for just one row, but the measurement shows
that you're doing a time-consuming INDEX RANGE SCAN that does 10k Buffer
Gets just to find one row, you're gonna need a better index that has all
the relevant WHERE clause (and potentially nested loop columns) in the
right order. Or you might see an efficient UNIQUE INDEX lookup used on your
table, but that lookup is executed 10k times (Starts = 10,000), then you
might want a different join order, so that your tables are joined in a
different order and the query plan wouldn't have to "peek into" your table
just to find one matching row. These are just a couple of examples of where
your *measurement* might direct you, but measuring before fixing things is
the only systematic way to go.
Tanel.
On Fri, Mar 29, 2024 at 1:25 PM Laurentiu Oprea <laurentiu.oprea06@xxxxxxxxx>
wrote:
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@xxxxxxxxx>
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@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