We help IT Professionals succeed at work.

increase the performance of select queries on huge tables

Swaminathan_K
on
381 Views
Last Modified: 2013-11-23
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
Comment
Watch Question

flow01IT-specialist
CERTIFIED EXPERT

Commented:
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 ?
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
You may look into using PARALLEL processing option (Parallel Hint). Personally it helped us a lot times for queries with huge table joins.

Commented:
have you tried CHANGING the index? if index modification is allowed? sometimes optimzing the index could make your table slick

Author

Commented:
Thanks , as suggested I just made changes to my query where clause and it is fine.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.