This example illustrates a SHOW JOIN INDEX statement for the join index ord_cust_idx.
SHOW JOIN INDEX ord_cust_idx;The statement reports the DDL for the join index:
CREATE JOIN INDEX YourDB.ord_cust_idx ,NO FALLBACK ,CHECKSUM = DEFAULT, MAP = SmallTableMap COLOCATE USING YourDB_ord_cust_idx AS SELECT (YourDB.orders.o_custkey ,YourDB.customer.c_name ) ,(YourDB.orders.o_status , YourDB.orders.o_date ,YourDB.orders.o_comment ) FROM YourDB.orders ,YourDB.customer WHERE YourDB.orders.o_custkey = YourDB.customer.c_custkey PRIMARY INDEX ( o_custkey )