Re: Query Performance Issue
From: Mladen Gogala <gogala.mladen_at_gmail.com>
Date: Tue, 23 Apr 2019 08:31:43 -0400
Message-ID: <231e7289-a73e-cb61-ad76-532b99146e13_at_gmail.com>
> SELECT COUNT (DISTINCT U.USER_KEY) FROM TMS.USERS U JOIN
> TMS.USER_GROUP UG ON U.USER_KEY = UG.USER_KEY WHERE
> UG.GROUP_TREE_KEY = 'ia744d7790000015b347a4749dd5889b8' and
> UG.delete_flag ='N' and U.delete_flag ='N';
Date: Tue, 23 Apr 2019 08:31:43 -0400
Message-ID: <231e7289-a73e-cb61-ad76-532b99146e13_at_gmail.com>
You can cluster table TMS.USERS and TMS.USERS_GROUP on the USER_KEY column and then create a 2 column index on GROUP_TREE_KEY and DELETE_FLAG on USER_GROUPS and a bitmap index on USERS.DELETE_FLAG. Kudos for not trying to solve that with parallelism.
On 4/23/19 8:08 AM, Rakesh Ra wrote:
>
> SELECT COUNT (DISTINCT U.USER_KEY) FROM TMS.USERS U JOIN
> TMS.USER_GROUP UG ON U.USER_KEY = UG.USER_KEY WHERE
> UG.GROUP_TREE_KEY = 'ia744d7790000015b347a4749dd5889b8' and
> UG.delete_flag ='N' and U.delete_flag ='N';
-- Mladen Gogala Database Consultant Tel: (347) 321-1217 -- http://www.freelists.org/webpage/oracle-lReceived on Tue Apr 23 2019 - 14:31:43 CEST