SELECT CONCAT (UP.hcc_last_name,CONCAT (', ',CONCAT (UP.hcc_first_name,CONCAT (' [', CONCAT (u.user_alias, ']'))))), u.user_id FROM bv_user_profile UP, bv_user u, bv_ep_qual_value qv, bv_ep_uprof_qval uq, bv_category c, (SELECT u2.user_id FROM bv_user u2, bv_ep_uprof_qval uq2, bv_ep_qual_value qv2, bv_category c2 WHERE u2.user_id = uq2.user_id AND uq2.qvid = qv2.OID AND qv2.status = 1 AND qv2.deleted = 0 AND qv2.friendly_name ='Alice - Atlas CH' AND qv2.store_id = 107 AND uq2.qid = c2.OID AND c2.NAME = 'ATLAS LQA Districts' AND c2.store_id = 107 ) dis WHERE u.user_id = UP.user_id AND u.user_state = 0 AND u.user_id = dis.user_id AND u.user_id = uq.user_id AND uq.qvid = qv.OID AND qv.store_id = 107 AND qv.status = 1 AND qv.deleted = 0 AND qv.friendly_name = 'Grading Manager' AND uq.qid = c.OID AND c.store_id = 107 AND c.status = 1 AND c.deleted = 0 AND c.NAME = 'ATLAS LQA User Role' ORDER BY 1 ASC

sorry once again for lacking discipline and being irresponsible

SELECT   Concat(Up.hcc_Last_Name,Concat(', ',Concat(Up.hcc_First_Name,Concat(' [',Concat(u.User_Alias,']'))))),
         u.User_Id
FROM     bv_User_Profile Up,
         bv_User u,
         bv_ep_qual_Value qv,
         bv_ep_UprOf_qval uq,
         bv_Category c,
         (SELECT u2.User_Id
          FROM   bv_User u2,
                 bv_ep_UprOf_qval uq2,
                 bv_ep_qual_Value qv2,
                 bv_Category c2
          WHERE  u2.User_Id = uq2.User_Id
                 AND uq2.qvId = qv2.oId
                 AND qv2.Status = 1
                 AND qv2.Deleted = 0
                 AND qv2.Friendly_Name = 'Alice - Atlas CH'
                 AND qv2.Store_Id = 107
                 AND uq2.qId = c2.oId
                 AND c2.NAME = 'ATLAS LQA Districts'
                 AND c2.Store_Id = 107) dIs
WHERE    u.User_Id = Up.User_Id
         AND u.User_State = 0
         AND u.User_Id = dIs.User_Id
         AND u.User_Id = uq.User_Id
         AND uq.qvId = qv.oId
         AND qv.Store_Id = 107
         AND qv.Status = 1
         AND qv.Deleted = 0
         AND qv.Friendly_Name = 'Grading Manager'
         AND uq.qId = c.oId
         AND c.Store_Id = 107
         AND c.Status = 1
         AND c.Deleted = 0
         AND c.NAME = 'ATLAS LQA User Role'
ORDER BY 1 ASC

1.Row Count for BV_USER = 78663
2.Row Count for bv_user_profile = 58490
3.Row Count for bv_ep_qual_value =2074
4.Row Count for bv_ep_uprof_qval = 4398579
5.Row Count for bv_category = 43786

set verify off

break on index_name skip 1
col column_name format a30

