CREATE TABLE optional_info_a(
   optional_info_a_id    NUMBER
  ,optional_info_a_attr1 VARCHAR2(100)
  ,optional_info_a_attr2 VARCHAR2(100)
  ,optional_info_a_attr3 VARCHAR2(100)
  ,optional_info_a_attr4 VARCHAR2(100)
);
ALTER TABLE optional_info_a ADD CONSTRAINT optional_info_a_pk PRIMARY KEY (optional_info_a_id);


CREATE TABLE optional_info_b(
   optional_info_b_id    NUMBER
  ,optional_info_b_attr1 VARCHAR2(100)
  ,optional_info_b_attr2 VARCHAR2(100)
);
ALTER TABLE optional_info_b ADD CONSTRAINT optional_info_b_pk PRIMARY KEY (optional_info_b_id);


CREATE TABLE optional_info_c(
   optional_info_c_id    NUMBER
  ,optional_info_c_attr1 VARCHAR2(100)
  ,optional_info_c_attr2 VARCHAR2(100)
  ,optional_info_c_attr3 VARCHAR2(100)
);
ALTER TABLE optional_info_c ADD CONSTRAINT optional_info_c_pk PRIMARY KEY (optional_info_c_id);


CREATE TABLE main_table(
   main_table_id      NUMBER
  ,main_type          NUMBER
  ,main_attr1         VARCHAR2(100)
  ,main_attr2         VARCHAR2(100)
  ,optional_info_a_id NUMBER
  ,optional_info_b_id NUMBER
  ,optional_info_c_id NUMBER);
ALTER TABLE main_table ADD CONSTRAINT main_table_pk PRIMARY KEY (main_table_id);
ALTER TABLE main_table ADD CONSTRAINT main_table_a_fk FOREIGN KEY (optional_info_a_id) REFERENCES optional_info_a;
ALTER TABLE main_table ADD CONSTRAINT main_table_b_fk FOREIGN KEY (optional_info_b_id) REFERENCES optional_info_b;
ALTER TABLE main_table ADD CONSTRAINT main_table_c_fk FOREIGN KEY (optional_info_c_id) REFERENCES optional_info_c;
CREATE UNIQUE INDEX main_table_u1 ON main_table(optional_info_a_id);
CREATE UNIQUE INDEX main_table_u2 ON main_table(optional_info_b_id);
CREATE UNIQUE INDEX main_table_u3 ON main_table(optional_info_c_id);

SELECT *
FROM main_table mt
LEFT JOIN optional_info_a a ON a.optional_info_a_id = mt.optional_info_a_id
LEFT JOIN optional_info_b b ON b.optional_info_b_id = mt.optional_info_b_id
LEFT JOIN optional_info_c c ON c.optional_info_c_id = mt.optional_info_c_id

CREATE TABLE main_table(
   main_table_id         NUMBER
  ,main_type             NUMBER
  ,main_attr1            VARCHAR2(100)
  ,main_attr2            VARCHAR2(100)
  ,optional_info_a_attr1 VARCHAR2(100)
  ,optional_info_a_attr2 VARCHAR2(100)
  ,optional_info_a_attr3 VARCHAR2(100)
  ,optional_info_a_attr4 VARCHAR2(100)
  ,optional_info_b_attr1 VARCHAR2(100)
  ,optional_info_b_attr2 VARCHAR2(100)
  ,optional_info_c_attr1 VARCHAR2(100)
  ,optional_info_c_attr2 VARCHAR2(100)
  ,optional_info_c_attr3 VARCHAR2(100)
);