fine tuning of query execution using cursor.setinputsizes gives - ORA-01036: illegal variable name/number

[urosdigital]

Hello,

I have the following query:

query=
DELETE FROM my_table
WHERE id = :1 and rowid not in
(SELECT MIN(rowid)
FROM my_table
GROUP BY id)

This query is executed without errors using the function shown below. The issue is with execution time, it takes too long for small number of records:

input [202411112108015064, 202411112104433149]

@timer
def delete_duplicates1(input_list):
    param_list = [[x] for x in input_list]
    cursor.executemany(query, param_list)
    deleted_rows = cursor.rowcount
    return deleted_rows

needs ~5 seconds to complete ->

20241127T133601.898h wrapper 109: Function delete_duplicates1() execution time: 4.81974503s

In case when 9 records needs to be deleted, it takes ~29 seconds ->

20241106T133122.951h wrapper 109: Function delete_duplicates1() execution time: 28.72231798s

I have tried to use setinputsizes param with following adjustments of the same function but it gives an error:

@timer
def delete_duplicates2(input_list):
    param_list = [[x] for x in input_list]
    db_types = [int for x in Input_list]
    cursor.setinputsizes(*db_types)
    cursor.executemany(query, param_list)
    deleted_rows = cursor.rowcount
    return deleted_rows

gives:

code: 1036
full code: ORA-01036
error message: ORA-01036: illegal variable name/number

Any suggestions how to overcome this issue?

BR,
Uros

[cjbj]

The number of parameters to setinputsizes should match the number of bind variable placeholders in the SQL string, i.e. be 1 in your case.

Since the data is already numeric, forcing the type may not help: but check your SQL optimizer plan.

param_list = [(x,) for x in input_list]
cursor.setinputsizes(oracledb.DB_TYPE_NUMBER)
cursor.executemany(sql, param_list)
deleted_rows = cursor.rowcount

[urosdigital]

Hi @cjbj

Thanks for you feedback, i see i had a wrong expectation from setinputsizes param in this case. I should go for sql optimization.

BR,
Uros