DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Optimaizint help

  1. #1
    Join Date
    Aug 2000
    Location
    Ny
    Posts
    105

    Question

    Can any one help me optimize this query:

    UPDATE TABLE_1 A
    SET A.COL_1 =
    (SELECT COUNT (DISTINCT B.USER_ID)
    FROM TABLE_2 B
    WHERE B.DATE_COL >= '01-APR-01'
    AND B.DATE_COL < '01-MAY-01'
    AND A.COL_2 = B.COL_2
    AND A.COL_3 = 'TOTAL'
    GROUP BY B.COL_2)
    WHERE A.COL_3 = 'TOTAL';

    there is an index on a.col_2 and a seperate index on a.col_3, table_2 is partition by day and I can not create any indexes on it, I also tried to execute it in parallel.

    table_1 have about 450 records and about 40-50 meet the where creteria, table_2 is very big probably about 1 mill per day.

    Any ideas???


  2. #2
    Join Date
    Feb 2001
    Posts
    163
    Please post the plan of execution and is writing this in PL/SQL a choice?

    uday

  3. #3
    Join Date
    Aug 2000
    Location
    Ny
    Posts
    105
    Yes PLSQL is a chooice, and I'll generate the exec plan

  4. #4
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Originally posted by Highlander
    ...and I can not create any indexes on it, ...
    Why not?

    It would seem to me that :
    - This should definitely be done in parallel (~30 threads - 1 per partition)
    - There should be a local index on B: DATE_COL, COL_2

    There might be some tricks to be done with the query, but I'd have to see the plan.

    - Chris

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width