SELECT qv.friendly_name AS VALUE 
FROM   bv_category c, 
       bv_content_ref cr, 
       bv_ep_qual_value qv, 
       bv_ep_uprof_qval uq 
WHERE  c.content_type = 315 
       AND c.NAME = 'GenericRole' 
       AND c.store_id IN (SELECT s.store_id 
                          FROM   bv_store s 
                          WHERE  s.store_name = 'CLIFT') 
       AND c.oid = cr.parent_oid 
       AND qv.oid = cr.oid 
       AND qv.status = 1 
       AND qv.deleted = 0 
       AND uq.user_id = 10082863 
       AND uq.qvid = qv.oid 

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=21 Card=1 Bytes=81
          )

   1    0   NESTED LOOPS (Cost=21 Card=1 Bytes=81)
   2    1     HASH JOIN (Cost=21 Card=108 Bytes=7560)
   3    2       MERGE JOIN (CARTESIAN) (Cost=8 Card=108 Bytes=4752)
   4    3         NESTED LOOPS (Cost=4 Card=2 Bytes=66)
   5    4           TABLE ACCESS (BY INDEX ROWID) OF 'BV_STORE' (TABLE
          ) (Cost=1 Card=1 Bytes=9)

   6    5             INDEX (UNIQUE SCAN) OF 'BV_STORE_NAME_INDEX' (IN
          DEX (UNIQUE)) (Cost=0 Card=1)

   7    4           TABLE ACCESS (BY INDEX ROWID) OF 'BV_CATEGORY' (TABLE) (Cost=3 Card=2 Bytes=48)

   8    7             INDEX (RANGE SCAN) OF 'BV_CATEGORY_KEY_IDX' (IND
          EX (UNIQUE)) (Cost=2 Card=2)

   9    3         BUFFER (SORT) (Cost=5 Card=57 Bytes=627)
  10    9           INDEX (RANGE SCAN) OF 'BV_EP_UPROF_QVAL_PK' (INDEX
           (UNIQUE)) (Cost=2 Card=57 Bytes=627)

  11    2       TABLE ACCESS (FULL) OF 'BV_EP_QUAL_VALUE' (TABLE) (Cos
          t=12 Card=1147 Bytes=29822)

  12    1     INDEX (UNIQUE SCAN) OF 'BV_CONTENT_REF_PK' (INDEX (UNIQU
          E)) (Cost=0 Card=1 Bytes=11)

select count(*) from bv_category--44181
select count(*) from bv_content_ref--56472
select count(*) from bv_ep_qual_value--2294
select count(*) from bv_ep_uprof_qval--5238265

SELECT qv.friendly_name AS VALUE
FROM   bv_category c, 
       bv_content_ref cr, 
       bv_ep_qual_value qv, 
       bv_ep_uprof_qval uq 

SELECT count(*) FROM bv_ep_qual_value qv WHERE qv.status = 1;---2294
SELECT count(*) FROM bv_ep_qual_value qv WHERE qv.deleted = 0;--1983

Eliminate C, CR, & UQ tables out of the FROM clause; because they contribute no data to SELECT clause.

Subordinate them into the WHERE clause.

How can we subordinate them into where clause.
by EXISTS or IN as part of the WHERE clause

AND qv.status = 1 
AND qv.deleted = 0 

AND qv.status = 1 
AND qv.deleted = 0 

       AND c.store_id IN (SELECT s.store_id 
                          FROM   bv_store s 
                          WHERE  s.store_name = 'CLIFT') 

select count(*) from bv_ep_qual_value where status=1 and deleted=0--1983

select * from bv_category where store_id in(SELECT s.store_id 

FROM   bv_store s WHERE  s.store_name = 'CLIFT')--254

SELECT qv.friendly_name AS VALUE 
FROM   bv_category c, 
       bv_content_ref cr, 
       bv_ep_qual_value qv       
WHERE  c.content_type = 315 
       AND c.NAME = 'GenericRole' 
       AND c.store_id IN (SELECT s.store_id 
                          FROM   bv_store s 
                          WHERE  s.store_name = 'CLIFT') 
       AND c.oid = cr.parent_oid 
       AND qv.oid = cr.oid 
       AND qv.status = 1 
       AND qv.deleted = 0 
       AND qv.oid in ( select uq.qvid from bv_ep_uprof_qval uq
                       where  uq.user_id = 10082863)

  3    2       MERGE JOIN (CARTESIAN) (Cost=8 Card=108 Bytes=4752)

AND uq.user_id = 10082863 

There is already concatenate index on(name,content_type,parent_oid,store_oid) in bv_category;
there is concatenate index on (user_id,qid,qvid)in bv_ep_uprof_qval
There is index on OID in bv_ep_qual_value.
there is index on oid and parent oid in bv_content_ref

Make sure you have an index on bv_ep_uprof_qval(user_id)
Make sure you have an index on bv_ep_uprof_qval(qvid)

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=11 Card=1 Bytes=89
          )

   1    0   NESTED LOOPS (Cost=11 Card=1 Bytes=89)
   2    1     NESTED LOOPS (Cost=9 Card=1 Bytes=76)
   3    2       NESTED LOOPS (Cost=8 Card=1 Bytes=49)
   4    3         NESTED LOOPS (Cost=4 Card=1 Bytes=37)
   5    4           TABLE ACCESS (BY INDEX ROWID) OF 'BV_STORE' (TABLE
          ) (Cost=1 Card=1 Bytes=9)

   6    5             INDEX (UNIQUE SCAN) OF 'BV_STORE_NAME_INDEX' (IN
          DEX (UNIQUE)) (Cost=0 Card=1)

   7    4           TABLE ACCESS (BY INDEX ROWID) OF 'BV_CATEGORY' (TA
          BLE) (Cost=3 Card=1 Bytes=28)

   8    7             INDEX (RANGE SCAN) OF 'BV_CATEGORY_KEY_IDX' (IND
          EX (UNIQUE)) (Cost=2 Card=1)

   9    3         TABLE ACCESS (BY INDEX ROWID) OF 'BV_CONTENT_REF' (T
          ABLE) (Cost=4 Card=1 Bytes=12)

  10    9           INDEX (RANGE SCAN) OF 'BV_CNTRF_IDX' (INDEX) (Cost
          =2 Card=25)

  11    2       TABLE ACCESS (BY INDEX ROWID) OF 'BV_EP_QUAL_VALUE' (T
          ABLE) (Cost=1 Card=1 Bytes=27)

  12   11         INDEX (UNIQUE SCAN) OF 'BV_EP_QUAL_VALUE_PK' (INDEX
          (UNIQUE)) (Cost=0 Card=1)

  13    1     INDEX (RANGE SCAN) OF 'BV_EP_UPROF_QVAL_PK' (INDEX (UNIQ
          UE)) (Cost=2 Card=1 Bytes=13)