| Haywire Indexes [message #210624] |
Thu, 21 December 2006 10:01  |
bswarmer
Messages: 5
Registered: December 2006
|
Junior Member |
|
|
Howdy all,
I have a good one here that I need some help with.
I have one massive table (TABLE_A) with approx 125 million rows and over 350 columns. This table has 7 individual phone numbers that I will be joining to and each phone column has its own bitmap index. ** Each phone is more sparsely populated the further up you go. Phone 1 contains almost 60 Million values and Phone 7 has less than 5,000.
I have another table (TABLE_B) that I am joining to it that contains anywhere from 100 to 10000 rows. This table has one phone number column that is also indexed.
SELECT A.CUSTOMER
,B.PHONE_NBR
,1 AS INDIV_NBR
FROM TABLE_A A
,TABLE_B B
WHERE A.PHONENUM_1 = B.PHONE_NBR
UNION
SELECT A.CUSTOMER
,B.PHONE_NBR
,2 AS INDIV_NBR
FROM TABLE_A A
,TABLE_B B
WHERE A.PHONENUM_2 = B.PHONE_NBR
UNION .... DOWN THRU 7
This works fine until I get down to the 5th statement (PHONE_5). The explain plan (below) now shows that the query is going to use the index for phone_7 and the performance is not so good. I have found a work around by adding "AND A.PHONE_7 = A.PHONE_7" to the WHERE clause and it actually improves performance (from 10 minutes to .02 seconds). This does not always work though. The 7th statement actually appears to be doing a FTS.
SELECT STATEMENT, GOAL = ALL_ROWS Cost=152295 Cardinality=4492 Bytes=300964
HASH JOIN Cost=152295 Cardinality=4492 Bytes=300964
BITMAP CONVERSION TO ROWIDS Cost=5 Cardinality=2466 Bytes=27126
BITMAP INDEX FAST FULL SCAN Object owner=TABLE_B Object name=LOAD_2_NDX_P
TABLE ACCESS BY INDEX ROWID Object owner=TABLE_A Object name=TABLE_A Cost=152281 Cardinality=399736 Bytes=22385216
BITMAP CONVERSION TO ROWIDS
BITMAP MINUS
BITMAP MERGE
BITMAP INDEX FULL SCAN Object owner=TABLE_A Object name=PN_7_BIDX
BITMAP INDEX SINGLE VALUE Object owner=TABLE_A Object name=PN_5_BIDX
So I really have two questions here. Why would the optimizer opt to use the index tied to a column that is not even in the query. And why is the index not used on Phone 7 when is seems very logically to me that an index would be faster than a full table scan.
I have attempted to use tips, rebuilt all indexes, and I have been researching this off and on for over a week. This seems very jacked up to me. Any input would be greatly appreciated.
Thanks
bswarmer
|
|
|
|
| Re: Haywire Indexes [message #210636 is a reply to message #210624] |
Thu, 21 December 2006 11:10  |
michael_bialik
Messages: 621
Registered: July 2006
|
Senior Member |
|
|
Try to force the join to be performed from Table_B to _A:
SELECT /*+ ORDERED USE_NL(A) */
A.CUSTOMER ,B.PHONE_NBR ,2 AS INDIV_NBR
FROM TABLE_B B, TABLE_A A /* I reversed the table order */
WHERE A.PHONENUM_2 = B.PHONE_NBR
HTH.
|
|
|
|
| Re: Haywire Indexes [message #210679 is a reply to message #210636] |
Thu, 21 December 2006 14:07 |
bswarmer
Messages: 5
Registered: December 2006
|
Junior Member |
|
|
That is awesome, It worked like a charm. Can't say that I fully understand why, but you have provided me with a great start. Thank you!
bswarmer
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
