Need help with improving performance of a query.

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 Received on Thu Jun 24 2004 - 08:53:43 CDT