Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2004
    Location
    Far away from heaven
    Posts
    194

    Unanswered: Really Need Help In Composite Index

    Hi folks, i need an advise.

    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.


    Howdy!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2004
    Location
    Far away from heaven
    Posts
    194
    Thanx, sir, for ur help. It has converted to MERGE join now.



    Howdy!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •