Re: Functional index on MOD function with binds

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_at_gmail.com> 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_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