AND where clause on key, value structure

by Mohammad Mirzaeyan   Last Updated June 18, 2018 20:06 PM

I have two table that store my assets information in that 2 table , in first table i store the general information about assets and in second i store the properties that belong to the assets ( base on their types for example a Car just have MOTOR_NUM,CHASIS_NUM or Chairs just have a SERIAL_NUMBER) . These are to create table sql statement that define the schema of these tables :

CREATE TABLE assets(
    id number(19) not null ,
    asset_code number(19) not null ,
    description VARCHAR2(400) null ,
    CONSTRAINT assets_pk PRIMARY KEY(id)
); 
create table assets_properties(
    id number(19) ,
    asset_id number(19) ,
    key VARCHAR2(100) ,
        value VARCHAR2(100) ,
    CONSTRAINT assets_pr_pk PRIMARY KEY(id)
);

i have 2 car information in these 2 table and there is sql for insert these 2 car:

insert into assets(id , asset_code , description)
values
    (1 , 1122 , 'this is number 1 car');

insert into assets(id , asset_code , description)
values
    (2 , 1122 , 'this is number 2 car');

insert into assets_properties(id , asset_id , key , value) 

values
    (1 , 1 , 'MOTOR_NUM' , '10002000');

insert into assets_properties(id , asset_id , key , value) 

values
    (2 , 1 , 'CHASSIS_NUM' , 'ABCDE');

insert into assets_properties(id , asset_id , key , value) 

values
    (3 , 2 , 'MOTOR_NUM' , '566775545');

insert into assets_properties(id , asset_id , key , value) 

values
    (4 , 2 , 'CHASSIS_NUM' , 'ABCDE');

i want to fetch a car that : MOTOR_NUM = '10002000' AND CHASSIS_NUM= 'ABCDE' . i think the way that join these 2 table it's not good idea because of data size and bad performance that in real database it's takes too long with join. and with exists i have to write exists per key . how can i fetch it in another way and with perfect performance ?

this is live fiddle for try the best way : http://rextester.com/live/PKXD29609

Tags : sql oracle


Answers 1


There may be better options in Oracle but in most DBMS you'll need composite indexes for this type of queries (EAV) to perform well.

I'd try adding an index on (key, value, asset_id) and then query either with JOIN (or EXISTS):

SELECT
      a.*
FROM 
      assets  a
      JOIN  assets_properties  k1  ON  k1.asset_id = a.id
      JOIN  assets_properties  k2  ON  k2.asset_id = a.id
WHERE
      k1.key = 'MOTOR_NUM'   AND k1.value = '10002000'
  AND k2.key = 'CHASSIS_NUM' AND k2.value = 'ABCDE'
 ;
ypercubeᵀᴹ
ypercubeᵀᴹ
June 18, 2018 19:28 PM

Related Questions



Altering the location of Oracle-Suggested Backup

Updated July 03, 2016 08:02 AM

Ways to Schedule Rman backups

Updated February 24, 2017 12:06 PM

EMCA insists that it cannot connect to the service

Updated August 28, 2017 14:06 PM

Password for Oracle Database 18c user “system”

Updated August 02, 2018 06:06 AM