Re: Full Table Scan problem with like

From: Steve M <steve.mcdaniels_at_vuinteractive.com>
Date: Wed, 3 Apr 2002 12:17:36 -0800
Message-ID: <a8fnu3$hvv$1_at_spiney.sierra.com>


I don't know the "fix" for your problem
but where clauses that look like:

   FIELD_NAME = '...%...' will definitely not have the LIKE behaviour you're after. (the equal sign does not honor your LIKE desires...)

"Andrew Ryals" <aryals_at_seismicexchange.com> wrote in message news:ef797a81.0203302010.13a4c1d0_at_posting.google.com...
> I recently converted to 9i. I cannot figure why I get a full table
> scan on a child table when using the 'like' condition on the primary
> table. I have listed the SQL and Explain plans below.
>
> ***********************************************
> Without using like:
>
> SELECT ls.linesegname
> FROM survey s, phases p, line_segment ls
> WHERE
> p.surveyid = s.surveyid
> and ls.phaseid = p.phaseid
> AND ls.linesegname = 'BRA%';
>
> Explain:
>
> NESTED LOOPS
> NESTED LOOPS
> TABLE ACCESS BY INDEX ROWID LINE_SEGMENT
> INDEX RANGE SCAN IDX_LINESEG_LINESEGNAME
> TABLE ACCESS BY INDEX ROWID PHASES
> INDEX UNIQUE SCAN PK_PHASES
> INDEX UNIQUE SCAN PK_SURVEY
>
> ***********************************************
>
> Using Like:
>
> SELECT ls.linesegname
> FROM survey s, phases p, line_segment ls
> WHERE
> p.surveyid = s.surveyid
> and ls.phaseid = p.phaseid
> AND ls.linesegname like 'BRA%';
>
> Explain:
>
> NESTED LOOPS
> HASH JOIN
> TABLE ACCESS BY INDEX ROWID LINE_SEGMENT
> INDEX RANGE SCAN IDX_LINESEG_LINESEGNAME
> TABLE ACCESS FULL PHASES
> INDEX UNIQUE SCAN PK_SURVEY
>
> ***********************************************
>
> I have rerun the table and index analyzed command without any success.
Received on Wed Apr 03 2002 - 22:17:36 CEST

Original text of this message