CODEWITHSUNDEEP
sqloracle-databaseindexingsql-likefull-table-scan
Koyix
Koyix

Reputation: 181

How to avoid Full Table Scan with LIKE operator

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;

Upvotes: 4

Views: 7087

Answers (1)

Lalit Kumar B
Lalit Kumar B

Reputation: 49112

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.

Upvotes: 7

Related Questions