Skip to Main Content
  • Questions

  • select data with a where clause on non-indexed column and indexed column

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, ali.

Asked: May 16, 2016 - 4:17 am UTC

Last updated: May 20, 2016 - 3:17 am UTC

Version: oracle 10g

Viewed 1000+ times

You Asked

Hi TOM
I really appreciate you for your answers and your help.

I have question

I have a table with more than 10 million of records

like this structure

tblTest(id int,EmpNumber int,nName VARCHAR2(100 BYTE) nullable)

in this table Id is key so is not nullable and EmpNumber is indexed and also is not nullable

nName is nullable

------------------------------------------------------
i have this query

SELECT * FROM tblTest WHERE EmpNumber LIKE '12%' AND nName IS NOT NULL


Q1- does it need to make index over nName column???? ( in tblTest i have around 10k records that nName is not null and in 9,990,000 record nName is null)

------------------------------------------------------

Q2- if yes how I can know if i make index how much my performance will increase ???????
(before make index i want to know how much may my performance will increase)
in general I using SQL Developer tool

------------------------------------------------------
Q3- can SQL TUNING ADVISOR in SQL DEVELOPER tell me that I need to index or no???
and if yes can we believe this tool all times????
------------------------------------------------------
thanks for your help



and Connor said...

Well, here is the first problem: emp like '12%'... That's a number being compared to a string


SQL> drop table tblTest purge;

Table dropped.

SQL>
SQL> create table tblTest(id int,EmpNumber int,nName VARCHAR2(100 BYTE));

Table created.

SQL>
SQL> insert /*+ APPEND */ into tblTest
  2  select rownum, rownum, case when mod(rownum,100) = 0 then rownum end
  3  from
  4    ( select 1 from dual connect by level <= 10000 ),
  5    ( select 1 from dual connect by level <= 1000 )
  6  /

10000000 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> create index tblTest_ix1 on tblTest ( EmpNumber ) ;

Index created.

SQL>
SQL> create index tblTest_ix2 on tblTest ( EmpNumber, nName ) invisible;

Index created.

SQL> create index tblTest_ix3 on tblTest ( nName ) invisible;

Index created.

SQL>
SQL>
SQL>
SQL>
SQL>
SQL> set autotrace traceonly explain
SQL> SELECT * FROM tblTest WHERE EmpNumber LIKE '12%' ;

Execution Plan
----------------------------------------------------------
Plan hash value: 2832004635

-----------------------------------------------------------------------------
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |   500K|  6347K|  6471   (2)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TBLTEST |   500K|  6347K|  6471   (2)| 00:00:01 |
-----------------------------------------------------------------------------

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

   1 - filter(TO_CHAR("EMPNUMBER") LIKE '12%')

SQL>


See the TO_CHAR ... even your standard index on empnumber will not be used.

So before we proceed any further, can you let us know -

1) is empnumber *really* a number ? if so, we want to look at something similar to:

empnumber between 1200000 and 1299999

OR

2) is empnumber a string ?

Let us know via review, and we'll pick it up from there

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Hi Connor McDonald

ali salamat, May 19, 2016 - 4:12 am UTC

Hi Connor McDonald

thanks for your answer

empnumber is not number and it's string


Connor McDonald
May 20, 2016 - 3:17 am UTC

Ah....so when you say

"like this structure:
tblTest(id int,EmpNumber int,nName VARCHAR2(100 BYTE) nullable)"

you are using "like" in the "approximate" sense :-)

So we can look at some indexing options and see what works best:

SQL> create table tblTest(id int,EmpNumber varchar2(20),nName VARCHAR2(100 BYTE));

Table created.

SQL>
SQL> insert /*+ APPEND */ into tblTest
  2  select rownum, 10000000+rownum, case when mod(rownum,100) = 0 then rownum end
  3  from
  4    ( select 1 from dual connect by level <= 10000 ),
  5    ( select 1 from dual connect by level <= 1000 )
  6  /

10000000 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> create index tblTest_ix1 on tblTest ( EmpNumber ) ;

Index created.

SQL> create index tblTest_ix2 on tblTest ( EmpNumber, nName );

Index created.

SQL> create index tblTest_ix3 on tblTest ( nName );

Index created.

SQL>
SQL> set autotrace on
SQL>
SQL> SELECT /*+ index(t tblTest_ix1) */ count(*) FROM tblTest t WHERE EmpNumber LIKE '12%' AND nName IS NOT NULL;

  COUNT(*)
----------
     10000

1 row selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2224612546

