Re: Slow Running Query

Never mind. I discovered that the outer join is constructed backwards.

Ed Jennings wrote:
>
> 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"

-- 
~~~~~~~~~~~~~~~~~~~~~~~~~
jenningse_at_mindspring.com

"The opinions expressed here are my own, not those of DOMAIN
technologies"