Re: Need help with improving performance of a query.

Ronnie wrote:
> Hi,
>
> Oracle Database Version 8.1.7.0.0
>
> I have a table with 30,00 records and the structure is
>
> SQL> desc int_buyer_seller
> Name Type Nullable Default Comments
> -------------- ------------- -------- ------- --------
> TRANSACTION_ID NUMBER
> COMPANY_ID NUMBER
> TRANS_ROLE_ID NUMBER
> ADVISOR_ID NUMBER Y
> RATIONALE_DESC VARCHAR2(100) Y
>
> The table has a composite primary primary key on the
> Transaction_Id, Company_Id and the Trans_Role_Id columns. Additionaly
> I created an Index on the Transaction_Id and Company_Id column.
>
> I have a query which takes about 20 seconds to run
>
> SQL> Select transaction_id From int_buyer_seller
> 2 Where company_id in
> 3 (select * from THE(select cast
> 4 (manda_search.prs_str_ret_num('2624,2860,83540,104346,104348,2954')
> as id_list_type) from dual)
> )
> 5 group By transaction_id having Count(Distinct company_id) = 6
> 6 /
>
> no rows selected
>
> Elapsed: 00:00:19.57
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=294168 Card=148 Byte
> s=1480)
>
> 1 0 FILTER
> 2 1 SORT (GROUP BY) (Cost=294168 Card=148 Bytes=1480)
> 3 2 NESTED LOOPS (Cost=294168 Card=240742 Bytes=2407420)
> 4 3 INDEX (FULL SCAN) OF 'PK_BUYER_SELLER' (UNIQUE) (Cos
> t=98 Card=29407 Bytes=235256)
>
> 5 3 COLLECTION ITERATOR (PICKLER FETCH)
> 6 5 TABLE ACCESS (FULL) OF 'DUAL' (Cost=1 Card=1)
>
>
>
>
> Statistics
> ----------------------------------------------------------
> 9 recursive calls
> 235256 db block gets
> 58919 consistent gets
> 0 physical reads
> 0 redo size
> 222 bytes sent via SQL*Net to client
> 314 bytes received via SQL*Net from client
> 1 SQL*Net roundtrips to/from client
> 5 sorts (memory)
> 0 sorts (disk)
> 0 rows processed
>
> This query calls a function which parses the string and returns the
> values in a pl/sql table type.
>
> The same query executes in less than 1 sec when I modify it to
>
>
> 1 Select transaction_id From int_buyer_seller
> 2 Where company_id in (2624,2860,83540,104346,104348,2954)
> 3* group By transaction_id having Count(Distinct company_id) = 6
> 4 /
>
> no rows selected
>
> Elapsed: 00:00:00.53
>
> Any suggestions as to how to improve this.
>
> I have wasted the whole day looking for solutions and trying various
> methods without any success.
>
> Hope somebody will help me out.
>
> Thanks
> Ron

HUH?
Did you ever clearly state the problem; other than query 1 takes 20 seconds to run?
Observation #1 - it appears a datatype mismatch exists between company_id (which I suspect is some flavor ot NUMBER) and the "leftside" of the WHERE clause in query #1 ( a STRING).

Please explain the purpose of the following... "(select * from THE(select cast
(manda_search.prs_str_ret_num('2624,2860,83540,104346,104348,2954') as id_list_type) from dual)" Received on Wed Jun 23 2004 - 19:56:24 CDT