----------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |             |     1 |    11 |   219   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE                      |             |     1 |    11 |            |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID BATCHED| TBLTEST     |   391 |  4301 |   219   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                  | TBLTEST_IX1 | 39064 |       |   111   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

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

   2 - filter("NNAME" IS NOT NULL)
   3 - access("EMPNUMBER" LIKE '12%')
       filter("EMPNUMBER" LIKE '12%')


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       5567  consistent gets
       5566  physical reads
          0  redo size
        542  bytes sent via SQL*Net to client
        552  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> SELECT /*+ index(t tblTest_ix2) */ count(*) FROM tblTest t WHERE EmpNumber LIKE '12%' AND nName IS NOT NULL;

  COUNT(*)
----------
     10000

1 row selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2675792195

---------------------------------------------------------------------------------
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             |     1 |    11 |   117   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |             |     1 |    11 |            |          |
|*  2 |   INDEX RANGE SCAN| TBLTEST_IX2 |   391 |  4301 |   117   (0)| 00:00:01 |
---------------------------------------------------------------------------------

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

   2 - access("EMPNUMBER" LIKE '12%')
       filter("NNAME" IS NOT NULL AND "EMPNUMBER" LIKE '12%')


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       2940  consistent gets
       2939  physical reads
          0  redo size
        542  bytes sent via SQL*Net to client
        552  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> SELECT /*+ index(t tblTest_ix3) */ count(*) FROM tblTest t WHERE EmpNumber LIKE '12%' AND nName IS NOT NULL;

  COUNT(*)
----------
     10000

1 row selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3026274853

----------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |             |     1 |    11 | 42275   (1)| 00:00:02 |
|   1 |  SORT AGGREGATE                      |             |     1 |    11 |            |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID BATCHED| TBLTEST     |   391 |  4301 | 42275   (1)| 00:00:02 |
|*  3 |    INDEX FULL SCAN                   | TBLTEST_IX3 |   100K|       |   266   (1)| 00:00:01 |
----------------------------------------------------------------------------------------------------

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

   2 - filter("EMPNUMBER" LIKE '12%')
   3 - filter("NNAME" IS NOT NULL)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      45183  consistent gets
      25123  physical reads
          0  redo size
        542  bytes sent via SQL*Net to client
        552  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> SELECT /*+ full(t) */ count(*) FROM tblTest t WHERE EmpNumber LIKE '12%' AND nName IS NOT NULL;

  COUNT(*)
----------
     10000

1 row selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1533672297

------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |     1 |    11 |  7605   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE    |         |     1 |    11 |            |          |
|*  2 |   TABLE ACCESS FULL| TBLTEST |   391 |  4301 |  7605   (1)| 00:00:01 |
------------------------------------------------------------------------------

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

   2 - filter("NNAME" IS NOT NULL AND "EMPNUMBER" LIKE '12%')


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      27650  consistent gets
          0  physical reads
          0  redo size
        542  bytes sent via SQL*Net to client
        552  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


So they are all quite expensive. If this is an important query, we could create a customised index just for this need


SQL>
SQL>
SQL> set autotrace off
SQL>
SQL> create index tblTest_ix4 on tblTest ( case when nname is not null then empnumber end  );

Index created.

SQL>
SQL> set autotrace on
SQL>
SQL> SELECT /*+ index(t tblTest_ix4) */ count(*) FROM tblTest t WHERE CASE  WHEN "NNAME" IS NOT NULL THEN "EMPNUMBER" END LIKE '12%' ;

  COUNT(*)
----------
     10000

1 row selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1107403368

---------------------------------------------------------------------------------
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             |     1 |    12 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |             |     1 |    12 |            |          |
|*  2 |   INDEX RANGE SCAN| TBLTEST_IX4 | 11002 |   128K|     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------

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

   2 - access(CASE  WHEN "NNAME" IS NOT NULL THEN "EMPNUMBER" END  LIKE
              '12%')
       filter(CASE  WHEN "NNAME" IS NOT NULL THEN "EMPNUMBER" END  LIKE
              '12%')

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - 1 Sql Plan Directive used for this statement


Statistics
----------------------------------------------------------
         94  recursive calls
          0  db block gets
        145  consistent gets
         29  physical reads
          0  redo size
        542  bytes sent via SQL*Net to client
        552  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          7  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>
SQL>
SQL>



ali salamat, May 20, 2016 - 4:37 am UTC

Hi Connor McDonald
thanks for your help and it's big help for me.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Hints

Need more information on hints? Check out the complete hint descriptions