-
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!
-
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
-
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
-
I'd like to see the execution plan.... and a tkprof.
Jeff Hunter
-
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|