Forum Stats

  • 3,734,415 Users
  • 2,246,969 Discussions
  • 7,857,276 Comments

Discussions

How to create an SQL for this scenario?

SebaVasta
SebaVasta Member Posts: 31 Red Ribbon
edited Jun 11, 2021 7:43AM in SQL & PL/SQL

Here is a real challenging SQL for the Gurus. Our DB is 11g R2

I have simplified our issue as follows:

(a.) We have vehicles

(b.) We have addresses (i.e. any address). Address has suburb.

(c.) In order to see which vehicle belongs to whom (actually we use it for everything)

   we use a linking table.


Here is a example set of records:


(1.) We have vehicles. One row for each reg no.


SQL> select * from of_vehicle;

 

  VEH_ID REGISTRATION

---------- -------------------------

    504 peugeot504reg

 


(2.) We have addresses. Other columns in the table will have house no, street etc.


SQL> select * from of_address;

 

  ADD_ID SUBURB_ID

---------- ----------

    111    7777

    222    8888


(3.) We link like this. So, vehicle is owned by person 100 and he is located at address 111.

   And address 111 is in suburb 7777.


SQL> select * from of_gen_links;

 

  GNL_ID MASTER_TABLE  MASTER_ID ASSOCIATION_ID  CHILD_TABLE   CHILD_ID

---------- ------------------------- ----------    --------------- ---------- ------------------------- ----------

     1 person        100 _is_the_owner_of_ vehicle     504

     2 person        100 _has_address_of_ address     111



So, in order to find links between various entities in our system we have this gigantic table with all the links.


Now we have a vehicle query screen (In Oracle Forms 11g) where users can give various query criteria and search for vehicles.


Now the users want to search for vehicles that are in a particular suburb.  


So we added the LOV to select multiple suburbs they have an interest in. If they select 3 suburbs we have to look for vehicles registred in these 3 suburbs

along with the other criteria. So now we have to add a WHERE-CLAUSE to our existing SQL where we plug in this suburb check.


The Query to find a suburb of a vehicle is this (i.e. for a particular vehicle. Here vehicle with ID 504):


SQL>  SELECT *

                FROM (SELECT child_id add_id

                     FROM

                       ( SELECT a.child_id -- Address ID for a vehicle

                         FROM of_gen_links p, of_gen_links a

                         WHERE p.master_table = 'person' AND p.association_id = '_is_the_owner_of_'  AND p.child_table = 'vehicle'

                          AND p.child_id = 504

                          AND a.master_table = 'person' AND a.association_id = '_has_address_of_'  AND a.child_table = 'address'

                          AND a.master_id = p.master_id

                      )

                    ) o,

                 of_address adr

                 WHERE adr.add_id = o.add_id

                  AND adr.suburb_id IN (7777)

  /

 

  ADD_ID   ADD_ID SUBURB_ID

---------- ---------- ----------

    111    111    7777

 

Now, when we plug in this SQL to the existing query it looks like this:


SELECT *

 FROM of_vehicle veh

 WHERE (EXISTS (SELECT NULL

              FROM (SELECT child_id add_id

                   FROM 

                     ( SELECT a.child_id -- Address ID for a vehicle

                       FROM of_gen_links p, of_gen_links a

                       WHERE p.master_table = 'person' AND p.association_id = '_is_the_owner_of_'  AND p.child_table = 'vehicle'

                        AND p.child_id = 504

                        AND a.master_table = 'person' AND a.association_id = '_has_address_of_'  AND a.child_table = 'address'

                        AND a.master_id = p.master_id

                     )

                   ) o,

               of_address adr

               WHERE adr.add_id = o.add_id

                 AND adr.suburb_id IN (7777)

            )

       )

  AND (UPPER(veh.registration) LIKE UPPER('peugeot504reg'));


We have to have it in this format inside the EXISTS-> SELECT NULL and FROM clause query.


Now since this screen searches for all vehicles that matches a particular criteria we need to match the veh.veh_id to the p.child_id.


We can't put p.child_id = veh.veh_id since it gives errror "Invalid identifier" error.


When we try to take the p.child_id to the top to match it against the veh.veh_id we get huge performance issue as the OF_GEN_LINKS table goes to a full table scan and it has 100s of millions of rows.


