Indexes ignored

From: Jim Poe <jpoe_at_fulcrumit.com>
Date: Fri, 12 Jul 2002 17:57:38 -0700
Message-ID: <MPG.17991ae19c75bb05989682@news.callatg.com>

SELECT DISTINCT OFFICE_PHN_SRCH.OFFICE_ID, OFFICE_PHN_SRCH.NAME FROM OFFICE_PHN_SRCH WHERE ( UPPER( OFFICE_PHN_SRCH.NBR ) = '7072247700' ) This query takes ~10 seconds to return a result. I would like to make it faster.

I have a combination of views that result in this plan. I have put reverse indexes on all the foreign keys joining these tables. How do I force the use of indexes.

SELECT STATEMENT, GOAL = CHOOSE				1017297
	18641435	1174410405
 SORT UNIQUE							1017297
	18641435	1174410405

  FILTER
   HASH JOIN OUTER

    HASH JOIN OUTER						211353	30383296
	1428014912
     MERGE JOIN CARTESIAN					72982	30383296
	941882176
      VIEW	FIT_DBA					2	164	492
       UNION-ALL					
        TABLE ACCESS FULL	SYS	DUAL	1		82	
        TABLE ACCESS FULL	SYS	DUAL	1		82	
      SORT JOIN						72982	185264
	5187392
       TABLE ACCESS FULL	FIT_DBA	OFFICE		445	185264
	5187392
     VIEW	FIT_DBA	OFFICE_SRCH_OFFICE_PHN	62	33780	540480
      HASH JOIN						62	33780	776940
       TABLE ACCESS FULL	FIT_DBA	PHN		12	24437	366555
       TABLE ACCESS FULL	FIT_DBA	OFFICE_PHN	25	33780	270240
    VIEW	FIT_DBA	OFFICE_SRCH_ROLE_PHN	25	157	2512
     HASH JOIN						25	157	4867
      HASH JOIN						12	157	2512
       TABLE ACCESS FULL	FIT_DBA	ROLE_PHN	1	157	1256
       TABLE ACCESS FULL	FIT_DBA	OFFICE_ROLE	10	16105	128840
      TABLE ACCESS FULL	FIT_DBA	PHN		12	24437	366555

The schema looks like this

OFFICE.OFFICE_ID PK

	OFFICE_ROLE.OFFICE_ID FK to OFFICE (Reverse Index )
	OFFICE.ROLE.ROLE_ID	(Reverse Index )
		ROLE_PHN.ROLE_ID (Reverse Index )
		ROLE_PHN.PHN_ID FK to PHN (Reverse Index )
			PHN.PHN_ID (Reverse Index )

	OFFICE_PHN.OFFICE_ID FK to OFFICE  (Reverse Index )
	OFFICE_PHN.PHN_ID FK to PHN  (Reverse Index )
		PHN.PHN_ID PK
	

Thanks for any help.

-- 
Jim Poe
Fulcrum InteTech, Inc.
<jpoe_at_fulcrumit.com>

Received on Fri Jul 12 2002 - 19:57:38 CDT