Full Table Scan problem with like

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.

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

I have rerun the table and index analyzed command without any success. Received on Sun Mar 31 2002 - 06:10:46 CEST