Home » SQL & PL/SQL » SQL & PL/SQL » Haywire Indexes
Haywire Indexes [message #210624] Thu, 21 December 2006 10:01 Go to next message
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 Go to previous messageGo to next message
michael_bialik
Messages: 611
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 Go to previous message
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
Previous Topic: File In Ftp
Next Topic: Transaction log
Goto Forum:
  


Current Time: Fri Dec 02 14:10:45 CST 2016

Total time taken to generate the page: 0.09584 seconds