Join indexes can be created by using CREATE JOIN INDEX statements on Multitable.

BTEQ -- Enter your DBC/SQL request or BTEQ command:
create join index all_chemical as select (organic_name, carbon_number),
(inorgnic_name, cation, anion) from organic inner join inorganic
on organic_name = inorgnic_name;

 *** Index has been created.
 *** Total elapsed time was 2 seconds.

Join indexes can be created by using CREATE JOIN INDEX statements on Single table.

BTEQ -- Enter your DBC/SQL request or BTEQ command:
create join index chem_name as select organic_name from organic;

 *** Index has been created.
 *** Total elapsed time was 2 seconds.

BTEQ -- Enter your DBC/SQL request or BTEQ command:
create join index ion as select anion, cation from inorganic;

 *** Index has been created.
 *** Total elapsed time was 1 second.

It is better to define a primary index when creating join index.

BTEQ -- Enter your DBC/SQL request or BTEQ command:
create join index all_chemicals as select (organic_name, carbon_number),
(inorgnic_name, cation, anion) from organic inner join inorganic
on organic_name = inorgnic_name primary index (organic_name);

 *** Index has been created.
 *** Total elapsed time was 2 seconds.

Secondary index can be defined on top of a join index.

BTEQ -- Enter your DBC/SQL request or BTEQ command:
create index inorg all (anion, cation) on all_chemicals;

 *** Index has been created.
 *** Total elapsed time was 2 seconds.

Join indexes can be dropped by using DROP JOIN INDEX statements.

BTEQ -- Enter your DBC/SQL request or BTEQ command:
drop join index all_chemical;

 *** Index has been dropped.
 *** Total elapsed time was 2 seconds.

BTEQ -- Enter your DBC/SQL request or BTEQ command:
drop join index chem_name;

 *** Index has been dropped.
 *** Total elapsed time was 2 seconds.