select d.*, h.*, a.agent_name, p.part_name
from doc_data d
    join doc_heads h on d.doc_id = h.doc_id
    join ref_agents a on h.agent_id = a.id
    join ref_parts p on d.table_ref = :const_parts_table_id and d.ref_id = p.id
where 
   a.agent_name like '%хрен%' 
   and a.agent_name like '%знает%' 
   and a.agent_name like '%кто%' 
   and p.part_name like '%фиг%'
   and a.part_name like '%поймёт%' 
   and a.part_name like '%что%' ;

with
agent_smart_filter as(
    select /*+ index_ffs(agents_agent_name) */
        a.id, a.agent_name 
    from ref_agents a
    where
        a.agent_name like '%хрен%' 
        and a.agent_name like '%знает%' 
        and a.agent_name like '%кто%' 
)
,part_smart_filter as(
    select /*+ index_ffs(parts_part_name) */
        p.id, p.part_name
    from ref_parts p
    where
        and p.part_name like '%фиг%'
        and a.part_name like '%поймёт%' 
        and a.part_name like '%что%'
)

select d.*, h.*, a.agent_name
from doc_data d
    join doc_heads h on d.doc_id = h.doc_id
    join agent_smart_filter af  on h.agent_id = af.id
    join part_smart_filter pf on d.table_ref = :const_parts_table_id and d.ref_id = pf.id
;

create table aaa(id number, name varchar(50));
insert into aaa select level+1 id, dbms_random.string('u', 50) from dual connect by level<=1000000;
commit;
create index aaa_name on aaa(name);

set timing on
set autot on

select count(*) from aaa where name like '%ABC%' and name like '%QWE%' and name like '%RTY%';

  COUNT(*)
----------
         0

Elapsed: 00:00:00.20

Execution Plan
----------------------------------------------------------
Plan hash value: 3069757864

----------------------------------------------------------------------------------
| Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |     1 |    27 |  1282   (2)| 00:00:06 |
|   1 |  SORT AGGREGATE       |          |     1 |    27 |            |          |
|*  2 |   INDEX FAST FULL SCAN| AAA_NAME |     1 |    27 |  1282   (2)| 00:00:06 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("NAME" IS NOT NULL AND "NAME" IS NOT NULL AND "NAME" IS NOT
              NULL AND "NAME" LIKE '%ABC%' AND "NAME" LIKE '%QWE%' AND "NAME" LIKE
              '%RTY%')

Note
-----
   - dynamic sampling used for this statement (level=2)


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