How to avoid Full Table Scan with LIKE operator

Question

I have problem while changing a query in PL/SQL oracle with different proper query. Current query :

SELECT MAX (workzone)
           FROM sccd_device_uim_tab
          WHERE NAME LIKE 18075009 || '%';

my client need the query revised because :

Full table with a % should definitely be avoided.

what solution of this issue?

Thanks in advance

Adding query from package

PROCEDURE sccd_get_impactservice_manual (
      in_faultid          IN       VARCHAR2,
      in_deviceid         IN       VARCHAR2,
      in_status           IN       VARCHAR2,
      in_opendate         IN       DATE,
      in_closedate        IN       DATE,
      out_impact_result   OUT      tcur,
      out_count_service   OUT      NUMBER,
      out_workzone        OUT      VARCHAR2,
      p_ret_char          OUT      VARCHAR2,
      p_ret_number        OUT      NUMBER,
      p_ret_msg           OUT      VARCHAR2
   )
   IS
   BEGIN
      SELECT orauser.ossa_get_sto_from_device (in_deviceid)
        INTO out_workzone
        FROM DUAL;

      IF out_workzone IS NULL
      THEN
         SELECT MAX (workzone)
           INTO out_workzone
           FROM sccd_device_uim_tab
          WHERE NAME = in_deviceid; --//this is I try to solve
          --WHERE NAME LIKE in_deviceid || '%'; //this is the current issue
      END IF;

You need to provide more details. What is the data type of NAME column? Seems to be a varchar2, but you are comparing it with a number. So, explain it. When you say there is a Full Table Scan going on, post the execution plan. Are there any indexes involved? If it is a NUMBER column, then using LIKE makes no sense. You are forcing an implicit data type conversion. For numbers use a RANGE comparison, not LIKE. — Lalit Kumar B, Mar 13 '15 at 03:33
thanks for response my question, why we use like because the type of 18075009 is not a number, it is varchar2 and column Name also. — Koyix, Mar 13 '15 at 03:53
i amaze , how its previously working with `WHERE NAME LIKE 18075009 || '%';` its a syntax error — Nagendra Nigade, Mar 13 '15 at 05:05
`WHERE NAME LIKE in_deviceid || '%'` If you have an index on the column, you should see an Index Scan and not a full table scan. See my answer. — Lalit Kumar B, Mar 13 '15 at 05:19
@NagendraNigade *its a syntax error* No, it is syntactically correct. Just that it is not a good idea to use `LIKE` with numbers like that. — Lalit Kumar B, Mar 13 '15 at 05:25
@LalitKumarB as he said `NAME` is `varchar` type then proper syntax will be like `WHERE NAME LIKE '18075009' || '%'; ` isn't it ? — Nagendra Nigade, Mar 13 '15 at 05:32
@NagendraNigade, No, you are getting confused. It will work, just that there will be an implicit data type conversion. However, it will still work. Try these two in Oracle, `SELECT 1||2 FROM dual;` and `SELECT '1'||'2' FROM dual;` Both will work, just that the first query goes for an implicit conversion from number to varchar. So, it won't error out, but just not the right way to do it. — Lalit Kumar B, Mar 13 '15 at 05:36
@LalitKumarB one last thing , as this type conversion supported on 11G as on 10.2 i am getting `ora-00904 "18075009" invalid identifier` error — Nagendra Nigade, Mar 13 '15 at 05:47
Hmm, might be. I don't have 10g to validate. There were numerous changes in 11g, So, you might be right. — Lalit Kumar B, Mar 13 '15 at 05:49

score 7 · Answer 1 · edited Jun 20 '20 at 09:12

WHERE NAME LIKE in_deviceid || '%';

Full table with a % should definitely be avoided.

You are wrong. When you use the LIKE operator as 'STRING-%', Oracle will perform an INDEX SCAN if there is any index on the column. With the statistics up to date, you shouldn't be seeing a Full Table Scan.

Let's see a test case:

SQL> CREATE TABLE emp_new AS SELECT ename FROM emp;

Table created.

SQL>
SQL> EXEC DBMS_STATS.gather_table_stats('LALIT', 'EMP_NEW');

PL/SQL procedure successfully completed.

SQL>

LIKE ename||'%' : WITHOUT Index:

SQL> EXPLAIN PLAN FOR
  2  SELECT * FROM emp_new WHERE ename LIKE ename||'%';

Explained.

SQL>
SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------
Plan hash value: 565523140

-----------------------------------------------------------------------------
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |     2 |    12 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP_NEW |     2 |    12 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------

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

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------

   1 - filter("ENAME" LIKE "ENAME"||'%')

13 rows selected.

SQL>

So, as expected Oracle does a Full Table Scan.

LIKE ename||'%' : WITH Index:

SQL> CREATE INDEX idx_nm ON emp_new(ename);

Index created.

SQL>
SQL> EXEC DBMS_STATS.gather_table_stats('LALIT', 'EMP_NEW');

PL/SQL procedure successfully completed.

SQL>
SQL> EXPLAIN PLAN FOR
  2  SELECT * FROM emp_new WHERE ename LIKE ename||'%';

Explained.

SQL>
SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
Plan hash value: 848277793

---------------------------------------------------------------------------
| Id  | Operation        | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT |        |     2 |    12 |     1   (0)| 00:00:01 |
|*  1 |  INDEX FULL SCAN | IDX_NM |     2 |    12 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------

   1 - filter("ENAME" LIKE "ENAME"||'%')

13 rows selected.

SQL>

So, you can clearly see the index is being used.

@Koyix, You're welcome. Please mark it as answered, it will help others. — Lalit Kumar B, Mar 13 '15 at 08:40

How to avoid Full Table Scan with LIKE operator

1 Answers1