Re: How do you force the order?

From: Paul Bennett <bennett_at_cc.gatech.edu>
Date: Mon, 13 Dec 1999 20:31:34 GMT
Message-ID: <385557C5.C6084DF0@cc.gatech.edu>

I am having trouble articualting my problem. One reason is because I have fixed parts of my problems and other parts have not. Lets try this:

Here is some SQL:

 SELECT
    "USER_EVENT"."EVENT_ID",
"USER_EVENT"."CONTACT_ID",
"USER_EVENT"."CONTACT_ID2",
"USER_EVENT"."CONTACT_TYPE",
"USER_EVENT"."EVENT_DATE",
"USER_EVENT"."EVENT_TIME",
"USER_EVENT"."EVENT_TYPE",
"USER_EVENT"."EVENT_PRIORITY",
"USER_EVENT"."USER_ID",
"USER_EVENT"."STATUS",
"USER_EVENT"."ALARM",
"USER_EVENT"."FROM_ID",
"USER_EVENT"."CONTACT",
"USER_EVENT"."TYPE_CODE",
"USER_EVENT"."EVENT_TEXT",
"USER_EVENT"."TICKLER_TYPE",
"USER_EVENT"."COMPLETE_ID",
"USER_EVENT"."COMPLETE_DATE",
"USER_EVENT"."ORIGINAL_EVENT_DATE",
"USER_EVENT"."PROTECT",

         USER "online_user",
         f_ticker_to(event_id) "TO",
         f_tickler_to_full(Event_id) "PROTECT_LIST"

    FROM user_Event,
(select tickler_id from user_Event_link where employee_id = 'BENNETTP'   union
elect tickler_id from user_Event_link where group_id IN (select group_id from user_Event_group_members a where a.employee_id = 'BENNETTP') ) e
WHERE ( "USER_EVENT"."EVENT_ID" = e.TICKLER_ID ) and "USER_EVENT"."EVENT_DATE" <= SYSDATE
AND user_Event.alarm = 'T'

Now, this takes 1 second to execute. when I add on and user_Event.status = 'A'

it takes 10 seconds because oracle is adding a full table scan of USER_EVENT. Why is this?? It should just apply this rule to the result set already filtered down.

Another issue:

With this SQL:

    SELECT "USER_EVENT"."EVENT_ID",

          "USER_EVENT"."CONTACT_ID",
          "USER_EVENT"."CONTACT_ID2",
          "USER_EVENT"."CONTACT_TYPE",
          "USER_EVENT"."EVENT_DATE",
          "USER_EVENT"."EVENT_TIME",
          "USER_EVENT"."EVENT_TYPE",
          "USER_EVENT"."EVENT_PRIORITY",
          "USER_EVENT"."USER_ID",
          "USER_EVENT"."STATUS",
          "USER_EVENT"."ALARM",
          "USER_EVENT"."FROM_ID",
          "USER_EVENT"."CONTACT",
          "USER_EVENT"."TYPE_CODE",
          "USER_EVENT"."EVENT_TEXT",
          "USER_EVENT"."TICKLER_TYPE",
          "USER_EVENT"."COMPLETE_ID",
          "USER_EVENT"."COMPLETE_DATE",
          "USER_EVENT"."ORIGINAL_EVENT_DATE",
          "USER_EVENT"."PROTECT",
          USER "online_user",
          f_ticker_to(event_id) "TO",
          f_tickler_to_full(Event_id) "PROTECT_LIST"
     FROM    (select tickler_id from user_Event_link where employee_id = 'BENNETTP'

 union
 select tickler_id from user_Event_link where group_id IN (select a.group_id from user_Event_group_members a where a.employee_id = 'BENNETTP')  ) e, user_Event

  WHERE      "USER_EVENT"."EVENT_ID" = e.TICKLER_ID
 AND           "USER_EVENT"."STATUS" = 'A'  AND
           "USER_EVENT"."EVENT_DATE" between '01-JAN-1990' AND '31-DEC-1999'

when I used anlyze table compute statistics this took 300 seconds to execute. when I put in the ORDERD hint, it took 5. when I change it to analyze table esitmate statistics, it takes about 3. this is messed up.

• Paul

Received on Mon Dec 13 1999 - 14:31:34 CST