Oracle SQL query running slow, full table scan on primary key, why?

Question

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.

 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

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.

 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'||'%'

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

 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'||'%'

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.

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.

I'm running on an Oracle 11g database.

Thanks in Advance to any answers.

Does a composite index on both cre_surr_id and logically_deleted_y help? — Thilo, Apr 21 '16 at 09:44
Try to use explain plan on your query so you could identify where is the problem in your query. Check https://docs.oracle.com/cd/B19306_01/server.102/b14211/ex_plan.htm#g42231 — Elie M, Apr 21 '16 at 09:50
What is the plan for your simple select from the TLE_TITLE_TOKENS? Does it uses index? — Serg, Apr 21 '16 at 10:42
keep in mind: IS NULL implies always a full table scan if you are using it on an indexed col, because the index does not contain the NULL values, that's why oracle need iterate through a whole value-set — Thomas, Apr 21 '16 at 11:13
@Thomas: A composite index will contain NULL values (unless all of the columns are NULL at once). — Thilo, Apr 21 '16 at 11:29
but WRK_I1 is not a composite index, and both solution containing below the IS NULL condition. ergo logically_deleted_y need to be filled with something, cause it seems that col maybe is a flag — Thomas, Apr 21 '16 at 11:38
anyway, in other threads came up that theory already in a thousand times, full table scan is not a monster. oracle CBO always will choose the best execution. You can force oracle to use index and you can compare the two explain plans (full table scan vs. index used) — Thomas, Apr 21 '16 at 11:41
first of all: https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:9422487749968 — Thomas, Apr 21 '16 at 11:45
please post the execution plan, see [here](http://stackoverflow.com/questions/34975406/how-to-describe-performance-issue-in-relational-database?answertab=active#tab-top) how to get it. Without knowing the plan it's all a speculation... — Marmite Bomber, Apr 21 '16 at 15:28

score 1 · Answer 1 · answered Apr 21 '16 at 11:03

First, rewrite the query using a join:

SELECT wrk.cre_surr_id 
FROM tle_title_tokens ttt JOIN
     works wrk 
     ON ttt.dn_wrk_cre_surr_id = wrk.cre_surr_id 
WHERE wrk.logically_deleted_y IS NULL  AND
      ttt.token_type in ('E', 'N')  AND
      ttt.search_token LIKE 'BELIEVE'||'%';

You should be able to speed this query by using indexes. It is not clear what the best index is. I would suggest either tle_title_tokens(search_token, toekn_type, dn_wrk_cre_surr_id) and works(cre_surr_id, logically_deleted_y).

Another possibility is to write the query using EXISTS, such as:

SELECT wrk.cre_surr_id 
FROM works wrk 
WHERE wrk.logically_deleted_y IS NULL AND
      EXISTS (SELECT 1
              FROM tle_title_tokens ttt
              WHERE ttt.dn_wrk_cre_surr_id = wrk.cre_surr_id AND
                    ttt.token_type IN ('N', 'E') AND
                    ttt.search_token LIKE 'BELIEVE'||'%'
             ) ;

For this version, you want indexes on works(logically_deleted_y, cre_surr_id) and tle_title_tokens(dn_wrk_cre_surr_id, token_type, search_token).

Adding that index on `works(cre_surr_id, logically_deleted_y)` should improve things. It would make accessing the table itself unnecessary as only these two columns are needed for the query. — Thilo, Apr 21 '16 at 11:31

score 0 · Answer 2 · answered Apr 21 '16 at 10:32

0

try this:

SELECT /*+ leading(ttt) */ 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'||'%'

answered Apr 21 '16 at 10:32

saphsys

96
4

still doing a full table scan on works. This is part of a bigger query but I've chopped it down to this part as I think this is where the problem is. The query is built up into a string l_sql := 'select .... from' and then executed as a cursor. The last line of the query in the string is "ttt.search_token LIKE :title||'%'" , could it be the bind variable, if I run the query with the line "'ttt.search_token LIKE 'BELIEVE'||'%' it uses the index but with the bind variable it doesn't – Shaun Kinnair Apr 21 '16 at 10:57
The bind variable is passed into the query, is there another way I can do this without the bind variable in the SQL built up string – Shaun Kinnair Apr 21 '16 at 10:59

score 0 · Answer 3 · answered Apr 21 '16 at 14:17

Out of the 19,923,002 rows in LE_TITLE_TOKENS,

How many records have TOKEN_TYPE 'E', how many have 'N'? Are there any other TokenTypes? If yes, then how many are they put together?

If E and N put together forms a small part of the total records, then check if histogram statistics are updated for that column.

The execution plan depends on how many records are being selected from LE_TITLE_TOKENS out of the 20M records for the given filters.

score 0 · Answer 4 · answered Apr 21 '16 at 15:24

I'm assuming this index definition

 create index works_idx on works (cre_surr_id,logically_deleted_y);
 create index title_tokens_idx on  tle_title_tokens(search_token,token_type,dn_wrk_cre_surr_id);

There are typically two possible scenarios to execute the join

NESTED LOOPS which access the inner table WORKS using index, but repeatedly in a loop for each row in the outer table

HASH JOIN which access the WORKS using FULL SCAN but only once.

It is not possible to say that one option is bad and the other good.

Nested loops is better if there are only few row in the outer table (few loops), but with increasing number of records in the outer table (TOKEN) gets slower and slower and at some number of row the HASH JOIN is bettwer.

How to see what execution plan is better? Simple force Oracle using hint to run both scanarios and compare the elapsed time.

In your case you should see those two execution plans

HASH JOIN

-----------------------------------------------------------------------------------------------
| Id  | Operation          | Name             | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                  |   207K|    10M|       |  2439   (1)| 00:00:30 |
|*  1 |  HASH JOIN         |                  |   207K|    10M|  7488K|  2439   (1)| 00:00:30 |
|*  2 |   INDEX RANGE SCAN | TITLE_TOKENS_IDX |   207K|  5058K|       |    29   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| WORKS            |   893K|    22M|       |   431   (2)| 00:00:06 |
-----------------------------------------------------------------------------------------------

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

   1 - access("TTT"."DN_WRK_CRE_SURR_ID"="WRK"."CRE_SURR_ID")
   2 - access("TTT"."SEARCH_TOKEN" LIKE 'BELIEVE%')
       filter("TTT"."SEARCH_TOKEN" LIKE 'BELIEVE%' AND ("TTT"."TOKEN_TYPE"='E' OR 
              "TTT"."TOKEN_TYPE"='N'))
   3 - filter("WRK"."LOGICALLY_DELETED_Y" IS NULL)

NESTED LOOPS

--------------------------------------------------------------------------------------
| Id  | Operation         | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                  |   207K|    10M|   414K  (1)| 01:22:56 |
|   1 |  NESTED LOOPS     |                  |   207K|    10M|   414K  (1)| 01:22:56 |
|*  2 |   INDEX RANGE SCAN| TITLE_TOKENS_IDX |   207K|  5058K|    29   (0)| 00:00:01 |
|*  3 |   INDEX RANGE SCAN| WORKS_IDX        |     1 |    26 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------


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

   2 - access("TTT"."SEARCH_TOKEN" LIKE 'BELIEVE%')
       filter("TTT"."SEARCH_TOKEN" LIKE 'BELIEVE%' AND 
              ("TTT"."TOKEN_TYPE"='E' OR "TTT"."TOKEN_TYPE"='N'))
   3 - access("TTT"."DN_WRK_CRE_SURR_ID"="WRK"."CRE_SURR_ID" AND 
              "WRK"."LOGICALLY_DELETED_Y" IS NULL)

My gues is the (with 280K loops) the hash join (i.e. FULLTABLE SCAN) will be bettwer, but it could be that you recognise that nested loops should be used. In this case the optimize doesn't correct recognise the switching point between nested loops and hash join. Common cause of this is wrong or missing system statistics or improper optimizer parameters.

Oracle SQL query running slow, full table scan on primary key, why?

4 Answers4