Solved

increase the performance of select queries on huge tables

Posted on 2010-09-03
5
350 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
0
Comment
Question by:Swaminathan_K
5 Comments
 
LVL 20

Expert Comment

by:flow01
ID: 33603032
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 ?
0
 
LVL 4

Accepted Solution

by:
msk_apk earned 500 total points
ID: 33605757
Just going based on your index values,

pm_acct.ACCOUNT_ID=PM_BILL.PM_ACCOUNT_ID
pm_acct.ACCOUNT_NUMBER=PT_PMKN_DUMP.VID

But the contradict thing is pm_acct.ACCOUNT_NUMBER is a number where as  PT_PMKN_DUMP.VID is a varchar2(50). Am i missing something? If not then converting PT_PMKN_DUMP.VID as number could gain you great performance. From this I guess your sample query would be as below

select * from PT_PMKN_DUMP inner join pm_acct on PT_PMKN_DUMP.VID=pm_acct.ACCOUNT_NUMBER inner join PM_BILL on pm_acct.ACCOUNT_ID=PM_BILL.PM_ACCOUNT_ID

in the above you shall add criteria to check whether the product is active or not.

Now normally when you have composite index,  in the where criteria (or in the join criteria) if you dont specify all columns it tries to match with at least the left most column.

i.e in the above query join PT_PMKN_DUMP.VID=pm_acct.ACCOUNT_NUMBER might not use index of pm_acct.ACCOUNT_NUMBER as pm_acct has ACCOUNT_NUMBER as the second value in the composite index.

But join pm_acct.ACCOUNT_ID=PM_BILL.PM_ACCOUNT_ID will use complete index as pm_acct table has ACCOUNT_ID as the first column in the composite index and PM_BILL table has PM_ACCOUNT_ID as the first column in the composite index.

check whether the following changes are possible,

1. All possible join columns should have same column type.
2. Change composite index of pm_acct table into individual indices.

Apart from this you can tune memory parameters as well.
0
 
LVL 6

Expert Comment

by:sridharv9
ID: 33612556
You may look into using PARALLEL processing option (Parallel Hint). Personally it helped us a lot times for queries with huge table joins.
0
 
LVL 4

Expert Comment

by:msk_apk
ID: 33619217
have you tried CHANGING the index? if index modification is allowed? sometimes optimzing the index could make your table slick
0
 

Author Closing Comment

by:Swaminathan_K
ID: 33814709
Thanks , as suggested I just made changes to my query where clause and it is fine.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Java functions are among the best things for programmers to work with as Java sites can be very easy to read and prepare. Java especially simplifies many processes in the coding industry as it helps integrate many forms of technology and different d…
In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now