Slow Running Query

From: Ed Jennings <jenningse_at_mindspring.com>
Date: Mon, 24 Aug 1998 17:17:02 -0400
Message-ID: <35E1D84E.45C5F7BB_at_mindspring.com>

[Quoted] I have a view (described below) that is performing *VERY* poorly. Table A contains transaction data, with Tables B & C containing data on the sender and receiver. (The outer-join is necessary because a valid transaction can have a NULL sender) I have many users that query this view very often looking for recent (last week) transactions for a specific broker. It takes upward of 15 minutes to retrieve a few hundred rows. The query looks like:

  select * from activity_vw
  where activity_date >= to_date('15-AUG-98', 'DD-MON-YY')     and broker_id = 5;

I've been playing with the DDL for the view, but have been unable to get any significant performance increase. I'm using a view because the application is FORMS, and the form was easier to create with the underlying data_block a view rather than 3 tables. With that said, I'm not opposed to changing the form if that's what it takes to get decent performance. Anyone have any ideas about what may speed up the query?

TIA Ed Jennings

---

CREATE OR REPLACE VIEW ACTIVITY_VW
 (ACTIVITY_DATE,
  RECEIVER,
  SENDER,
  SENDER_DATA,
  BROKER_ID,
  RECEIVER_DATA)
 AS SELECT

        A.ACTIVITY_DATE,
        A.RECEIVER,
        A.SENDER,
        B.DATA,
        C.BROKER_ID,
        C.DATA
 FROM TABLE_A A,
      TABLE_B B,
      TABLE_C C

 WHERE A.SENDER (+) = B.SENDER
   AND A.RECEIVER = C.RECEIVER; TABLE A (700,000 rows)

Name                  Null?    Type


--------------------- -------- ----


ACTIVITY_DATE         NOT NULL DATE
RECEIVER              NOT NULL VARCHAR2(15)
SENDER                         VARCHAR2(15)

...
--UNIQUE INDEX = ACTIVITY_DATE, SENDER, RECEIVER
--NON-UNIQUE INDEX = RECEIVER
--NON-UNIQUE INDEX = SENDER
TABLE B (5,000 rows)

Name                  Null?    Type


--------------------- -------- ----


SENDER                NOT NULL VARCHAR2(15)     --PRIMARY KEY
DATA                           VARCHAR2(100)

...

TABLE C (40,000 rows)

Name                  Null?    Type


--------------------- -------- ----


RECEIVER              NOT NULL VARCHAR2(15)     --PRIMARY KEY
BROKER_ID                      NUMBER           --NON-UNIQUE INDEX
DATA                           VARCHAR2(100)

...

---

--

---

jenningse_at_mindspring.com

"The opinions expressed here are my own, not those of DOMAIN technologies" Received on Mon Aug 24 1998 - 23:17:02 CEST