I've a gerand table customers_orders table with customer_id and order_id.
Whenever we have to find orders, for customer, this table is involved. Hey; i know u'll be angry y the heck this gerand exist but i've to blame the older dudes then.
Now this table has composite clustered index; CUSTOMER_ID+ORDER_ID.
The tables have grown over GB size; i see HASH INNER JOIN rather than MERGE for the GEREND and CUSTOMER table join.
Is it good to use composite clustered index; or should i clustered one the columns in the GEREND and other to normal index. What performance impact it could be.
i think you will also need a separate index on order_id alone, if you're joining this table to both the customer table via customer_id and the order table via order_id, and you want orders for more than one customer (e.g. find all customers who have more than $50000 in orders)
joining to the customer table should use the clustered composite index, but joining to the order table would not, so a separate index is necessary