optimizing database record fetching time

i have 2 kind of big database tables : [product] and [product_position], a product may change position so i use a function product_pos(product_id) to get the current (meaning last) position of a said product. in my Apex report i need to show each product with its current position with this query:

SELECT id, serial_n, product_name, product_pos(id) as position
FROM product

the function product_pos :

create or replace FUNCTION product_pos(id_p IN NUMBER) 
    RETURN VARCHAR
AS
    res varchar2(20);
BEGIN
    select new_pos into res from product_position v where v.product_id=id_p order by date_move desc fetch first 1 rows only;
    return res; 
END product_pos;

the problem i have now is the fetching time, in the apex report page it takes almost 30 seconds for every loading/refresh. i thought about adding a column [position] in the product table and updating it by using a trigger for every insert/update/delete on [product_position] but that leaves a possibility for errors when someone changes the position in the [product] table manually. now is there a way to reduce the processing time or maybe a way to proceed with the trigger solution with no errors by making the new [position] column changeable by trigger only?

Answer

A function can impede the optimizer. You might find that a simple query works better. For instance:

SELECT p.id, p.serial_n, p.product_name, pp.new_pos as position
FROM product p LEFT JOIN
     (SELECT pp.*,
             ROW_NUMBER() OVER (PARTITION BY pp.product_id ORDER BY pp.id DESC) as seqnum
      FROM product_position pp
     ) pp
     ON pp.product_id = p.id AND pp.seqnum = 1;

Or a lateral join:

SELECT p.id, p.serial_n, p.product_name, pp.new_pos as position
FROM product p LEFT JOIN LATERAL
     (SELECT pp.*
      FROM product_position pp
      WHERE pp.product_id = p.id 
      ORDER BY pp.id DESC
      FETCH FIRST 1 ROW ONLY
     ) pp
     ON 1=1;

Regardless, you want an index on product_position(product_id, id, new_pos) for performance.