select index_name,column_name
,(select uniqueness from dba_indexes b where b.owner = a.index_owner and b.index_name = a.index_name) uniqueness
,(select tablespace_name from dba_indexes b where b.owner = a.index_owner and b.index_name = a.index_name) tablespace_name
from dba_ind_columns a
where table_name = upper('&&2')
and table_owner = upper('&&1')
order by 1,column_position
/

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2058 Card=1 Bytes=
          192)

   1    0   SORT (ORDER BY) (Cost=2058 Card=1 Bytes=192)
   2    1     NESTED LOOPS (Cost=2057 Card=1 Bytes=192)
   3    2       NESTED LOOPS (Cost=2056 Card=1 Bytes=175)
   4    3         NESTED LOOPS (Cost=2056 Card=1 Bytes=169)
   5    4           HASH JOIN (Cost=2055 Card=1 Bytes=147)
   6    5             HASH JOIN (Cost=2052 Card=6 Bytes=732)
   7    6               TABLE ACCESS (FULL) OF 'BV_EP_QUAL_VALUE' (TAB
          LE) (Cost=9 Card=1 Bytes=30)

   8    6               NESTED LOOPS (Cost=2042 Card=28253 Bytes=25992
          76)

   9    8                 MERGE JOIN (CARTESIAN) (Cost=272 Card=883 By
          tes=66225)

  10    9                   MERGE JOIN (CARTESIAN) (Cost=11 Card=1 Byt
          es=51)

  11   10                     TABLE ACCESS (FULL) OF 'BV_EP_QUAL_VALUE
          ' (TABLE) (Cost=9 Card=1 Bytes=30)

  12   10                     BUFFER (SORT) (Cost=2 Card=1 Bytes=21)
  13   12                       TABLE ACCESS (BY INDEX ROWID) OF 'BV_C
          ATEGORY' (TABLE) (Cost=2 Card=1 Bytes=21)

  14   13                         INDEX (RANGE SCAN) OF 'BV_CATEGORY_K
          EY_IDX' (INDEX (UNIQUE)) (Cost=1 Card=1)

  15    9                   BUFFER (SORT) (Cost=270 Card=39455 Bytes=9
          46920)

  16   15                     TABLE ACCESS (FULL) OF 'BV_USER' (TABLE)
           (Cost=261 Card=39455 Bytes=946920)

  17    8                 INDEX (RANGE SCAN) OF 'BV_EP_UPROF_QVAL_PK'
          (INDEX (UNIQUE)) (Cost=2 Card=32 Bytes=544)

  18    5             TABLE ACCESS (BY INDEX ROWID) OF 'BV_CATEGORY' (
          TABLE) (Cost=3 Card=1 Bytes=25)

  19   18               INDEX (RANGE SCAN) OF 'BV_CATEGORY_KEY_IDX' (I
          NDEX (UNIQUE)) (Cost=2 Card=1)

  20    4           TABLE ACCESS (BY INDEX ROWID) OF 'BV_USER_PROFILE'
           (TABLE) (Cost=1 Card=1 Bytes=22)

  21   20             INDEX (UNIQUE SCAN) OF 'BV_USER_PROFILE_ID_IDX'
          (INDEX (UNIQUE)) (Cost=0 Card=1)

  22    3         INDEX (UNIQUE SCAN) OF 'BV_USER_PK' (INDEX (UNIQUE))
           (Cost=0 Card=1 Bytes=6)

  23    2       INDEX (UNIQUE SCAN) OF 'BV_EP_UPROF_QVAL_PK' (INDEX (U
          NIQUE)) (Cost=1 Card=1 Bytes=17)





Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
     177178  consistent gets
      17296  physical reads
          0  redo size
        453  bytes sent via SQL*Net to client
        279  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
          5  rows processed

col PLAN_PLUS_EXP format a400
set linesize 999
set trimspool on
set trimout on

