SQL> explain plan for SELECT accounts account_id_dups, account_id 2 FROM (SELECT MAX 3 (CTX_QUERY.COUNT_HITS 4 ('table_name_idx', 5 'NDATA (full_name, ' || t2.full_name || ') AND ' || 6 'NDATA (full_address, ' || t2.full_address || ')')) 7 AS accounts, 8 t1.account_id 9 FROM table_name t1, table_name t2 10 WHERE t1.account_id != t2.account_id 11 AND CONTAINS 12 (t1.names_and_addresses, 13 'NDATA (full_name, ' || t2.full_name || ') AND ' || 14 'NDATA (full_address, ' || t2.full_address || ')', 15 1) > 30 16 GROUP BY t1.account_id) 17 WHERE accounts >= 2 18 ORDER BY accounts DESC, account_id; SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- Plan hash value: 1580261012 ---------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2 | 588 | 24 (5)| 00:00:01 | | 1 | SORT ORDER BY | | 2 | 588 | 24 (5)| 00:00:01 | |* 2 | FILTER | | | | | | | 3 | SORT GROUP BY NOSORT | | 2 | 588 | 24 (5)| 00:00:01 | | 4 | NESTED LOOPS | | 2 | 588 | 23 (0)| 00:00:01 | | 5 | TABLE ACCESS BY INDEX ROWID| TABLE_NAME | 61 | 1647 | 1 (0)| 00:00:01 | PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- | 6 | INDEX FULL SCAN | TABLE_NAME_GSMIDX | 61 | | 1 (0)| 00:00:01 | |* 7 | TABLE ACCESS FULL | TABLE_NAME | 1 | 267 | 0 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter(MAX("CTX_QUERY"."COUNT_HITS"('table_name_idx','NDATA (full_name, '||"T2"."FULL_NAME"||') AND '||'NDATA (full_address, '||"T2"."FULL_ADDRESS"||')'))>=2) 7 - filter("T1"."account_id"<>"T2"."account_id" AND "CTXSYS"."CONTAINS"("T1"."NAMES_AND_ADDRESSES",'NDATA (full_name, '||"T2"."FULL_NAME"||') PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- AND '||'NDATA (full_address, '||"T2"."FULL_ADDRESS"||')',1)>30) Note ----- - dynamic sampling used for this statement (level=2) 27 rows selected. -------------------------------PRODUCTION TABLE-------------------------------------------------------- SQL> explain plan for SELECT accounts,account_id 2 FROM (SELECT MAX 3 (CTX_QUERY.COUNT_HITS 4 ('cust_data_idx', 5 'NDATA (full_name, ' || t2.full_name || ') AND ' || 6 'NDATA (full_address, ' || t2.full_address || ')')) 7 AS accounts, 8 t1.account_id 9 FROM customer_data t1, customer_data t2 10 WHERE t1.account_id != t2.account_id 11 AND CONTAINS 12 (t1.names_and_addresses, 13 'NDATA (full_name, ' || t2.full_name || ') AND ' || 14 'NDATA (full_address, ' || t2.full_address || ')', 15 1) > 30 16 GROUP BY t1.account_id) 17 WHERE accounts >= 7 18 ORDER BY accounts DESC, account_id; Explained. SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- Plan hash value: 1660243679 ---------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 26G| 10T| 965M (2)|999:59:59 | | 1 | SORT ORDER BY | | 26G| 10T| 965M (2)|999:59:59 | |* 2 | FILTER | | | | | | | 3 | HASH GROUP BY | | 26G| 10T| 965M (2)|999:59:59 | | 4 | NESTED LOOPS | | 26G| 10T| 958M (1)|999:59:59 | | 5 | TABLE ACCESS FULL | CUSTOMER_DATA | 7333K| 2874M| 28422 (2)| 00:06:38 | PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- |* 6 | TABLE ACCESS BY INDEX ROWID| CUSTOMER_DATA | 3667 | 77007 | 958M (1)|999:59:59 | |* 7 | DOMAIN INDEX | CUST_DATA_IDX | | | 1 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter(MAX("CTX_QUERY"."COUNT_HITS"('cust_data_idx','NDATA (full_name, '||"T2"."FULL_NAME"||') AND '||'NDATA (full_address, '||"T2"."FULL_ADDRESS"||')'))>=7) 6 - filter("T1"."account_id"<>"T2"."account_id") 7 - access("CTXSYS"."CONTAINS"("T1"."NAMES_AND_ADDRESSES",'NDATA (full_name, PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- '||"T2"."FULL_NAME"||') AND '||'NDATA (full_address, '||"T2"."FULL_ADDRESS"||')',1)>30) Note ----- - dynamic sampling used for this statement (level=2) 27 rows selected. 19