Link to home
Start Free TrialLog in
Avatar of Swaminathan_K
Swaminathan_KFlag for India

asked on

increase the performance of select queries on huge tables

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
Avatar of flow01
flow01
Flag of Netherlands image

Indexes seem appropiatie at first sight:  
1) what query do you want to improve ?
2) did you verify if the indexes are actualy used (explain plan) ?
3) are database statistics up to date ?
ASKER CERTIFIED SOLUTION
Avatar of msk_apk
msk_apk
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
You may look into using PARALLEL processing option (Parallel Hint). Personally it helped us a lot times for queries with huge table joins.
have you tried CHANGING the index? if index modification is allowed? sometimes optimzing the index could make your table slick
Avatar of Swaminathan_K

ASKER

Thanks , as suggested I just made changes to my query where clause and it is fine.