DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Perfomance in index creation

  1. #1
    Join Date
    Jan 2003
    Posts
    141

    Perfomance in index creation

    Hi!

    A table already has a index on three columns (date_as_of,Brch_code,Porft_no) and partition on branch. To improve the performance took an export and created a new partitioned the table,imported the data and created some additional index on the date_as _of column for different quarters.

    When the users query on the date_as_of columns its talking a long time. The record count in the table is 14680309

    Cheers!

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    post execution plan

    create index as global index

  3. #3
    Join Date
    Jan 2003
    Posts
    141
    Hi !
    I have table bal_mm_tb which is partitioned on column BRANCH_CODE using RANGE Partition.

    To improve perfromance on TABLE bal_mm_tb we have
    exported the table, crated new partition using RANGE Partition for DAT_AS_OF, subpartition by LIST for BRANCH_CODE with LOCAL
    index for DAT_AS_OF and BRANCH_CODE and imported the data.The table bal_mm_tb has a view (vw_bal_mm_tb).

    Now i have problem in my existing queries (very slow)while fetching values. The query is selecting values from the
    View ( vw_bal_mm_tb) with join condition based on branch on another table with "RANGE" Partition on column branch_code.

    If i remove the join on branch code the query is faster!
    Do i need to drop and recreate the view to increase performance???

    How to improve the performance????

    partition/sub partition creation script:
    ---------------------------------------
    CONSTRAINT "BAL_MM_TB_PKEY" PRIMARY KEY ("BMM_BRCH_CODE", "BMM_PORTF_NO", "BMM_ORIG_REFER_NO", "BMM_DAT_AS_OF")
    USING INDEX TABLESPACE "PRDAGINDX" ENABLE
    )
    TABLESPACE "PRDAGDATA1"
    partition by range (BMM_DAT_AS_OF)
    subpartition by list (BMM_BRCH_CODE)
    subpartition template
    (
    subpartition bmm_hk_brch values ('712') TABLESPACE PRDAGDATA1 ,
    subpartition bmm_sg_brch values ('760') TABLESPACE PRDAGDATA3,
    subpartition bmm_oth_brch values (DEFAULT) TABLESPACE PRDAGDATA5
    )
    (
    partition bmm_1st_qtr_02 values less than (to_date('01-apr-2002','dd-mon-yyyy'))
    TABLESPACE "PRDAGDATA5" NOCOMPRESS ,
    partition bmm_2nd_qtr_02 values less than (to_date('01-jul-2002','dd-mon-yyyy'))
    TABLESPACE "PRDAGDATA5" NOCOMPRESS ,
    partition bmm_3rd_qtr_02 values less than (to_date('01-oct-2002','dd-mon-yyyy'))
    TABLESPACE "PRDAGDATA5" NOCOMPRESS ,
    partition bmm_4th_qtr_02 values less than (to_date('01-jan-2003','dd-mon-yyyy'))
    TABLESPACE "PRDAGDATA5" NOCOMPRESS ,
    partition bmm_1st_qtr_03 values less than (to_date('01-apr-2003','dd-mon-yyyy'))
    TABLESPACE "PRDAGDATA5" NOCOMPRESS ,
    partition bmm_2nd_qtr_03 values less than (to_date('01-jul-2003','dd-mon-yyyy'))
    TABLESPACE "PRDAGDATA5" NOCOMPRESS ,
    partition bmm_3rd_qtr_03 values less than (to_date('01-oct-2003','dd-mon-yyyy'))
    TABLESPACE "PRDAGDATA5" NOCOMPRESS ,
    partition bmm_4th_qtr_03 values less than (to_date('01-jan-2004','dd-mon-yyyy'))
    TABLESPACE "PRDAGDATA5" NOCOMPRESS ,
    partition bmm_1st_qtr_04 values less than (to_date('01-apr-2004','dd-mon-yyyy'))
    TABLESPACE "PRDAGDATA5" NOCOMPRESS ,
    partition bmm_2nd_qtr_04 values less than (to_date('01-jul-2004','dd-mon-yyyy'))
    TABLESPACE "PRDAGDATA5" NOCOMPRESS ,
    partition bmm_3rd_qtr_04 values less than (to_date('01-oct-2004','dd-mon-yyyy'))
    TABLESPACE "PRDAGDATA5" NOCOMPRESS ,
    partition bmm_4th_qtr_04 values less than (to_date('01-jan-2005','dd-mon-yyyy'))
    TABLESPACE "PRDAGDATA5" NOCOMPRESS ,
    partition bmm_1st_qtr_05 values less than (to_date('01-apr-2005','dd-mon-yyyy'))
    TABLESPACE "PRDAGDATA5" NOCOMPRESS ,
    partition bmm_2nd_qtr_05 values less than (to_date('01-jul-2005','dd-mon-yyyy'))
    TABLESPACE "PRDAGDATA5" NOCOMPRESS ,
    partition bmm_3rd_qtr_05 values less than (to_date('01-oct-2005','dd-mon-yyyy'))
    TABLESPACE "PRDAGDATA5" NOCOMPRESS ,
    partition bmm_4th_qtr_05 values less than (to_date('01-jan-2006','dd-mon-yyyy'))
    TABLESPACE "PRDAGDATA5" NOCOMPRESS ,
    partition bmm_oth_qtr values less than (MAXVALUE)
    TABLESPACE "PRDAGDATA5" NOCOMPRESS
    )
    /

    Want to know that where the data gets stored for date_as_of and branch_code and how its stored in the two tablespaces.



    Many Thanks!

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    hmmm I wonder what I asked wasnt clear? Cant you ppst the execution plan???

  5. #5
    Join Date
    Jan 2003
    Posts
    141
    Hi
    The query is generated in oracle discoverer and not able to view the execution plan. If i remove the branch code from the query is able to fetch the records. But with the Branch code it hangs!!!

    Query: based on view
    --------------------
    SELECT DISTINCT VW_BAL_MM_TB.BMM_DAT_MATURITY-VW_BAL_MM_TB.BMM_DAT_VALUE, VW_BAL_MM_TB.BMM_ASSET_CODE,
    VW_BAL_MM_TB.BMM_AUTOROLL_FLG, VW_BAL_MM_TB.BMM_AUTOROLL_TYPE, VW_BAL_MM_TB.BMM_BASE_NO, VW_BAL_MM_TB.BMM_BRCH_CODE, VW_BAL_MM_TB.BMM_CNTRY_CODE_NCY, VW_BAL_MM_TB.BMM_CONTRACT_TEXT, VW_BAL_MM_TB.BMM_CONTRACT_TYPE, VW_BAL_MM_TB.BMM_CREDIT_ACCT, VW_BAL_MM_TB.BMM_DAT_AS_OF, VW_BAL_MM_TB.BMM_DAT_INCEPTION, VW_BAL_MM_TB.BMM_DAT_LAST_UPDT, VW_BAL_MM_TB.BMM_DAT_MATURITY, VW_BAL_MM_TB.BMM_DAT_NOTIFICATION, VW_BAL_MM_TB.BMM_DAT_TAKEDOWN, VW_BAL_MM_TB.BMM_DAT_VALUE, VW_BAL_MM_TB.BMM_DEBIT_ACCT, VW_BAL_MM_TB.BMM_DP_SOURCE_OF_FUNDS, VW_BAL_MM_TB.BMM_FMS_ACCT, VW_BAL_MM_TB.BMM_IND_CODE, VW_BAL_MM_TB.BMM_INTRST_FREQ, VW_BAL_MM_TB.BMM_INTRST_INCREMENT, VW_BAL_MM_TB.BMM_INTRST_METHOD, VW_BAL_MM_TB.BMM_LN_ACCRUAL_STAT, VW_BAL_MM_TB.BMM_LN_CREDIT_LINE_NO, VW_BAL_MM_TB.BMM_LN_GL_STATUS, VW_BAL_MM_TB.BMM_LN_LOAN_PURPOSE, VW_BAL_MM_TB.BMM_LN_LOAN_PURPOSE_DESC, VW_BAL_MM_TB.BMM_NOM_CCY, VW_BAL_MM_TB.BMM_ORIG_PROD_CODE, VW_BAL_MM_TB.BMM_ORIG_REFER_NO, VW_BAL_MM_TB.BMM_PLEDGED_FLG, VW_BAL_MM_TB.BMM_PORTF_NO, VW_BAL_MM_TB.BMM_PP_CODE, VW_BAL_MM_TB.BMM_PROD_CODE, VW_BAL_MM_TB.BMM_PROD_TYPE, VW_BAL_MM_TB.BMM_REF_CCY, VW_BAL_MM_TB.BMM_RG_CODE, VW_BAL_MM_TB.BMM_RM_CODE, VW_BAL_MM_TB.BMM_STATUS, VW_BAL_MM_TB.BMM_TENOR_FREQ, VW_CLIENT_INFO_TB.CLINFO_AO_NAME, VW_CLIENT_INFO_TB.CLINFO_COST_CNTR_DESC, VW_BAL_MM_TB.BMM_REFINANCE_RATE-VW_BAL_MM_TB.BMM_CUSTMR_INTRST_RATE, VW_BAL_MM_TB.BMM_AMT_OUTST_PRINCIPAL_RCY, VW_BAL_MM_TB.BMM_AMT_OUTST_PRINCIPAL_USD, VW_BAL_MM_TB.BMM_AMT_PRINCIPAL_NCY, VW_BAL_MM_TB.BMM_AMT_PRINCIPAL_RCY, VW_BAL_MM_TB.BMM_AMT_PRINCIPAL_USD, VW_BAL_MM_TB.BMM_CUSTMR_INTRST_RATE, VW_BAL_MM_TB.BMM_LN_LOANABLE_VAL_PERCENT, VW_BAL_MM_TB.BMM_MARKET_TRESURY_RATE, VW_BAL_MM_TB.BMM_REFINANCE_RATE
    FROM "OPS$SCOTT".VW_BAL_MM_TB VW_BAL_MM_TB,
    "OPS$SCOTT".VW_CLIENT_INFO_TB VW_CLIENT_INFO_TB
    WHERE
    VW_CLIENT_INFO_TB.CLINFO_BRCH_CODE = '712',
    AND VW_CLIENT_INFO_TB.CLINFO_PORTF_NO = VW_BAL_MM_TB.BMM_PORTF_NO,
    AND VW_BAL_MM_TB.BMM_PP_CODE = 'ALS',
    AND VW_BAL_MM_TB.BMM_DAT_AS_OF = '07-OCT-2005',
    AND VW_BAL_MM_TB.BMM_DAT_MATURITY >='10-OCT-2004',
    ORDER BY VW_BAL_MM_TB.BMM_NOM_CCY ASC,
    VW_BAL_MM_TB.BMM_DAT_MATURITY ASC,
    VW_CLIENT_INFO_TB.CLINFO_COST_CNTR_DESC ASC,
    VW_CLIENT_INFO_TB.CLINFO_AO_NAME ASC


    cheers!

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Well Oracle Discoverer gives you the option to see the execution plan

  7. #7
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    Originally posted by pando
    Well Oracle Discoverer gives you the option to see the execution plan
    stop being difficult and just ask him to post the execution plan ;-)
    I'm stmontgo and I approve of this message

  8. #8
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    I'd like to see the execution plan.... and a tkprof.
    Jeff Hunter

  9. #9
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    There are some problems already reported with RANGE-LIST partition.

    Any new option provided by Oracle should be tested carefully.

    Check with MetaLink for further info.

    Why don't you try with a simple RANGE partiton or RANGE+HASH partition?

    Tamil

  10. #10
    Join Date
    Mar 2002
    Posts
    534
    Just as information have a look at:

    http://www.dbasupport.com/forums/sho...threadid=44760

    There is no patch yet for this bug and it can be reporduced on any Oracle Version.

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