OR Condition in Oracle SQL

Question

We have inherited a set of tables from a third-party product. One of the tables has 2 foreign key constraints that link to the same column on the parent table. I have simplified the tables to demonstrate this:

CREATE TABLE profile_defn 
(   
  profile_id NUMBER(10,0), 
  profile_name  VARCHAR2(50),
  CONSTRAINT pk1 PRIMARY KEY (profile_id)
);

CREATE TABLE letter_defn 
(
  letter_defn_id  NUMBER(10,0), 
  letter_name     VARCHAR2(50 BYTE), 
  orig_print_id   NUMBER(10,0),
  new_print_id    NUMBER(10,0),
  CONSTRAINT fk1 FOREIGN KEY (orig_print_id) REFERENCES profile_defn (profile_id) ENABLE,
  CONSTRAINT fk2 FOREIGN KEY (new_print_id) REFERENCES profile_defn (profile_id) ENABLE
);

INSERT INTO profile_defn
VALUES
(
  1,
  'profile1'
);

INSERT INTO profile_defn
VALUES
(
  2,
  'profile2'
);

INSERT INTO profile_defn
VALUES
(
  3,
  'profile3'
);

INSERT INTO letter_defn
VALUES 
(
  1,
  'letter1',
  1,
  2
);

INSERT INTO letter_defn
VALUES 
(
  1,
  'letter2',
  2,
  3
);

Consequently when joining the 2 tables there is an OR condition as it needs to identify matching records on either column:

SELECT * FROM letter_defn ld
JOIN profile_defn p 
ON ld.orig_print_id = p.profile_id OR ld.new_print_id = p.profile_id;

Are there are any consequences of using OR in the JOIN or is there a 'better' way of doing it?

Thanks.

Answer 1

An alternative will be to use UNION or UNION ALL which might have better execution plan :

SELECT * FROM letter_defn ld 
JOIN profile_defn p ON ld.orig_print_id = p.profile_id 
UNION
SELECT * FROM letter_defn ld 
JOIN profile_defn p ON ld.new_print_id = p.profile_id;

The consequences is that or is usually slower because the compiler can no longer perform an index seek. Other then that, it's ok .