The question is:

Where do you need to manage version specific relations and why?

For things like orders and invoices, often you need to know exactly what the customer ordered to ensure you deliver the correct product. So there you need to store the exact version they ordered.

But it's usually less relevant when finding out who supplies which products. Only who supplies what at a given time. So you only want to store product codes to suppliers. The problem here is you can't then have time-independent FKs back to the product code in the history:

create table product_versions (
  product_id   int primary key,
  product_code int not null,
  product_name varchar2(10) not null,
  start_date   date not null,
  end_date     date,
  unique ( product_code, start_date )
);

create table product_suppliers (
  product_code  int not null,
  supplier_code int not null,
  start_date    date not null,
  end_date      date,
  unique ( product_code, supplier_code, start_date )
);

alter table product_suppliers add constraint product_fk 
  foreign key ( product_code ) references product_versions ( product_code );

ORA-02270: no matching unique or primary key for this column-list

I'm not sure what your link_id attribute represents. If this is a product or supplier code, you need to combine it with start_date to make it unique across the table. If it's another value generated for each row it has no extra value over the sequence id.

In any case, I suspect what you need is a single code that applies to several versions of a product. To get around the problem above, create a table to store this and add the relevant foreign keys:

create table products (
  product_code int primary key
);

alter table product_suppliers add constraint prsu_product_fk 
  foreign key ( product_code ) references products ( product_code );
  
alter table product_versions add constraint prve_product_fk 
  foreign key ( product_code ) references products ( product_code );

If you do want to store the specific version of a product a supplier provides and keep this current, you can do so with an API like:

create or replace procedure update_product (
old_prod_id int, product_name int
) as
new_prod_id int := product_seq.nextval ;
new_start_date date := sysdate;
begin
insert into product_versions (
product_id, product_name, start_date
)
select new_prod_id, nvl(update_product.product_name, pv.produce_name), --etc.
new_start_date
from product_versions pv
where pv.product_id = update_product.old_prod_id;

update product_versions pv
set end_date = new_start_date
where product_id = old_prod_id;

update product_suppliers ps
set ps.product_id = new_prod_id
where ps.product_id = old_prod_id
/* Only active rows */
and ps.start_date < sysdate
and ps.end_date is null;

/* ... further updates to non-version specific tables ... */

end update_product;
/</code>

This should be reasonably efficient. If your performance issue relate to something like this API, please provide more details so we can help you, speficially:

- DDL for the tables and indexes involved (create table + create index)
- The DML statements you're running
- Execution plans for these statements. Read this article more details on how to do this:

https://blogs.oracle.com/sql/how-to-create-an-execution-plan