SELECT *

 FROM of_vehicle veh

 WHERE (EXISTS (SELECT NULL

              FROM (SELECT child_id add_id, veh_id

                   FROM 

                     ( SELECT a.child_id -- Address ID for a vehicle

                            ,p.child_id veh_id 

                       FROM of_gen_links p, of_gen_links a

                       WHERE p.master_table = 'person' AND p.association_id = '_is_the_owner_of_'  AND p.child_table = 'vehicle'

                       -- AND p.child_id = 504

                        AND a.master_table = 'person' AND a.association_id = '_has_address_of_'  AND a.child_table = 'address'

                        AND a.master_id = p.master_id

                     )

                   ) o,

               of_address adr

               WHERE adr.add_id = o.add_id

                 AND adr.suburb_id IN (7777)

                 AND o.veh_id = veh.veh_id --

            )

       )

  AND (UPPER(veh.registration) LIKE UPPER('peugeot504reg'));



How do you solve this issue???


Tables and data:


drop table of_vehicle;

drop table of_address;

drop table of_gen_links;


create table of_vehicle (veh_id  number primary key, registration varchar2(25));

create table of_address (add_id number primary key, suburb_id number);

create table of_gen_links (gnl_id number primary key,

                    master_table varchar2(25),

                    master_id  number,

                    association_id varchar2(25),

                    child_table varchar2(25),

                    child_id number);



insert into of_vehicle (VEH_ID, REGISTRATION) values (504, 'peugeot504reg');                    

                     

insert into of_address (ADD_ID, SUBURB_ID) values (111, 7777);

insert into of_address (ADD_ID, SUBURB_ID) values (222, 8888);


insert into of_gen_links (GNL_ID, MASTER_TABLE, MASTER_ID, ASSOCIATION_ID, CHILD_TABLE, CHILD_ID) values (1, 'person', 100, '_is_the_owner_of_', 'vehicle', 504);

insert into of_gen_links (GNL_ID, MASTER_TABLE, MASTER_ID, ASSOCIATION_ID, CHILD_TABLE, CHILD_ID) values (2, 'person', 100, '_has_address_of_', 'address', 111);

Best Answer

  • James Su
    James Su Member Posts: 1,100 Silver Trophy
    Accepted Answer

    Can you put all the joins into one layer?

    SELECT *

     FROM of_vehicle veh

     WHERE (EXISTS (SELECT NULL

                FROM of_gen_links p, of_gen_links a, of_address adr

                WHERE p.master_table = 'person' AND p.association_id = '_is_the_owner_of_' AND p.child_table = 'vehicle'

                AND p.child_id = veh.veh_id

                AND a.master_table = 'person' AND a.association_id = '_has_address_of_' AND a.child_table = 'address'

                AND a.master_id = p.master_id

                AND adr.add_id = a.child_id

                AND adr.suburb_id IN (7777)

              ) 

          )

     AND (UPPER(veh.registration) LIKE UPPER('peugeot504reg'));  

    SebaVasta

Answers

  • James Su
    James Su Member Posts: 1,100 Silver Trophy
    Accepted Answer

    Can you put all the joins into one layer?

    SELECT *

     FROM of_vehicle veh

     WHERE (EXISTS (SELECT NULL

                FROM of_gen_links p, of_gen_links a, of_address adr

                WHERE p.master_table = 'person' AND p.association_id = '_is_the_owner_of_' AND p.child_table = 'vehicle'

                AND p.child_id = veh.veh_id

                AND a.master_table = 'person' AND a.association_id = '_has_address_of_' AND a.child_table = 'address'

                AND a.master_id = p.master_id

                AND adr.add_id = a.child_id

                AND adr.suburb_id IN (7777)

              ) 

          )

     AND (UPPER(veh.registration) LIKE UPPER('peugeot504reg'));  

    SebaVasta
  • SebaVasta
    SebaVasta Member Posts: 31 Red Ribbon

    Actually I can't put all joins into one layer since there are unions and order by and rowcount involved. So, I put it in a function and called it. I don't get the performance issue when using the function. So going with it.

Sign In or Register to comment.