Re: Indexes ignored

Hi Jim,

If I can reply by asking a few questions (the answers to which might answer your question).

Do you have a reason for using the upper function on a numeric (?) NBR column ?

Do you have an index on the NBR column ? (else main query has no way of reading just the required NBR)

Is it a function based index, created as create index index_name on table_name (upper (NBR)); ? Else the upper function negates the use of a normal index.

Do you have a good reason for all your other indexes to be reversed (incrementally increasing values, high insert loads) ? Range scans can't be used which possibly limits their use.

Have all the tables been analyzed if you are using the CBO ?

Good luck

Richard

Is it a
"Jim Poe" <jpoe_at_fulcrumit.com> wrote in message news:MPG.17991ae19c75bb05989682_at_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 - 21:34:17 CDT