SQL> set autotrace on SQL>SELECT TO_CHAR(A.ID) A118,A.ASSET_GROUP_XID A2,A.ACL_ID A106,A.OWNER A4,CONVERT(A.NAME,'WE8MSWIN1252') A1,A.FIRST_VERSION_CREATED_BY A15,LPAD(LTRIM(TO_CHAR(A.DATE_CREATED,'SYYYYMMDDHH24MISS')),15,'+') A18,LPAD(LTRIM(TO_CHAR(A.FIRST_VERSION_DATE_CREATED,'SYYYYMMDDHH24MISS')),15,'+') A16,TO_CHAR(A.ASSET_SIZE) A7,A.CHECKED_OUT_BY A23,A.ARCHIVE_STATUS A27,A.STORAGE_CHANGE_REQUEST A110,A.HAS_HISTORY A74,TO_CHAR(A.CONTAINED_IN_COUNT) A73,TO_CHAR(A.CONTAINER_OF_COUNT) A72,TO_CHAR(A.RELATED_WITH_COUNT) A71,CONVERT(A.FILE_TYPE,'WE8MSWIN1252') A12,CONVERT(A.FILE_CREATOR_APPLICATION,'WE8MSWIN1252') A13,CONVERT(A.FILE_EXTENSION,'WE8MSWIN1252') A9,TO_CHAR(A.REVISION) A21,A.IS_CURRENT_VERSION A109,TO_CHAR(A.FAMILY_ID) A112,LPAD(LTRIM(TO_CHAR(A.FILE_DATE_CREATED,'SYYYYMMDDHH24MISS')),15,'+') A11,LPAD(LTRIM(TO_CHAR(A.FILE_DATE_LAST_MODIFIED,'SYYYYMMDDHH24MISS')),15,'+') A10 FROM MAM_ASSETS A WHERE ((A.ACL_ID IN (65536,65685,65687,65717)) AND (A.IS_CURRENT_VERSION=1) AND A.ID IN ( SELECT DMV65538.ID ASSET_ID FROM MAM_ATTR_DOMAIN_VALUES DMV65538 WHERE (DMV65538.DOMAIN_VALUE_ID=2 OR DMV65538.DOMAIN_VALUE_ID IN (SELECT CHILD_VALUE_ID FROM MAM_DOMAIN_VALUE_HIERARCHIES START WITH DOMAIN_XID=66365 AND VALUE_ID=2 CONNECT BY PRIOR CHILD_VALUE_ID=VALUE_ID AND DOMAIN_XID=66365)) AND (DMV65538.ATTRIBUTE_XID=65538) INTERSECT SELECT SV29.ID ASSET_ID FROM MAM_ATTR_STRING_VALUES SV29 WHERE (SV29.STRING_VALUE LIKE CONVERT('%Hegde%','WE8MSWIN1252','WE8MSWIN1252')) AND (SV29.ATTRIBUTE_XID=29) INTERSECT SELECT DMV65542.ID ASSET_ID FROM MAM_ATTR_DOMAIN_VALUES DMV65542 WHERE (DMV65542.DOMAIN_VALUE_ID=1) AND (DMV65542.ATTRIBUTE_XID=65542) ) ) / 311 rows selected. Elapsed: 00:34:32.01 Execution Plan ----------------------------------------------------------------------------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=7097 Card=19321 Bytes=2685619) 1 0 NESTED LOOPS (Cost=7097 Card=19321 Bytes=2685619) 2 1 VIEW OF 'VW_NSO_1' (VIEW) (Cost=6682 Card=20799 Bytes=270387) 3 2 INTERSECTION 4 3 INTERSECTION 5 4 SORT (UNIQUE) 6 5 FILTER 7 6 TABLE ACCESS (BY INDEX ROWID) OF 'MAM_ATTR_DOMAIN_VALUES' (TABLE) (Cost=2171 Card=54542 Bytes=763588) 8 7 INDEX (RANGE SCAN) OF 'ATRVALDOM_ATRDEF_FK_I' (INDEX) (Cost=2042 Card=556031) 9 6 FILTER 10 9 CONNECT BY (WITH FILTERING) 11 10 TABLE ACCESS (BY INDEX ROWID) OF 'MAM_DOMAIN_VALUE_HIERARCHIES' (TABLE) 12 11 INDEX (RANGE SCAN) OF 'DOMVALHIE_PARENT_FK_I' (INDEX) (Cost=1 Card=2 Bytes=18) 13 10 NESTED LOOPS 14 13 BUFFER (SORT) 15 14 CONNECT BY PUMP 16 13 TABLE ACCESS (BY INDEX ROWID) OF 'MAM_DOMAIN_VALUE_HIERARCHIES' (TABLE) (Cost=1 Card=2 Bytes=22) 17 16 INDEX (RANGE SCAN) OF 'DOMVALHIE_PARENT_FK_I' (INDEX) (Cost=1 Card=2) 18 10 TABLE ACCESS (FULL) OF 'MAM_DOMAIN_VALUE_HIERARCHIES' (TABLE) (Cost=1 Card=2 Bytes=22) 19 4 SORT (UNIQUE) 20 19 TABLE ACCESS (BY INDEX ROWID) OF 'MAM_ATTR_STRING_VALUES' (TABLE) (Cost=441 Card=20799 Bytes=707166) 21 20 INDEX (SKIP SCAN) OF 'ATRVALSTR_VALUE_I' (INDEX) (Cost=10853 Card=20799) 22 3 SORT (UNIQUE) 23 22 TABLE ACCESS (BY INDEX ROWID) OF 'MAM_ATTR_DOMAIN_VALUES' (TABLE) (Cost=2394 Card=368169 Bytes=5154366) 24 23 INDEX (RANGE SCAN) OF 'ATRVALDOM_VALUE_I' (INDEX) (Cost=1654 Card=368169) 25 1 TABLE ACCESS (BY INDEX ROWID) OF 'MAM_ASSETS' (TABLE) (Cost=1 Card=1 Bytes=126) 26 25 INDEX (UNIQUE SCAN) OF 'AST_PK' (INDEX (UNIQUE)) Statistics ---------------------------------------------------------- 258 recursive calls 0 db block gets 766788 consistent gets 199263 physical reads 0 redo size 53551 bytes sent via SQL*Net to client 880 bytes received via SQL*Net from client 22 SQL*Net roundtrips to/from client 3352 sorts (memory) 0 sorts (disk) 311 rows processed On April 9, 2010