troubleshooting Question

increase the performance of select queries on huge tables

Avatar of Swaminathan_K
Swaminathan_KFlag for India asked on
Oracle DatabaseJava
5 Comments1 Solution384 ViewsLast Modified:
Hi,


I can anyone suggest the correct index to use for optimised query performance when fired from procedure and sql prompt.
I  have 3 tables as mentioned below .
1. PT_PMKN_DUMP . Ths is the main table which conatins the data for which we are doing the data validation. It contains around 4 lakh records.
I have an index on the table named idx_PT_PMKN_DUMP on columns (vid, external_id) as these are the main columns. Its a composite index
2.PM_BILL this table Iam querying to check the product id is active or not. This table contains around 4.5 records. I have am index named idx_PM_BILL on columns (pm_account_id, pricing_instance_id).

3.PM_ACCT . This is the master table which contains the account_id and account_number(vid) This table Iam referring to get the account_number (vid) which is named as VID in the table  PT_PMKN_DUMP  which i match with the pm_merchant_billing table to get the . active status of the product id. I have an index named idx_Pm_acct on columns (account_id, account_number). This table contains 3 lakh rows.

the table structures are as follows


desc PM_BILL
Name                           Null     Type                                                                                                                                                                                          
------------------------------ -------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
MERCHANT_BILLING_ID            NOT NULL NUMBER
ACCOUNT_ID                              VARCHAR2(32)  
PRODUCT_ID                     NOT NULL NUMBER
PRICING_ID                     NOT NULL NUMBER
PRICING_INSTANCE_ID            NOT NULL NUMBER
BUNDLE_ID                               NUMBER
PRODUCT_LINE_ID                NOT NULL NUMBER
EXTRACT_FLAG                   NOT NULL NUMBER
PRODUCT_STATUS                 NOT NULL NUMBER
ACTIVATION_DATE                         DATE
DEACTIVATION_DATE                       DATE
BILLING_REASON_ID              NOT NULL NUMBER  
BILLING_START_DATE                      DATE
LAST_CHANGED                   NOT NULL DATE
PM_ACCOUNT_ID                           NUMBER

desc PT_PMKN_DUMP
Name                           Null     Type                                                                                                                                                                                          
------------------------------ -------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
VID                                     VARCHAR2(50)
BUNDLEID                                NUMBER
PRODUCTID                               NUMBER
CURRENCYCODE                            NUMBER(3)
RATE                                    NUMBER  
SI_LVL_COMPONENT                        NUMBER(15)
TOTBUNDLEPRICE                          NUMBER
EXTERNAL_ID                             VARCHAR2(144)
OWNING_ACCOUNT_NO                       NUMBER(10)
COMPONENT_ID                            NUMBER(10)
ELEMENT_ID                              NUMBER(10)
PRODUCT_ID                              NUMBER(15)
CURRENCY_CODE                           NUMBER(6)
KENANRATE                               NUMBER  
ACTIVEDT                                DATE
INACTIVEDT                              DATE
RK                                      NUMBER  
NOOFRECS                                NUMBER
TOTALKENANRATE                          NUMBER



desc pm_acct
Name                           Null     Type                                                                                                                                                                                          
------------------------------ -------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ACCOUNT_ID                     NOT NULL NUMBER
ACCOUNT_VALUE                  NOT NULL VARCHAR2(32)
ACCOUNT_TYPE                            CHAR(1)
LAST_CHANGED                   NOT NULL DATE
ACCOUNT_NUMBER                          NUMBE
ASKER CERTIFIED SOLUTION
msk_apk

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros