Make query from multiple tables shorter and optimized

Question

I am using PreparedStatement in Java. I have a sql-query from multiple tables. If it's possible, how can i make this query much shorter and optimized? DB is "Oracle 12c database". Also my task is to make the query sql-injection safe and to prevent injection.

preparedStatement = connection.prepareStatement("delete from " +
            "TBL_ORG_DATA, " +
            "TBL_ORG_CONTACTS, " +
            "TBL_HEALTH_SERVICE, " +
            "TBL_EDUCATION, " +
            "TBL_LENGTH_WORK, " +
            "TBL_CONTRACTS, " +
            "TBL_EVENTS, " +
            "TBL_TRADE_UNIONS, " +
            "TBL_COMMITTEES, " +
            "TBL_HEALTH_CHECK, " +
            "TBL_ACCIDENTS, " +
            "TBL_VICTIMS, " +
            "TBL_ACCIDENTS_DAMAGE, " +
            "TBL_ATTESTATION, " +
            "TBL_ATTESTATION_FIVE_YEAR, " +
            "TBL_TRANING, " +
            "TBL_TRAINING_MANAGER, " +
            "TBL_TRANING_THREE_YEAR, " +
            "TBL_MANAGEMENT " +
            "where TBL_ORG_DATA.ORG_ID = ? AND\n" +
            "TBL_ORG_CONTACTS.ORG_ID = ? AND\n" +
            "TBL_HEALTH_SERVICE.ORG_ID = ? AND\n" +
            "TBL_EDUCATION.ORG_ID = ? AND\n" +
            "TBL_LENGTH_WORK.ORG_ID = ? AND\n" +
            "TBL_CONTRACTS.ORG_ID = ? AND\n" +
            "TBL_EVENTS.ORG_ID = ? AND\n" +
            "TBL_TRADE_UNIONS.ORG_ID = ? AND\n" +
            "TBL_COMMITTEES.ORG_ID = ? AND\n" +
            "TBL_HEALTH_CHECK.ORG_ID = ? AND\n" +
            "TBL_ACCIDENTS.ORG_ID = ? AND\n" +
            "TBL_VICTIMS.ORG_ID = ? AND\n" +
            "TBL_ACCIDENTS_DAMAGE.ORG_ID = ? AND\n" +
            "TBL_ATTESTATION.ORG_ID = ? AND\n" +
            "TBL_ATTESTATION_FIVE_YEAR.ORG_ID = ? AND\n" +
            "TBL_TRANING.ORG_ID = ? AND\n" +
            "TBL_TRAINING_MANAGER.ORG_ID = ? AND\n" +
            "TBL_TRANING_THREE_YEAR.ORG_ID = ? AND\n" +
            "TBL_MANAGEMENT.ORG_ID = ? AND\n" +                
            "TBL_ORG_DATA.DATE_PERIOD = ? AND\n" +
            "TBL_ORG_CONTACTS.DATE_PERIOD = ? AND\n" +
            "TBL_HEALTH_SERVICE.DATE_PERIOD= ? AND\n" +
            "TBL_EDUCATION.DATE_PERIOD= ? AND\n" +
            "TBL_LENGTH_WORK.DATE_PERIOD = ? AND\n" +
            "TBL_CONTRACTS.DATE_PERIOD = ? AND\n" +
            "TBL_EVENTS.DATE_PERIOD = ? AND\n" +
            "TBL_TRADE_UNIONS.DATE_PERIOD = ? AND\n" +
            "TBL_COMMITTEES.DATE_PERIOD = ? AND\n" +
            "TBL_HEALTH_CHECK.DATE_PERIOD = ? AND\n" +
            "TBL_ACCIDENTS.DATE_PERIOD = ? AND\n" +
            "TBL_VICTIMS.DATE_PERIOD = ? AND\n" +
            "TBL_ACCIDENTS_DAMAGE.DATE_PERIOD = ? AND\n" +
            "TBL_ATTESTATION.DATE_PERIOD = ? AND\n" +
            "TBL_ATTESTATION_FIVE_YEAR.DATE_PERIOD = ? AND\n" +
            "TBL_TRANING.DATE_PERIOD = ? AND\n" +
            "TBL_TRAINING_MANAGER.DATE_PERIOD = ? AND\n" +
            "TBL_TRANING_THREE_YEAR.DATE_PERIOD = ? AND\n" +
            "TBL_MANAGEMENT.DATE_PERIOD = ?");

These many table in `from` section and now joining clause in `where`. Doesn't seem right to me. Are these table related to each other in some way — Ashishkumar Singh, Aug 01 '18 at 07:26
Sorry, i mistook. In this query i should to use "delete" instead "select". Theese tables have the same fields: ORG_ID, DATE_PERIOD. — Vladimir, Aug 01 '18 at 07:36
What is the problem with your current `DELETE` that needs solving? — Mick Mnemonic, Aug 01 '18 at 07:40
@MickMnemonic Query is huge and strings repeats many times. Is exist an approach to remake this query? — Vladimir, Aug 01 '18 at 08:52
You're deleting data from ~20 tables so yes, the SQL will be lengthy. I would personally split these into separate DELETEs but without knowing anything about your DB schema it's hard to give suggestions. — Mick Mnemonic, Aug 01 '18 at 09:57

score 0 · Accepted Answer · answered Aug 01 '18 at 10:55

As far as I know in Oracle You have to create separate delete command for each table.

delete from a where org_id = 101 and date_period = date '2018-05-25';
delete from b where org_id = 101 and date_period = date '2018-05-25';

If you can modify tables then you could maybe use constraint on delete cascade. I do not know how your tables are organized, but if in one of them you can define unique constraint then rest may depend on it, like here:

create table a (
    id number primary key, org_id number, date_period date, cola varchar2(5),
    constraint uq_org_id_period unique (org_id, date_period) );
insert into a values (1, 101, date '2018-05-25', 'DEL');
insert into a values (2, 101, date '2018-05-26', 'PQR');
insert into a values (3, 102, date '2018-06-17', 'CBA');

create table b (
    id number primary key, org_id number, date_period date, colb varchar2(5),
    constraint fk_org_id_period 
        foreign key (org_id, date_period) 
        references a(org_id, date_period) on delete cascade);
insert into b values (1, 101, date '2018-05-26', 'AAXXX');
insert into b values (2, 102, date '2018-06-17', 'PCCQR');
insert into b values (3, 101, date '2018-05-25', 'DEL1');
insert into b values (4, 101, date '2018-05-25', 'DEL2');
insert into b values (5, 102, date '2018-06-17', 'XYZYX');

Now if you run:

delete from a where (org_id, date_period) in ((101, date '2018-05-25'));

not only row 1 from table a will be deleted but also rows 3 and 4 from b. You could also use triggers, but constraint is more appropriate here.

Thanks for your response. Unfortunately, applying "on cascade delete" is not impossible in my db. — Vladimir, Aug 01 '18 at 12:26

Make query from multiple tables shorter and optimized

1 Answers1