Help: Strange sql behaviour

From: charlie.peltier <here-antispam)_at_verizon.net>
Date: Mon, 20 May 2002 21:19:31 GMT
Message-ID: <DLdG8.1188$mN3.1171@nwrddc02.gnilink.net>

We are using 817 on Solaris
We have three tables join. One table is like below.

SQL> desc life

 Name                                      Null?    Type
 ----------------------------------------- -------- ------------------------
----
 DASET_ID                                  NOT NULL NUMBER(10)
 SECT_ID                                   NOT NULL NUMBER(10)
 LIFE_ID                                   NOT NULL NUMBER(10)
 VERSION_ID                                NOT NULL VARCHAR2(10)
 METRO_ID                                  NOT NULL NUMBER
 RAMP                                      NOT NULL VARCHAR2(1)
 GEOLOC                                             MDSYS.SDO_GEOMETRY
 SPEED                                              NUMBER
 STATUS_DESC                                        VARCHAR2(25)
 EDGE_ID                                            NUMBER(10)

We use choose as our optimizer mode. But after we analyze table life, the sql begin to use full table scan, even if we put rule as hint. And the sql is not complicated at all. After we delete the statistics on the table life, everything is working fine again.

Why? I thought for rule hint, oracle will use index no matter what cost is, am I right? And I checked several times that the hint was taken, there is not syntax error.

Thanks for your help.
select

  ln.DASET_ID daset,
  ln.SECT_ID sect,
  ln.LIFE_ID life,
  ln.VERSION_ID ver,
  ln.UPSTREAM_DASET_ID updaset,
  ln.UPSTREAM_SECT_ID upsect,
  ln.UPSTREAM_POINT_ID upid,
  ln.UPSTREAM_VERSION_ID uver,
  ln.DOWNSTREAM_DASET_ID dpdaset,
  ln.DOWNSTREAM_SECT_ID dpsect,
  ln.DOWNSTREAM_POINT_ID dpid,
  ln.DOWNSTREAM_VERSION_ID dver,
  ln.REFNODE refn,

  l.EDGE_ID edge,
  l.GEOLOC.SDO_ORDINATES geom,
  ld.DIRECTION direction
from
  LIFE_NODE_XREF ln,
  LIFE l,
  LIFE_DIRECTION ld
where

  ln.METRO_ID = 2 and
  ln.STATUS_DESC = 'ACTIVE' and
  ln.DASET_ID = ld.DASET_ID (+) and
  ln.SECT_ID = ld.SECT_ID (+) and
  ln.LIFE_ID = ld.LIFE_ID (+) and
  ln.VERSION_ID = ld.VERSION_ID (+) and
  ln.DASET_ID = l.DASET_ID and
  ln.SECT_ID = l.SECT_ID and
  ln.LIFE_ID = l.LIFE_ID and
  ln.VERSION_ID = l.VERSION_ID and

  l.STATUS_DESC = 'ACTIVE' and
  l.RAMP = 'Y' and
  ln.LIFE_ID != 828552

In the above three tables, we can only analyze two tables, life table can not be analyzed Received on Mon May 20 2002 - 16:19:31 CDT