This is basically "select count(*) from cc_users", but I've copied the source for the view into the query. Performing the query with just the view name gave similar results.
SQL> select count(*)
from (select o.*, pa.*, pe.*, u.*, mr.member_state, mr.rel_id, u.rowid as row_id
from acs_objects o, parties pa, persons pe, users u, group_member_map m, membership_rels mr
where o.object_id = pa.party_id
and pa.party_id = pe.person_id
and pe.person_id = u.user_id
and u.user_id = m.member_id
and m.group_id = acs.magic_object_id('registered_users')
and m.rel_id = mr.rel_id
and m.container_id = m.group_id) cc_users;
SQL> 2 3 4 5 6 7 8 9 10
COUNT(*)
----------
45603
Elapsed: 00:00:44.51
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=85 Card=1 Bytes=51)
1 0 SORT (AGGREGATE)
2 1 NESTED LOOPS (Cost=85 Card=1 Bytes=51)
3 2 NESTED LOOPS (Cost=85 Card=1 Bytes=47)
4 3 NESTED LOOPS (Cost=85 Card=1 Bytes=43)
5 4 NESTED LOOPS (Cost=85 Card=1 Bytes=39)
6 5 NESTED LOOPS (Cost=85 Card=1 Bytes=35)
7 6 INDEX (FAST FULL SCAN) OF 'GROUP_ELEMENT_INDEX
_PK' (UNIQUE) (Cost=85 Card=1 Bytes=31)
8 6 INDEX (UNIQUE SCAN) OF 'USERS_PK' (UNIQUE)
9 5 INDEX (UNIQUE SCAN) OF 'PERSONS_PK' (UNIQUE)
10 4 INDEX (UNIQUE SCAN) OF 'PARTIES_PK' (UNIQUE)
11 3 INDEX (UNIQUE SCAN) OF 'MEMBERSHIP_REL_REL_ID_PK' (U
NIQUE)
12 2 INDEX (UNIQUE SCAN) OF 'ACS_OBJECTS_PK' (UNIQUE)
Statistics
----------------------------------------------------------
113786 recursive calls
0 db block gets
456883 consistent gets
0 physical reads
0 redo size
381 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1 rows processed
Now, I've changed the query to join on acs_magic_objects. Query time drops from 44.5 seconds to 8.6 seconds!
SQL> select count(*)
from (select o.*, pa.*, pe.*, u.*, mr.member_state, mr.rel_id, u.rowid as row_id
from acs_objects o, parties pa, persons pe, users u, group_member_map m, membership_rels mr, acs_magic_objects mo
where o.object_id = pa.party_id
and pa.party_id = pe.person_id
and pe.person_id = u.user_id
and u.user_id = m.member_id
and m.group_id = mo.object_id
and mo.name = 'registered_users'
and m.rel_id = mr.rel_id
and m.container_id = m.group_id) cc_users;
SQL> 2 3 4 5 6 7 8 9 10 11
COUNT(*)
----------
45603
Elapsed: 00:00:08.62
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=1 Bytes=71)
1 0 SORT (AGGREGATE)
2 1 NESTED LOOPS (Cost=5 Card=1 Bytes=71)
3 2 NESTED LOOPS (Cost=5 Card=1 Bytes=67)
4 3 NESTED LOOPS (Cost=5 Card=1 Bytes=63)
5 4 NESTED LOOPS (Cost=5 Card=1 Bytes=59)
6 5 NESTED LOOPS (Cost=5 Card=1 Bytes=55)
7 6 NESTED LOOPS (Cost=5 Card=1 Bytes=51)
8 7 TABLE ACCESS (BY INDEX ROWID) OF 'ACS_MAGIC_
OBJECTS' (Cost=1 Card=1 Bytes=20)
9 8 INDEX (UNIQUE SCAN) OF 'ACS_MAGIC_OBJECTS_
PK' (UNIQUE)
10 7 INDEX (UNIQUE SCAN) OF 'GROUP_ELEMENT_INDEX_
PK' (UNIQUE) (Cost=1 Card=1 Bytes=31)
11 10 INDEX (RANGE SCAN) OF 'GROUP_ELEM_IDX_GROU
P_IDX' (NON-UNIQUE) (Cost=1 Card=348)
12 6 INDEX (UNIQUE SCAN) OF 'USERS_PK' (UNIQUE)
13 5 INDEX (UNIQUE SCAN) OF 'PERSONS_PK' (UNIQUE)
14 4 INDEX (UNIQUE SCAN) OF 'PARTIES_PK' (UNIQUE)
15 3 INDEX (UNIQUE SCAN) OF 'MEMBERSHIP_REL_REL_ID_PK' (U
NIQUE)
16 2 INDEX (UNIQUE SCAN) OF 'ACS_OBJECTS_PK' (UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
273823 consistent gets
0 physical reads
0 redo size
381 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
