join indices

join indices

Post by Doug Cowle » Wed, 10 Feb 1999 04:00:00



Does Oracle support join indices or any variation
thereof?
 
 
 

1. Query/Join/Index Question

SS 6.5 Question.

I have a Query that joins a PK "OrderID" in table "Orders"
to an indexed field "OrderID" in the "Items" table. For
all intents and purposes, "Items.OrderID" is the FK
to "Orders.OrderID", but for reasons I won't get into
here, we can't set anything like that up *grumble*.

Now, within both tables I have duplicate fields
("ProductID", "CustomerID", "CustomerGroupID"). Yes, this
duplicative information is painfully "un-relational" (I
inherited this, I didn't build it). To illustrate:

Orders
------
OrderID (PK - Long)
ProductID (Long)
CustomerID (Long)
CustomerGroupID (Long)
OrderDate (datetime)

Items
------
ItemID (PK - Long)
OrderID (FK - Long [but not implemented as FK])
ProductID (Long)
CustomerID (Long)
CustomerGroupID (Long)

Now, my Query is (in essence):

SELECT I.ItemID
FROM Orders O, Items I
WHERE O.OrderID = I.OrderID
AND O.OrderDate='9/9/02'

Due to the size of both tables (Orders and Item), this is
not fast enough in some instances. Now, my question: Would
there be any theoretical impact (positively or negatively)
by adding the following to the WHERE clause of my query?

AND O.ProductID = I.ProductID
AND O.CustomerID = I.CustomerID
AND O.CustomerGroupID = I.CustomerGroupID

The information in those columns will ALWAYS be the same
for a particular OrderID. Will this help? Personally,
since I'm joining on OrderID already, I can't see how it
would possibly speed anything up. In fact, I suspect that
we'd slow down even more, but I'm being told by a more
senior member of my team that I should add this to improve
performance. By the way, both tables have individual
indexes and a group index on "ProductID", "CustomerID"
and "CustomerGroupID".

Thanks in advance,

Sean

2. Admin Issues for resale products

3. Join / index usage

4. CAB File size limitation.

5. Star join index

6. Paradox 8 to SQL server

7. Join Indexes?

8. Outer join, index failure

9. Outer join / index loss - strange behaviour

10. Outer join, index failure

11. Left Join Index Question

12. NEEDED: BETTER JOIN PERFORMANCE ON BIG TABLE WITH BIG INDEXES (NESTED LOOPS AND MERGE JOINS)