I took the statistics with the help of dbms_stats before testing it
please let me know if it is not fine.I tried formatting this in sql formatter but got errors,then i put the ouptput in code/code and sent it.

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2058 Card=1 Bytes=192)
   1    0   SORT (ORDER BY) (Cost=2058 Card=1 Bytes=192)
   2    1     NESTED LOOPS (Cost=2057 Card=1 Bytes=192)
   3    2       NESTED LOOPS (Cost=2056 Card=1 Bytes=175)
   4    3         NESTED LOOPS (Cost=2056 Card=1 Bytes=169)
   5    4           HASH JOIN (Cost=2055 Card=1 Bytes=147)
   6    5             HASH JOIN (Cost=2052 Card=6 Bytes=732)
   7    6               TABLE ACCESS (FULL) OF 'BV_EP_QUAL_VALUE' (TABLE) (Cost=9 Card=1 Bytes=30)
   8    6               NESTED LOOPS (Cost=2042 Card=28253 Bytes=2599276)
   9    8                 MERGE JOIN (CARTESIAN) (Cost=272 Card=883 Bytes=66225)
  10    9                   MERGE JOIN (CARTESIAN) (Cost=11 Card=1 Bytes=51)
  11   10                     TABLE ACCESS (FULL) OF 'BV_EP_QUAL_VALUE' (TABLE) (Cost=9 Card=1 Bytes=30)
  12   10                     BUFFER (SORT) (Cost=2 Card=1 Bytes=21)
  13   12                       TABLE ACCESS (BY INDEX ROWID) OF 'BV_CATEGORY' (TABLE) (Cost=2 Card=1 Bytes=21)
  14   13                         INDEX (RANGE SCAN) OF 'BV_CATEGORY_KEY_IDX' (INDEX (UNIQUE)) (Cost=1 Card=1)
  15    9                   BUFFER (SORT) (Cost=270 Card=39455 Bytes=946920)
  16   15                     TABLE ACCESS (FULL) OF 'BV_USER' (TABLE) (Cost=261 Card=39455 Bytes=946920)
  17    8                 INDEX (RANGE SCAN) OF 'BV_EP_UPROF_QVAL_PK' (INDEX (UNIQUE)) (Cost=2 Card=32 Bytes=544)
  18    5             TABLE ACCESS (BY INDEX ROWID) OF 'BV_CATEGORY' (TABLE) (Cost=3 Card=1 Bytes=25)
  19   18               INDEX (RANGE SCAN) OF 'BV_CATEGORY_KEY_IDX' (INDEX (UNIQUE)) (Cost=2 Card=1)
  20    4           TABLE ACCESS (BY INDEX ROWID) OF 'BV_USER_PROFILE' (TABLE) (Cost=1 Card=1 Bytes=22)
  21   20             INDEX (UNIQUE SCAN) OF 'BV_USER_PROFILE_ID_IDX' (INDEX (UNIQUE)) (Cost=0 Card=1)
  22    3         INDEX (UNIQUE SCAN) OF 'BV_USER_PK' (INDEX (UNIQUE)) (Cost=0 Card=1 Bytes=6)
  23    2       INDEX (UNIQUE SCAN) OF 'BV_EP_UPROF_QVAL_PK' (INDEX (UNIQUE)) (Cost=1 Card=1 Bytes=17)




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     177178  consistent gets
      17749  physical reads
          0  redo size
        453  bytes sent via SQL*Net to client
        279  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
          5  rows processed

INDEX_NAME                     COLUMN_NAME                    UNIQUENES TABLESPACE_NAME
------------------------------ ------------------------------ --------- ----------------------------
BV_USER_ALIAS_INDEX            USER_ALIAS                     UNIQUE    DEV07_PERM

BV_USER_PK                     USER_ID                        UNIQUE    DEV07_PERM

BV_USR_ACCTID_IDX              ACCOUNT_ID                     NONUNIQUE DEV07_PERM

BV_USR_STATE_IDX               USER_STATE                     NONUNIQUE DEV07_PERM

INDEX_NAME                     COLUMN_NAME                    UNIQUENES TABLESPACE_NAME
------------------------------ ------------------------------ --------- ----------------------------
BV_CATEGORY_DELETED_IDX        DELETED                        NONUNIQUE DEV07_PERM

BV_CATEGORY_KEY_IDX            NAME                           UNIQUE    DEV07_PERM
                               CONTENT_TYPE                   UNIQUE    DEV07_PERM
                               PARENT_OID                     UNIQUE    DEV07_PERM
                               STORE_ID                       UNIQUE    DEV07_PERM

BV_CATEGORY_PK                 OID                            UNIQUE    DEV07_PERM

BV_CATEGORY_STATUS_IDX         STATUS                         NONUNIQUE DEV07_PERM


INDEX_NAME                     COLUMN_NAME                    UNIQUENES TABLESPACE_NAME
------------------------------ ------------------------------ --------- ----------------------------
BV_CATEGORY_STR_IDX            STORE_ID                       NONUNIQUE DEV07_PERM

IDX_BV_CATEGORY_NAM_SID        SYS_NC00039$                   NONUNIQUE DEV07_PERM
                               STORE_ID                       NONUNIQUE DEV07_PERM

