Need help on tuning the query

I need to fine tune the below query , since it is taking more than 7 secs . In the below plan , it is not using the primary index on the column
organization_id in the organization table. Hence , the query performance is below par. Any help in fine tuning the query to use the index on organization
table and better execution plan is really appreciated.
I have attached the execution plan for the query. Organization table is going for an full table scan.

Query :
Select NULL crm_organization_id,
    ilo.organization_id ,
    ilo.PARENT_ID,
    NULL DEAL_GROUP_ID ,
    CASE
      WHEN EXISTS
        (SELECT 1
        FROM dcm.deal_group dg
        WHERE dg.organization_id=ilo.organization_id
        )
      THEN 'T'
      ELSE 'F'
    END IS_INTERNAL ,
    NULL OWNER_USER_ID ,
    DECODE(ilo.STATUS_ID ,11,11,2) STATUS_ID,
    ilo.ORGANIZATION_NAME,
    (
    SELECT listagg(url ,',') within GROUP (
    ORDER BY organization_domain_id) domain
    FROM il_owner.organization_domain ilod
    WHERE ilod.organization_id=ilo.organization_id
    ) domain,
    NULL LOCK_VERSION ,
    NULL NAME_EN,
    NULL TAX_ID,
    c.ADDRESS1,
    c.ADDRESS2,
    c.STATE,
    c.CITY,
    c.ZIP,
    NULL SUMMARY,
    c.COUNTRY_ID ,
    NULL REGION_ID,
    NULL VISIBILITY_TYPE,
    NULL VISIBILITY_RESOURCE_ID,
    'N' IS_ARRANGER , 
    'N' IS_BORROWER , 
    'N' IS_INVESTOR,  
    ilo.status_id    
from organization ilo
LEFT OUTER JOIN contact c
on (ilo.organization_id=c.resource_id and c.resource_type_id=10 and c.contact_type_id=4)

Open in new window

Queryplan.txt
sam_2012Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Swadhin RaySenior Technical Engineer Commented:
I see the problem in "HASH JOIN RIGHT OUTER" :
 Can you try using one hint and check the plan and execution time :

 /*+ USE_HASH(contact) */

It would also be good if you can send some sample data and structure of the below tables:

organization
contact
deal_group
organization_domain

Also check if you have the indexes on the columns used in the joins or not.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ste5anSenior DeveloperCommented:
Do you have an index on Contact ( ContactType, ResourceType, Resource_ID) ?
0
sam_2012Author Commented:
Even after adding the use_hash hint , the execution plan i s same. Also , we have an index on resource_id,resource _type_id and contact_type_id  in contact table, we have an index on organization_id in organization_domain table and index in organization_id in organization table.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

sam_2012Author Commented:
all indexes are used for deal_group table also , we haven index on organization_id in deal_group table.
0
Swadhin RaySenior Technical Engineer Commented:
Can you create a composite index on Contact  table for the below columns:
create index <<index name >> on Contact   (ContactType, ResourceType, Resource_ID)  ;

the check the plan and also time taking to execute.
0
ste5anSenior DeveloperCommented:
Also , we have an index on resource_id,resource _type_id and contact_type_id  in contact table
Yup, that is what I've suspected.

It's the wrong order of columns to support your query.

For a index supporting your query, the constant columns must be the first in the index. Whether contact_type_id or resource_type_id should be first depends on the selectivity of those predicates.

btw, depeding on the size of the contact table and the remaining tables space you may also to consider adding c.ADDRESS1,c.ADDRESS2, c.STATE, c.CITY, c.ZIP, c.COUNTRY_ID to this index to get a covering index.
0
sam_2012Author Commented:
there is an index on the contact table contact_pk
if u see the execution plan , this index is used. I need some logic to use the index on organization table .
0
ste5anSenior DeveloperCommented:
Can you post the entire DDL for your contact table?
0
sam_2012Author Commented:
desc contact
Name                           Null     Type                                                                                                                                                                                          
------------------------------ -------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
RESOURCE_TYPE_ID               NOT NULL NUMBER                                                                                                                                                                                        
CONTACT_TYPE_ID                NOT NULL NUMBER                                                                                                                                                                                        
RESOURCE_ID                    NOT NULL NUMBER                                                                                                                                                                                        
COUNTRY_ID                              NUMBER                                                                                                                                                                                        
USERNAME                                VARCHAR2(100 CHAR)                                                                                                                                                                            
FIRST_NAME                              VARCHAR2(30 CHAR)                                                                                                                                                                             
MIDDLE_NAME                             VARCHAR2(30 CHAR)                                                                                                                                                                             
LAST_NAME                               VARCHAR2(30 CHAR)                                                                                                                                                                             
ADDRESS1                                VARCHAR2(50 CHAR)                                                                                                                                                                             
ADDRESS2                                VARCHAR2(50 CHAR)                                                                                                                                                                             
ADDRESS3                                VARCHAR2(50 CHAR)                                                                                                                                                                             
CITY                                    VARCHAR2(50 CHAR)                                                                                                                                                                             
STATE                                   VARCHAR2(50 CHAR)                                                                                                                                                                             
ZIP                                     VARCHAR2(50 CHAR)                                                                                                                                                                             
TELEPHONE_NUMBER                        VARCHAR2(255 CHAR)                                                                                                                                                                            
FAX_NUMBER                              VARCHAR2(255 CHAR)                                                                                                                                                                            
CREATED_BY                     NOT NULL NUMBER                                                                                                                                                                                        
CREATED_DATE                   NOT NULL DATE                                                                                                                                                                                          
LAST_MODIFIED_BY               NOT NULL NUMBER                                                                                                                                                                                        
LAST_MODIFIED_DATE             NOT NULL DATE                                                                                                                                                                                          
CONTACT_ID                     NOT NULL NUMBER  

Open in new window

primary key i s on resource_type_id , contact_type_id and resource_id.
0
Swadhin RaySenior Technical Engineer Commented:
Did you tried using :

from organization ilo
LEFT OUTER JOIN contact c
on (ilo.organization_id= c.resource_type_id=10 and c.contact_type_id=4 and c.resource_id )
0
johnsoneSenior Oracle DBACommented:
To clarify an earlier point, columns in an index do not need to be in a specific order for the index to be used.  If you are not using all the columns in the index in order, the index can still be used in an index skip scan.  The details of how they were are in the concepts manual here -> http://docs.oracle.com/database/121/CNCPT/indexiot.htm#CNCPT88837

Your query is asking for every row in the ORGANIZATION table.  If you want every row, there is no better way to do that than with a full scan.  Using an index is pointless and just makes it slower.

About the only thing I can see that would improve the query would be removing the table access on CONTACT, but when you are only retrieving 2,600 rows I don't see much improvement that can be gained there.

To me 7 seconds to get back over 250,000 rows seems pretty reasonable.
0
sam_2012Author Commented:
I tried this join , but still no index on organization table is used. Also , when i include any equal condition in the where clause , index is used.

For example
Select *
from
organization ilo left outer join contact c on (c.resource_type_id=10 and c.contact_type_id=4 and c.resource_id =ilo.organization_id ) where organization_id =<> , then the index is used.

I tried with the below option, it does not work.

from organization ilo
 LEFT OUTER JOIN contact c
 on ( c.resource_type_id=10 and c.contact_type_id=4 and c.resource_id =ilo.organization_id )
0
johnsoneSenior Oracle DBACommented:
Yes, if you put a where condition on the ORGANIZATION table that limits rows and is indexed, it will use an index.  You have changed your results from all rows in the ORGANIZATION table to only some rows.  It shouldn't be surprising that would change the plan.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.