CODEWITHSUNDEEP
sqloracle-database
user7017392
user7017392

Reputation: 1

Checking existence of row with specific attributes in another table

I have two tables with one to many relation, like this:

MY_TABLE(ID, VALUE)
MY_TABLE_ATTRIBUTE(ID, MY_TABLE_ID, NAME, VALUE)

I want to check if there is record in MY_TABLE with particular attributes.

This query will probably explain what I am doing:

select 1 
from MY_TABLE mt
where exists (select 1 
              from MY_TABLE_ATTRIBUTE mta 
              where mta.my_table_id = mt.id
                and (mta.name = 'attributeName1' and mta.value = 'attributeValue1'))
  and exists (select 1 
              from MY_TABLE_ATTRIBUTE mta 
              where mta.my_table_id = mt.id
                and (mta.name = 'attributeName2' and mta.value = 'attributeValue2'))
  and exists ...
.
.
.

My question is if there is a better (faster) way to check that existence.

Thanks in advance

Upvotes: 0

Views: 721

Answers (2)

gsalem
gsalem

Reputation: 2028

Try the following rewrites:

    select * from MY_TABLE mt
    where exists (select 1 from MY_TABLE_ATTRIBUTE mta 
                where mta.my_table_id = mt.id
                and(
                    (mta.name = 'attributeName1' and mta.value = 'attributeValue1')
                    OR (mta.name = 'attributeName2' and mta.value = 'attributeValue2')
                    OR ....
    )

or

    select * from MY_TABLE mt
    where mt.id in (select mta.my_table_id from MY_TABLE_ATTRIBUTE mta 
                where 
                    (mta.name = 'attributeName1' and mta.value = 'attributeValue1')
                    OR (mta.name = 'attributeName2' and mta.value = 'attributeValue2')
                    OR ....
    )

An index on (name,value) may help if the cardinality of these columns is very high.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269483

Your query is fine. For performance, you want the following index:

create index idx_my_table_attribute_3 on my_table_attribute(my_table_id, name, value);

In fact, with this index, your code is probably the fastest way to implement this logic in Oracle.

Upvotes: 1

Related Questions