INDEX_NAME                     COLUMN_NAME                    UNIQUENES TABLESPACE_NAME
------------------------------ ------------------------------ --------- ----------------------------
BV_EP_QUAL_DELETED_IDX         DELETED                        NONUNIQUE DEV07_PERM

BV_EP_QUAL_STATUS_IDX          STATUS                         NONUNIQUE DEV07_PERM

BV_EP_QUAL_VALUE_KEY_IDX       QV_NAME                        UNIQUE    DEV07_PERM
                               STORE_ID                       UNIQUE    DEV07_PERM

BV_EP_QUAL_VALUE_PK            OID                            UNIQUE    DEV07_PERM

BV_EP_QUAL_VALUE_STR_IDX       STORE_ID                       NONUNIQUE DEV07_PERM


INDEX_NAME                     COLUMN_NAME                    UNIQUENES TABLESPACE_NAME
------------------------------ ------------------------------ --------- ----------------------------
IDX_BV_EP_QUAL_VALU_FRN_NAM    SYS_NC00010$                   NONUNIQUE DEV07_PERM

INDEX_NAME                     COLUMN_NAME                    UNIQUENES TABLESPACE_NAME
------------------------------ ------------------------------ --------- ----------------------------
BV_USER_PROFILE_ID_IDX         USER_ID                        UNIQUE    DEV07_PERM

BV_USER_PROF_LAST_IDX          HCC_LAST_NAME                  NONUNIQUE DEV07_PERM

USER_PROF_HCC_FIRST_NAME       HCC_FIRST_NAME                 NONUNIQUE DEV07_PERM

INDEX_NAME                     COLUMN_NAME                    UNIQUENES TABLESPACE_NAME
------------------------------ ------------------------------ --------- ----------------------------
BV_EP_UPROF_QVAL_KEY_IDX       USER_ID                        NONUNIQUE DEV07_PERM

BV_EP_UPROF_QVAL_PK            USER_ID                        UNIQUE    DEV07_PERM
                               QID                            UNIQUE    DEV07_PERM
                               QVID                           UNIQUE    DEV07_PERM

begin
 dbms_stats.gather_schema_stats( 
ownname          => 'DEV07', 
options          => 'GATHER AUTO', 
estimate_percent => dbms_stats.auto_sample_size, 
method_opt       => 'for all columns size repeat', 
cascade          => true
);
end;

create index i1 on bv_User (User_Id,User_State,User_Alias);

create index i1 on bv_User (User_Id);
create index i2 on bv_User (User_State);
create index i3 on bv_User (User_Alias);

SELECT   Up.hcc_Last_Name,
         Up.hcc_First_Name,
         u.User_Alias,
         dIs.User_Id,
         dIs.Friendly_Name AS District,
         qv.Friendly_Name
FROM     bv_User_Profile Up,
         bv_User u,
         bv_ep_qual_Value qv,
         bv_ep_UprOf_qval uq,
         bv_Category c,
         (SELECT u2.User_Id,
                 qv2.Friendly_Name
          FROM   bv_User u2,
                 bv_ep_UprOf_qval uq2,
                 bv_ep_qual_Value qv2,
                 bv_Category c2
          WHERE  u2.User_Id = uq2.User_Id
                 AND uq2.qvId = qv2.oId
                 AND qv2.Status = 1
                 AND qv2.Deleted = 0
                 AND qv2.Store_Id = 107
                 AND uq2.qId = c2.oId
                 AND c2.NAME = 'ATLAS LQA Districts'
                 AND c2.Store_Id = 107) dIs
WHERE    u.User_Id = Up.User_Id
         AND u.User_State = 0
         AND u.User_Id = dIs.User_Id
         AND u.User_Id = uq.User_Id
         AND uq.qvId = qv.oId
         AND qv.Store_Id = 107
         AND qv.Status = 1
         AND qv.Deleted = 0
         AND qv.Friendly_Name IN ('Grading Manager',
                                  'Reviewer')
         AND uq.qId = c.oId
         AND c.Store_Id = 107
         AND c.Status = 1
         AND c.Deleted = 0
         AND c.NAME = 'ATLAS LQA User Role'
ORDER BY dIs.Friendly_Name ASC