Hints in select with outer join cause order by to fail

From: -J.MIKLEWICZ <jmjm_at_hogpf.ho.att.com>
Date: 1995/04/25
Message-ID: <D7M2nn.824_at_nntpa.cb.att.com>

We are running Oracle 7.0.15 on a SparcCenter 1000 running Solaris 2.3.

We have a database that contains, among others, the following two tables:

CREATE TABLE "REST_STATUS"(
"INCIDENT_NUMBER" NUMBER(10),
"U_VERSION" VARCHAR2(1),
"STATUS" VARCHAR2(10),
"RNM_INITIALS" VARCHAR2(5),
"TIER3_INITIALS" VARCHAR2(5),
"RNM_DATE" DATE,
"REFERED_DATE" DATE,
"TIER3_DATE" DATE,
"CLOSED_DATE" DATE,
"RNC_FILE_STATUS" VARCHAR2(10),
  CONSTRAINT "REST_STATUSP" PRIMARY KEY(
"INCIDENT_NUMBER" )

  USING INDEX TABLESPACE REAINDEX

                PCTFREE 10
                INITRANS 2
                STORAGE(
                        INITIAL 4K
                        NEXT 4K
                        PCTINCREASE 0
                )

  )
  TABLESPACE READATA
  PCTFREE 10
  PCTUSED 90
  INITRANS 1
  MAXTRANS 20
  STORAGE(

        INITIAL 10K
        NEXT 20K
        PCTINCREASE 0

  );  

CREATE TABLE "REST_EVENT"(
"EVENT_NUMBER" NUMBER(10),
"U_VERSION" VARCHAR2(1),
"INCIDENT_NUMBER" NUMBER(10),
"EVENT_NAME" VARCHAR2(50),
"EVENT_TYPE" VARCHAR2(6),
"MANUAL_SUBTYPE" VARCHAR2(20),
"EVENT_STATUS" VARCHAR2(20),
"EVENT_WINDOW_OPEN_TIME" DATE,
"EVENT_START_TIME" DATE,
"REST_END_TIME" DATE,
"EVENT_END_TIME" DATE,
"OUTSUM_FAILED" NUMBER(3),
"OUTSUM_RESTORABLE" NUMBER(3),
"OUTSUM_RESTORED" NUMBER(3),
"INCLUDE" VARCHAR2(1),
"EVENT_FAIL_TIME" DATE,
"EVENT_WINDOW_CLOSE_TIME" DATE,
"EVENT_CLEAR_TIME" DATE,
CONSTRAINT "REST_EVENTP" PRIMARY KEY(
"EVENT_NUMBER" )

  USING INDEX TABLESPACE REAINDEX

                PCTFREE 10
                INITRANS 2
                STORAGE(
                        INITIAL 4K
                        NEXT 4K
                        PCTINCREASE 0
                )

  )
  TABLESPACE READATA
  PCTFREE 10
  PCTUSED 90
  INITRANS 1
  MAXTRANS 20
  STORAGE(

        INITIAL 10K
        NEXT 20K
        PCTINCREASE 0

  )
;    

CREATE INDEX "REST_EVENTI2" ON "REST_EVENT"(
"INCIDENT_NUMBER" )

  TABLESPACE REAINDEX
  PCTFREE 10
  INITRANS 2
  STORAGE(

        INITIAL 4K
        NEXT 4K
        PCTINCREASE 0

  );  

When we try to execute the following query, the order by does not work properly:

select /*+ FIRST_ROWS */ E.event_number from rest_event E, rest_status S
where E.incident_number = S.incident_number(+)
ORDER BY E.EVENT_NUMBER DESC
 

EVENT_NUMB

---

     95255
     95250
     95249
     95248
     95242
     95247
     95253
     95264
       .
       .
       .

If we remove the hint everything is fine:

select E.event_number from rest_event E, rest_status S where E.incident_number = S.incident_number(+) ORDER BY E.EVENT_NUMBER DESC   EVENT_NUMB

---

     95792
     95791
     95787
     95786
     95785
     95784
     95776
     95707
     95681
     95678
       .
       .
       .

The select is actually being generated by a 4GL program called Uniface. No one here is SQL guruish enough to really understand what the Oracle Applications Manual is talking about when it describes how FIRST_ROW hints operate and why it might be messing up in this situation. I suspect that the outer join has something to do with it.

Also, if we drop the index on incident_number in the rest_event table and re-create it, the select with the hint works until we shutdown and restart the database instance!

Anyone have any ideas?

Thanks,

-- 

---------------------------------------------------
| Joe Miklewicz  908-949-3061  jmjm_at_hogpa.att.com |
---------------------------------------------------

Received on Tue Apr 25 1995 - 00:00:00 CEST