The /* +RULE */ hint rarely works. Instead, use
ALTER SESSION SET OPTIMIZER_GOAL = RULE;
"charlie.peltier" <charlie.peltier(delete here-antispam)@verizon.net> wrote
in message news:DLdG8.1188$mN3.1171_at_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 Tue May 21 2002 - 14:49:09 CDT
