Printable View
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!
post execution plan
create index as global index
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!
hmmm I wonder what I asked wasnt clear? Cant you ppst the execution plan???
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!
Well Oracle Discoverer gives you the option to see the execution plan
stop being difficult and just ask him to post the execution plan ;-)Quote:
Originally posted by pando
Well Oracle Discoverer gives you the option to see the execution plan
I'd like to see the execution plan.... and a tkprof.
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
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.
Boy, sure you don't want the moon on a stick as well?Quote:
Originally posted by marist89
I'd like to see the execution plan.... and a tkprof.
:D
Would it be nice if this site/forum can support, sort of a DHTML publishing to the posts that are made by members and moderators? That every time one post like :
I would like to see the execution plan or the like docs thats details how to use execution plan or how to format a trace file using tkprof?
For the posters who ask the execution plan this link is not much of a relevance. But for the posters who initiate the THREAD, as we all know, we don't know if they really ignore the request to post the execution plan or just don't know yet how to do the task. As at least there would be an immediate link to request that was asked.:D