Dear sir/Madam,
We are performign UPDATE statement as below..but for the past 6 hrs..it is under progress..
UPDATE PHONE_MASTER
SET COX_UNIQUE_CUSTOMER_KEY = :1,
HOUSEHOLD_ID = :2,
PHONE_NBR = :3,
PHONE_TYPE = :4,
CUSTOMER_TN_SEQUENCE = :5,
CUST_TELEPHONE_STATUS = :6,
FRANCHISE_NBR = :7,
HOUSE_NBR = :8,
BILL_TYPE_CD = :9,
NPASTD_NBR = :10,
NXXEXCHANGE_NBR = :11,
THOUSAND_NBR = :12,
COMPANY_NBR = :13,
DIVISION_NBR = :14,
JOURNAL_DT = :15,
SOURCE_SYSTEM = :16,
LAST_UPDATE_DT = :17
WHERE SITE_ID = :18 AND PHONE_MASTER_KEY = :19 AND ACCOUNT_NBR = :20
Thsi above table is partitioned ( 22 parttions ), and when i checked index stastus and partitons status, it shows below:
select i.table_name,i.index_name,ip.partition_name,i.status as INDEX_STATUS, ip.status as PARTITION_STATUS from dba_indexes i,dba_ind_partitions ip
where i.index_name = ip.index_name
and i.owner = 'CDM'
and i.table_name = 'PHONE_MASTER'
and i.index_name in( 'XPK_PM_PHONE_MASTER_KEY','XNU_PM_SITE_ID_PHONE_NBR','XNU_PM_SITE_ID_ACCT_PHN_NBR','XNU_PM_JOURNAL_DT','XNU_PM_SID_ACCN_TSEQ_SC_SO');
TABLE_NAME INDEX_NAME PARTITION_NAME INDEX_ST PARTITIO
------------------------------ ------------------------------ -------------------- -------- --------
PHONE_MASTER XNU_PM_JOURNAL_DT PM_1 N/A UNUSABLE
PHONE_MASTER XNU_PM_JOURNAL_DT PM_126 N/A UNUSABLE
PHONE_MASTER XNU_PM_JOURNAL_DT PM_131 N/A UNUSABLE
PHONE_MASTER XNU_PM_JOURNAL_DT PM_132 N/A UNUSABLE
PHONE_MASTER XNU_PM_JOURNAL_DT PM_135 N/A UNUSABLE
PHONE_MASTER XNU_PM_JOURNAL_DT PM_182 N/A UNUSABLE
PHONE_MASTER XNU_PM_JOURNAL_DT PM_186 N/A UNUSABLE
PHONE_MASTER XNU_PM_JOURNAL_DT PM_214 N/A UNUSABLE
PHONE_MASTER XNU_PM_JOURNAL_DT PM_215 N/A UNUSABLE
PHONE_MASTER XNU_PM_JOURNAL_DT PM_216 N/A UNUSABLE
PHONE_MASTER XNU_PM_JOURNAL_DT PM_238 N/A UNUSABLE
PHONE_MASTER XNU_PM_JOURNAL_DT PM_239 N/A UNUSABLE
PHONE_MASTER XNU_PM_JOURNAL_DT PM_333 N/A UNUSABLE
PHONE_MASTER XNU_PM_JOURNAL_DT PM_334 N/A UNUSABLE
PHONE_MASTER XNU_PM_JOURNAL_DT PM_342 N/A UNUSABLE
PHONE_MASTER XNU_PM_JOURNAL_DT PM_436 N/A UNUSABLE
PHONE_MASTER XNU_PM_JOURNAL_DT PM_476 N/A UNUSABLE
PHONE_MASTER XNU_PM_JOURNAL_DT PM_477 N/A UNUSABLE
PHONE_MASTER XNU_PM_JOURNAL_DT PM_541 N/A UNUSABLE
PHONE_MASTER XNU_PM_JOURNAL_DT PM_580 N/A UNUSABLE
PHONE_MASTER XNU_PM_JOURNAL_DT PM_609 N/A UNUSABLE
PHONE_MASTER XNU_PM_JOURNAL_DT PM_DEFAULT N/A UNUSABLE
PHONE_MASTER XNU_PM_SID_ACCN_TSEQ_SC_SO PM_1 N/A UNUSABLE
PHONE_MASTER XNU_PM_SID_ACCN_TSEQ_SC_SO PM_126 N/A UNUSABLE
PHONE_MASTER XNU_PM_SID_ACCN_TSEQ_SC_SO PM_131 N/A UNUSABLE
PHONE_MASTER XNU_PM_SID_ACCN_TSEQ_SC_SO PM_132 N/A UNUSABLE
PHONE_MASTER XNU_PM_SID_ACCN_TSEQ_SC_SO PM_135 N/A UNUSABLE
PHONE_MASTER XNU_PM_SID_ACCN_TSEQ_SC_SO PM_182 N/A UNUSABLE
PHONE_MASTER XNU_PM_SID_ACCN_TSEQ_SC_SO PM_186 N/A UNUSABLE
PHONE_MASTER XNU_PM_SID_ACCN_TSEQ_SC_SO PM_214 N/A UNUSABLE
PHONE_MASTER XNU_PM_SID_ACCN_TSEQ_SC_SO PM_215 N/A UNUSABLE
PHONE_MASTER XNU_PM_SID_ACCN_TSEQ_SC_SO PM_216 N/A UNUSABLE
PHONE_MASTER XNU_PM_SID_ACCN_TSEQ_SC_SO PM_238 N/A UNUSABLE
PHONE_MASTER XNU_PM_SID_ACCN_TSEQ_SC_SO PM_239 N/A UNUSABLE
PHONE_MASTER XNU_PM_SID_ACCN_TSEQ_SC_SO PM_333 N/A UNUSABLE
PHONE_MASTER XNU_PM_SID_ACCN_TSEQ_SC_SO PM_334 N/A UNUSABLE
PHONE_MASTER XNU_PM_SID_ACCN_TSEQ_SC_SO PM_342 N/A UNUSABLE
PHONE_MASTER XNU_PM_SID_ACCN_TSEQ_SC_SO PM_436 N/A UNUSABLE
PHONE_MASTER XNU_PM_SID_ACCN_TSEQ_SC_SO PM_476 N/A UNUSABLE
PHONE_MASTER XNU_PM_SID_ACCN_TSEQ_SC_SO PM_477 N/A UNUSABLE
PHONE_MASTER XNU_PM_SID_ACCN_TSEQ_SC_SO PM_541 N/A UNUSABLE
PHONE_MASTER XNU_PM_SID_ACCN_TSEQ_SC_SO PM_580 N/A UNUSABLE
PHONE_MASTER XNU_PM_SID_ACCN_TSEQ_SC_SO PM_609 N/A UNUSABLE
PHONE_MASTER XNU_PM_SID_ACCN_TSEQ_SC_SO PM_DEFAULT N/A UNUSABLE
PHONE_MASTER XNU_PM_SITE_ID_ACCT_PHN_NBR PM_1 N/A UNUSABLE
PHONE_MASTER XNU_PM_SITE_ID_ACCT_PHN_NBR PM_126 N/A UNUSABLE
PHONE_MASTER XNU_PM_SITE_ID_ACCT_PHN_NBR PM_131 N/A UNUSABLE
PHONE_MASTER XNU_PM_SITE_ID_ACCT_PHN_NBR PM_132 N/A UNUSABLE
PHONE_MASTER XNU_PM_SITE_ID_ACCT_PHN_NBR PM_135 N/A UNUSABLE
PHONE_MASTER XNU_PM_SITE_ID_ACCT_PHN_NBR PM_182 N/A UNUSABLE
PHONE_MASTER XNU_PM_SITE_ID_ACCT_PHN_NBR PM_186 N/A UNUSABLE
PHONE_MASTER XNU_PM_SITE_ID_ACCT_PHN_NBR PM_214 N/A UNUSABLE
PHONE_MASTER XNU_PM_SITE_ID_ACCT_PHN_NBR PM_215 N/A UNUSABLE
PHONE_MASTER XNU_PM_SITE_ID_ACCT_PHN_NBR PM_216 N/A UNUSABLE
PHONE_MASTER XNU_PM_SITE_ID_ACCT_PHN_NBR PM_238 N/A UNUSABLE
PHONE_MASTER XNU_PM_SITE_ID_ACCT_PHN_NBR PM_239 N/A UNUSABLE
PHONE_MASTER XNU_PM_SITE_ID_ACCT_PHN_NBR PM_333 N/A UNUSABLE
PHONE_MASTER XNU_PM_SITE_ID_ACCT_PHN_NBR PM_334 N/A UNUSABLE
PHONE_MASTER XNU_PM_SITE_ID_ACCT_PHN_NBR PM_342 N/A UNUSABLE
PHONE_MASTER XNU_PM_SITE_ID_ACCT_PHN_NBR PM_436 N/A UNUSABLE
PHONE_MASTER XNU_PM_SITE_ID_ACCT_PHN_NBR PM_476 N/A UNUSABLE
PHONE_MASTER XNU_PM_SITE_ID_ACCT_PHN_NBR PM_477 N/A UNUSABLE
PHONE_MASTER XNU_PM_SITE_ID_ACCT_PHN_NBR PM_541 N/A UNUSABLE
PHONE_MASTER XNU_PM_SITE_ID_ACCT_PHN_NBR PM_580 N/A UNUSABLE
PHONE_MASTER XNU_PM_SITE_ID_ACCT_PHN_NBR PM_609 N/A UNUSABLE
PHONE_MASTER XNU_PM_SITE_ID_ACCT_PHN_NBR PM_DEFAULT N/A UNUSABLE
PHONE_MASTER XNU_PM_SITE_ID_PHONE_NBR PM_1 N/A UNUSABLE
PHONE_MASTER XNU_PM_SITE_ID_PHONE_NBR PM_126 N/A UNUSABLE
PHONE_MASTER XNU_PM_SITE_ID_PHONE_NBR PM_131 N/A UNUSABLE
PHONE_MASTER XNU_PM_SITE_ID_PHONE_NBR PM_132 N/A UNUSABLE
PHONE_MASTER XNU_PM_SITE_ID_PHONE_NBR PM_135 N/A UNUSABLE
PHONE_MASTER XNU_PM_SITE_ID_PHONE_NBR PM_182 N/A UNUSABLE
PHONE_MASTER XNU_PM_SITE_ID_PHONE_NBR PM_186 N/A UNUSABLE
PHONE_MASTER XNU_PM_SITE_ID_PHONE_NBR PM_214 N/A UNUSABLE
PHONE_MASTER XNU_PM_SITE_ID_PHONE_NBR PM_215 N/A UNUSABLE
PHONE_MASTER XNU_PM_SITE_ID_PHONE_NBR PM_216 N/A UNUSABLE
PHONE_MASTER XNU_PM_SITE_ID_PHONE_NBR PM_238 N/A UNUSABLE
PHONE_MASTER XNU_PM_SITE_ID_PHONE_NBR PM_239 N/A UNUSABLE
PHONE_MASTER XNU_PM_SITE_ID_PHONE_NBR PM_333 N/A UNUSABLE
PHONE_MASTER XNU_PM_SITE_ID_PHONE_NBR PM_334 N/A UNUSABLE
PHONE_MASTER XNU_PM_SITE_ID_PHONE_NBR PM_342 N/A UNUSABLE
PHONE_MASTER XNU_PM_SITE_ID_PHONE_NBR PM_436 N/A UNUSABLE
PHONE_MASTER XNU_PM_SITE_ID_PHONE_NBR PM_476 N/A UNUSABLE
PHONE_MASTER XNU_PM_SITE_ID_PHONE_NBR PM_477 N/A UNUSABLE
PHONE_MASTER XNU_PM_SITE_ID_PHONE_NBR PM_541 N/A UNUSABLE
PHONE_MASTER XNU_PM_SITE_ID_PHONE_NBR PM_580 N/A UNUSABLE
PHONE_MASTER XNU_PM_SITE_ID_PHONE_NBR PM_609 N/A UNUSABLE
PHONE_MASTER XNU_PM_SITE_ID_PHONE_NBR PM_DEFAULT N/A UNUSABLE
PHONE_MASTER XPK_PM_PHONE_MASTER_KEY PM_1 N/A UNUSABLE
PHONE_MASTER XPK_PM_PHONE_MASTER_KEY PM_126 N/A UNUSABLE
PHONE_MASTER XPK_PM_PHONE_MASTER_KEY PM_131 N/A UNUSABLE
PHONE_MASTER XPK_PM_PHONE_MASTER_KEY PM_132 N/A UNUSABLE
PHONE_MASTER XPK_PM_PHONE_MASTER_KEY PM_135 N/A UNUSABLE
PHONE_MASTER XPK_PM_PHONE_MASTER_KEY PM_182 N/A UNUSABLE
PHONE_MASTER XPK_PM_PHONE_MASTER_KEY PM_186 N/A UNUSABLE
PHONE_MASTER XPK_PM_PHONE_MASTER_KEY PM_214 N/A UNUSABLE
PHONE_MASTER XPK_PM_PHONE_MASTER_KEY PM_215 N/A UNUSABLE
TABLE_NAME INDEX_NAME PARTITION_NAME INDEX_ST PARTITIO
------------------------------ ------------------------------ -------------------- -------- --------
PHONE_MASTER XPK_PM_PHONE_MASTER_KEY PM_216 N/A UNUSABLE
PHONE_MASTER XPK_PM_PHONE_MASTER_KEY PM_238 N/A UNUSABLE
PHONE_MASTER XPK_PM_PHONE_MASTER_KEY PM_239 N/A UNUSABLE
PHONE_MASTER XPK_PM_PHONE_MASTER_KEY PM_333 N/A UNUSABLE
PHONE_MASTER XPK_PM_PHONE_MASTER_KEY PM_334 N/A UNUSABLE
PHONE_MASTER XPK_PM_PHONE_MASTER_KEY PM_342 N/A UNUSABLE
PHONE_MASTER XPK_PM_PHONE_MASTER_KEY PM_436 N/A UNUSABLE
PHONE_MASTER XPK_PM_PHONE_MASTER_KEY PM_476 N/A UNUSABLE
PHONE_MASTER XPK_PM_PHONE_MASTER_KEY PM_477 N/A UNUSABLE
PHONE_MASTER XPK_PM_PHONE_MASTER_KEY PM_541 N/A UNUSABLE
PHONE_MASTER XPK_PM_PHONE_MASTER_KEY PM_580 N/A UNUSABLE
PHONE_MASTER XPK_PM_PHONE_MASTER_KEY PM_609 N/A UNUSABLE
PHONE_MASTER XPK_PM_PHONE_MASTER_KEY PM_DEFAULT N/A UNUSABLE
110 rows selected.
since all the partitions status are UNUSABLE, shalli consider rebuild all these 5 indexes used for those 3 columns in WHERE condition in the UPDATE statement( a tthe top )
After rebuild, will the performnace faster or ?
thankyou
kesavan
] 
Blog
Search
Help
Members
Register
Login
Home
