handling large table joins for report
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: handling large table joins for report

  1. #1
    Join Date
    Oct 2009
    Posts
    1

    handling large table joins for report

    Hi,
    I 've a requirement to generate monthly report based on data from
    2 tables.Howver the size of the tables is very large .

    However there is partition defined for each month on rec_dt in both tables.
    The question is it advisable to join tables or is there any alternative.
    can I improve performance by breaking the query into parts.
    Pl suggest some better approach in sql or pl/sql .


    The query is below :

    select from mst_fact a,pty_fact b
    where a.ref_id like 'TA%300'
    and a.rec_dt between to_date('01-aug-09','dd-mon-yy') and to_date('31-aug-09','dd-mon-yy')
    and a.ref_id = b.ref_id
    and a.file_id = b.file_id
    and a.rec_dt = b.rec_dt

    Indexes on MST_FACT :
    1.Pk of mst_fact :
    REF_ID,FILE_ID,REC_DT

    2.XIE2MST_FACT on REC_DT

    Pk of pty_fact :
    REF_ID,FILE_ID,REC_DT,PTY_ROLE


    Explain plan :

    Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

    SELECT STATEMENT Optimizer Mode=CHOOSE 6 K 1752
    FILTER
    PARTITION RANGE ITERATOR KEY KEY
    HASH JOIN 6 K 2 M 1752
    INDEX FAST FULL SCAN XIE5_PTY_FACT 602 K 38 M 5 KEY KEY
    TABLE ACCESS BY LOCAL INDEX ROWID MST_FACT 229 K 60 M 11 KEY KEY
    INDEX RANGE SCAN XIE2MST_FACT 229 K 2 KEY KEY

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Posting suggests tables are partitioned.
    Posting also suggest best approach is join statement.

    If my understanding of the scenario is correct I would add parallel hint for the table with highest cardinality.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

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