Oracle SQL query running slow, full table scan on primary key, why?
<p>I have a problem with a piece of code, I can't understand why the below query is doing a full table scan on the works table when wrk.cre_surr_id is the primary key. The stats on both tables are both up to date below are the indexes on both tables.</p>

<pre><code> TABLE INDEXES
 WORKS

 INDEX NAME         UNIQUE  LOGGING     COLUMN NAME             ORDER
 WRK_I1             N       NO          LOGICALLY_DELETED_Y     Asc
 WRK_ICE_WRK_KEY    N       YES         ICE_WRK_KEY             Asc
 WRK_PK             Y       NO          CRE_SURR_ID             Asc
 WRK_TUNECODE_UK    Y       NO          TUNECODE                Asc

TLE_TITLE_TOKENS

INDEX NAME          UNIQUE  LOGGING     COLUMN NAME             ORDER
TTT_I1              N       YES         TOKEN_TYPE,             Asc
                                        SEARCH_TOKEN,
                                        DN_WRK_CRE_SURR_ID
TTT_TLE_FK_1        N       YES         TLE_SURR_ID
</code></pre>

<p>Problem query below. It has a cost of 245,876 which seems high, it's doing a FULL TABLE SCAN of the WORKS table which has 21,938,384 rows in the table. It is doing an INDEX RANGE SCAN of the TLE_TITLE_TOKENS table which has 19,923,002 rows in it. On the explain plan also is an INLIST ITERATOR which I haven't a clue what it means but it I think it's to do with having an "in ('E','N')" in my sql query.</p>

<pre><code> SELECT wrk.cre_surr_id 
 FROM   works wrk, 
        tle_title_tokens ttt      
 WHERE ttt.dn_wrk_cre_surr_id = wrk.cre_surr_id
 AND wrk.logically_deleted_y IS NULL   
 AND ttt.token_type in ('E','N')  
 AND  ttt.search_token LIKE 'BELIEVE'||'%'
</code></pre>

<p>When I break the query down and do a simple select from the TLE_TITLE_TOKENS table I get 280,000 records back.</p>

<pre><code> select ttt.dn_wrk_cre_surr_id 
 from tle_title_tokens ttt
 where ttt.token_type in ('E','N') 
 and ttt.search_token LIKE 'BELIEVE'||'%'
</code></pre>

<p>How do I stop it doing a FULL TABLE scan on the WORKS table. I could put a hint on the query but I would have thought Oracle would be clever enough to know to use the index without a hint.</p>

<p>Also on TLE_TITLE_TOKENS table would it be better to create a fuction based index on the column SEARCH_TOKEN as users seem to do LIKE % searches on this field. What would that fuction based index look like.</p>

<p>I'm running on an Oracle 11g database.</p>

<p>Thanks in Advance to any answers.</p>