We have the INVENTORY table. This is table has lots of columns. Currently in production there are over 55 million rows in this table.
Our front-end java application has to do a validation when a user tries to delete a particular row (from another table) and stop delete if validation fails.
This other table is related to the INVENTORY table, through many other tables.
We want to stop the delete of this other table's row if that other tables row has been linked to an INVENTORY row.
This is a sort of a foreign key validation done manually by us because our tables are not that normalized. OK, we use EAV + some un-normalized data structures.
We have tables without any FKs or child tables. That is, if you put these tables on an ER diagram, they will not have any relationship lines to other tables.
So , this is a sort of a complex BUSINESS RULE validation we can put inside trigger to validate data where foreign keys are just not enough to validate data.
But, instead of inside a trigger, the this is done by the Java front end. The Java dev wants an SQL to paste in his Java program. So, this is where we need an SQL.
I have simplified our requirement like this:
Our inventory table is this:
create table inv (invid number primary key, a varchar2(25));
Column a is the column we need to validate on. So in real life, there are 2 big sub-queries to get the column a values which are linked to other tables rows.
I have inserted this data:
insert into inv (INVID, A) values (1, 'link 1');
insert into inv (INVID, A) values (2, 'link 2');
insert into inv (INVID, A) values (3, 'no link');
insert into inv (INVID, A) values (4, 'link 1');
So, our table is like this:
SQL> select * from inv
2 /
INVID A
---------- -------------------------
1 link 1
2 link 2
3 no link
4 link 1
The other tables:
SQL> create table other_table1 (f1 varchar2(20) primary key);
Table created
SQL> create table other_table2 (f2 varchar2(20) primary key);
Table created
SQL> Insert into other_table1 values ('link 1');
1 row inserted
SQL> Insert into other_table2 values ('link 2');
1 row inserted
So my SQL to give Java Dev is:
select count(*)
from inv
where
(
a in (select f1 from other_table1)
or
a in (select f2 from other_table2)
)
and rownum < 2;
I CAN create the SQL, but the thing is, since there are 55 million rows in the production database, I need to maximize performance of this query.
Is there way to write this query in such a way that if only 1 of the sub-queries in the above returns at least one row then we stop the SQL. Because if there is at least one link through at least one path, then we can stop delete.
For example, we evaluate other_table2 sub-query ONLY if other_table1 sub-query returns 0 rows.
Is this possible in ONE SQL? That is, I want to stop the evaluation of one sub-query altogether, if at least another sub-query returns at least 1 row. Since only 1 sub-query is executed by the SQL engine, surely that can improve performance a lot.