Oracle SQL - Cannot remove index usage by hints?

Question

This was a challenge from uni test.
The requierment was to solve It using hints.

---

I'm trying to complete a query where I want it to use nested loops and NO indices. For this, I wrote the query:

select /*+ USE_NL(fulltable) NO_INDEX(fulltable) */  sum(QUANTITY) summarized from
    (select /*+ USE_NL(sz1, sz2, c1) NO_INDEX(sz1, sz2, c1) */ distinct *     
     from SZTABLE sz1 
          natural join SZTABLE2 sz2
          natural join CTABLE c1
          where city= 'Newark' and COLOR= 'red') fulltable;

However, upon explaining the query and writing it out, I get:

SELECT STATEMENT +  + 
  SORT + AGGREGATE + 
    VIEW +  + 
      HASH + UNIQUE + 
        NESTED LOOPS +  + 
          NESTED LOOPS +  + 
            HASH JOIN +  + 
              JOIN FILTER + CREATE + :BF0000
                TABLE ACCESS + FULL + CTABLE
              VIEW +  + VW_DTP_5C333E12
                HASH + UNIQUE + 
                  JOIN FILTER + USE + :BF0000
                    TABLE ACCESS + FULL + SZTABLE
            INDEX + UNIQUE SCAN + SZO_SZKOD
          TABLE ACCESS + BY INDEX ROWID + SZTABLE2

Apparently, the query still uses indices, even though I explicitly asked it not to. How can I fix this? Why is there still and index used for ROWID?

(The plan was written out by the snippet below.)

SELECT LPAD(' ', 2*(level-1))||operation||' + '||options||' + '||object_name terv
FROM plan_table
START WITH id = 0 AND statement_id = 'plan1'
CONNECT BY PRIOR id = parent_id AND statement_id = 'plan1'
ORDER SIBLINGS BY position;

Answer 1

Strange question but you can get all your index on tables involving into query and execute this:

alter index <Index_Name> unusable

Or rewrite query

select sum(QUANTITY) summarized from
    (select distinct *     
     from SZTABLE sz1 
          join SZTABLE2 sz2 on sz1.id+0 = sz2.id+0 --put your join condition, cause I don't know you table columns
          join CTABLE c1 on c.id+0 = sz2.id+0 --put your join condition
          where city || ''= 'Newark' and COLOR|| ''= 'red') fulltable;

Look at combination

z1.id+0 
city || ''

Results are the same but CBO see expr and can't find it in index

Answer 2

You had syntax errors.

• use_nl requires multiple tables to be separated by spaces and not commas.

• no_index works on a single table.

Please try, for the inner query.

use_nl(sz1 sz2 c1) no_index(sz1) no_index(sz2) no_index(c1)

You can remove anything else