SUBSTR Versus LIKE in Oracle 11.2

I was reading an old SQL tuning book which was printed in 2002. It said that a where clause with like could often use an index whereas a similar clause using substr could not. I wondered if this might still be the case in an Oracle 11.2.0.1 database. To find out, I created a table:

SQL> conn andrew/reid
Connected.
SQL> create table tab1 as
  2  select table_name from dba_tables
  3  /

Table created.

SQL>

... and made sure it had plenty of data:

SQL> begin
  2  for a in 1..12 loop
  3  insert into tab1 select * from tab1;
  4  end loop;
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> select count(*) from tab1
  2  /

  COUNT(*)
----------
  13348864

SQL>

I added an extra row which I could look for later:

SQL> insert into tab1 values('DAILY_FORECAST')
  2  /

1 row created.

SQL>

...added an index to help find it:

SQL> create index ind1 on tab1(table_name)
  2  /

Index created.

SQL>

...and collected statistics:

SQL> exec dbms_stats.gather_table_stats(-
> ownname=>'andrew', -
> tabname=>'tab1', -
> cascade=>true);

PL/SQL procedure successfully completed.

SQL>

I used like to find the row and it took 0.39 seconds:

SQL> alter session set sql_trace = true
  2  /

Session altered.

SQL> set timing on
SQL> select count(*) from tab1
  2  where table_name like 'DAILY%'
  3  /

  COUNT(*)
----------
         1

Elapsed: 00:00:00.39

SQL>

... but when I used substr, it took 28.79 seconds:

SQL> select count(*) from tab1
  2  where substr(table_name,1,5) = 'DAILY'
  3  /

  COUNT(*)
----------
         1

Elapsed: 00:00:28.79

SQL> set timing off
SQL> alter session set sql_trace = false
  2  /

Session altered.

SQL>

I ran the trace file through tkprof to see how Oracle had executed the SQL. The statement which used substr had done a full table scan:

********************************************************************************

select count(*) from tab1
where substr(table_name,1,5) = 'DAILY'

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      7.16      28.78      38936      38940          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      7.16      28.78      38936      38940          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 8891  (ANDREW)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=38940 pr=38936 pw=0 time=0 us)
      1   TABLE ACCESS FULL TAB1 (cr=38940 pr=38936 pw=0 time=0 us cost=9169 size=2135824 card=133489)

Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   MODE: ALL_ROWS
      1   SORT (AGGREGATE)
      1    TABLE ACCESS   MODE: ANALYZED (FULL) OF 'TAB1' (TABLE)

********************************************************************************

...but the statement which used like had used the index:

********************************************************************************

select count(*) from tab1
where table_name like 'DAILY%'

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.02       0.01          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.01          3          3          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.02       0.02          3          3          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 8891  (ANDREW)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=3 pr=3 pw=0 time=0 us)
      1   INDEX RANGE SCAN IND1 (cr=3 pr=3 pw=0 time=0 us cost=19 size=65792 card=4112)(object id 211183)

Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   MODE: ALL_ROWS
      1   SORT (AGGREGATE)
      1    INDEX   MODE: ANALYZED (RANGE SCAN) OF 'IND1' (INDEX)

********************************************************************************

A Simple Example with Indexes

I imagine there are many reasons why Oracle might (or might not) use an index. I guess there are also many reasons why you might (or might not) WANT Oracle to use an index.

I got the idea for this example from a book written by Mark Gurry and ran it on Oracle 11.2.

 

I dedicate it to Oliver, who thinks that database administrators spend all day creating indexes.

 

First I created a table:

SQL> create table t1

  2  as select * from dba_segments

  3  /

 

Table created.

 

SQL>

 

…then I made sure it contained enough data:

 

SQL> begin

  2  for a in 1..8 loop

  3  insert into t1 select * from t1;

  4  end loop;

  5  end;

  6  /

 

PL/SQL procedure successfully completed.

 

SQL>

I added an index:

SQL> create index i1 on t1(owner, extents)

  2  /

 

Index created.

 

SQL> exec dbms_stats.gather_table_stats -

> (ownname=>'ORACLE',tabname=>'T1');

 

PL/SQL procedure successfully completed.

 

SQL>

 

…then I ran a query against the table:

 

SQL> set autotrace on

SQL> set timing on

SQL> select sum(bytes) from t1

  2  where owner = 'SYS'

  3  and extents = 1

  4  /

 

SUM(BYTES)

----------

3.0098E+10

 

Elapsed: 00:00:27.29

 

Execution Plan

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

Plan hash value: 3693069535

 

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

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT   |      |     1 |    16 |  2438  (29)| 00:00:03 |

|   1 |  SORT AGGREGATE    |      |     1 |    16 |            |          |

|*  2 |   TABLE ACCESS FULL| T1   | 10388 |   162K|  2438  (29)| 00:00:03 |

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

 

Predicate Information (identified by operation id):

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

 

   2 - filter("EXTENTS"=1 AND "OWNER"='SYS')

 

 

Statistics

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

        365  recursive calls

          0  db block gets

      37109  consistent gets

      37046  physical reads

          0  redo size

        533  bytes sent via SQL*Net to client

        524  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          6  sorts (memory)

          0  sorts (disk)

          1  rows processed

 

SQL> set autotrace off

SQL> set timing off

SQL>

 

The query had to look at 23% of the rows in the table:

 

SQL> l

  1  select round

  2  ((select count(*) from t1

  3    where owner = 'SYS' and extents = 1)

  4  /

  5  (select count(*) from t1) * 100)

  6* as percentage from dual

SQL> /

 

PERCENTAGE

----------

        23

 

SQL>

 

…so it did a full table scan instead of using the index and the elapsed time was 27 seconds. I wanted to improve on this so I added the bytes column to the index:

 

SQL> drop index i1

  2  /

 

Index dropped.

 

SQL> create index i2 on t1(owner, extents, bytes)

  2  /

 

Index created.

 

SQL> exec dbms_stats.gather_table_stats -

> (ownname=>'ORACLE',tabname=>'T1');

 

PL/SQL procedure successfully completed.

 

SQL>

 

I ran the query again. This time, Oracle could get all the information it needed from the index so the elapsed time went down to 3 seconds:

 

SQL> set autotrace on

SQL> set timing on

SQL> select sum(bytes) from t1

  2  where owner = 'SYS'

  3  and extents = 1

  4  /

 

SUM(BYTES)

----------

3.0098E+10

 

Elapsed: 00:00:03.34

 

Execution Plan

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

Plan hash value: 494139663

 

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

| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT      |      |     1 |    16 |   581  (47)| 00:00:01 |

|   1 |  SORT AGGREGATE       |      |     1 |    16 |            |          |

|*  2 |   INDEX FAST FULL SCAN| I2   |   518K|  8099K|   581  (47)| 00:00:01 |

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

 

Predicate Information (identified by operation id):

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

 

   2 - filter("OWNER"='SYS' AND "EXTENTS"=1)

 

 

Statistics

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

        432  recursive calls

          0  db block gets

       6843  consistent gets

        569  physical reads

          0  redo size

        533  bytes sent via SQL*Net to client

        524  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

         13  sorts (memory)

          0  sorts (disk)

          1  rows processed

 

SQL> set autotrace off

SQL> set timing off

SQL>

Unusable Indexes

I tested this post on an Oracle 9 database. It shows that if you have an index which is marked as UNUSABLE, truncating the underlying table makes it VALID again.

First I created a table:

SQL> CREATE TABLE ANDREWS_TABLE AS
  2  SELECT * FROM DBA_TABLES
  3  /

Table created.

SQL>

Then I added an index to the table:

SQL> CREATE INDEX ANDREWS_INDEX
  2  ON ANDREWS_TABLE(TABLE_NAME)
  3  /

Index created.

SQL>

... and made it UNUSABLE:

SQL> ALTER INDEX ANDREWS_INDEX UNUSABLE
  2  /

Index altered.

SQL> SELECT STATUS
  2  FROM USER_INDEXES
  3  WHERE INDEX_NAME = 'ANDREWS_INDEX'
  4  /

STATUS
--------
UNUSABLE

SQL>

I truncated the table:

SQL> TRUNCATE TABLE ANDREWS_TABLE
  2  /

Table truncated.

SQL>

... and this made the index valid again:

SQL> SELECT STATUS
  2  FROM USER_INDEXES
  3  WHERE INDEX_NAME = 'ANDREWS_INDEX'
  4  /

STATUS
--------
VALID

SQL>

ORA-01502

This example was tested on Oracle 11.2.0.2.7. It looks at why an index might become unusable. First I created a table in the SYSTEM tablespace by mistake:

SQL> create table andrews_table

  2  tablespace system

  3  as select * from dba_tables

  4  /

 

Table created.

 

SQL>

I created an index on the table and checked that it was VALID:

SQL> create index andrews_index

  2  on andrews_table(table_name)

  3  /

 

Index created.

 

SQL> select status from user_indexes

  2  where index_name = 'ANDREWS_INDEX'

  3  /

 

STATUS

--------

VALID

 

SQL>

Then I made sure that Oracle would tell me if it tried to use an UNUSABLE index:

SQL> alter session

  2  set skip_unusable_indexes = false

  3  /

 

Session altered.

 

SQL>

I ran some SQL which would use the index and it worked OK:

SQL> select count(*) from andrews_table

  2  where table_name = 'BLAH'

  3  /

 

  COUNT(*)

----------

         0

 

SQL>

Then I moved the table to the correct tablespace:

SQL> alter table andrews_table

  2  move tablespace users

  3  /

 

Table altered.

 

SQL>

This invalidated the index:

SQL> select status from user_indexes

  2  where index_name = 'ANDREWS_INDEX'

  3  /

 

STATUS

--------

UNUSABLE

 

SQL>

So when I tried to use it I got an ORA-01502:

SQL> select count(*) from andrews_table

  2  where table_name = 'BLAH'

  3  /

select count(*) from andrews_table

*

ERROR at line 1:

ORA-01502: index 'OPS$ORACLE.ANDREWS_INDEX' or

partition of such index is in unusable state

 

SQL>

To fix this, I rebuilt the index:

SQL> alter index andrews_index rebuild

  2  /

 

Index altered.

 

SQL>

This made the index VALID:

SQL> select status from user_indexes

  2  where index_name = 'ANDREWS_INDEX'

  3  /

 

STATUS

--------

VALID

 

SQL>

... and I was able to use it again:

SQL> select count(*) from andrews_table

  2  where table_name = 'BLAH'

  3  /

 

  COUNT(*)

----------

         0

 

SQL>

NOSORT and ORA-01409

When you create an index, Oracle usually does a sort. I read about the NOSORT option recently. This allows Oracle to create an index without doing a sort. I decided to give it a try on an Oracle 9.2.0.7.0 database. First I created a table, counted the number of sorts my session had done, created an index on the table, counted the number of sorts again and saw that it had increased by 1:

 

SQL> create table andrews_table

  2  as select owner, table_name

  3  from dba_tables

  4  /

 

Table created.

 

SQL> select a.name, b.value

  2  from v$sysstat a, v$mystat b

  3  where a.statistic# = b.statistic#

  4  and a.name like '%sorts%'

  5  /

 

NAME                      VALUE

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

sorts (memory)               17

sorts (disk)                  0

sorts (rows)              11527

 

SQL> create index andrews_index

  2  on andrews_table(table_name)

  3  /

 

Index created.

 

SQL> select a.name, b.value

  2  from v$sysstat a, v$mystat b

  3  where a.statistic# = b.statistic#

  4  and a.name like '%sorts%'

  5  /

 

NAME                      VALUE

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

sorts (memory)               18

sorts (disk)                  0

sorts (rows)              13069

 

SQL>

 

Then I dropped the index and tried to recreate it with the NOSORT option. I expected this to fail as the table was not ordered on the indexed column:

 

SQL> drop index andrews_index

  2  /

 

Index dropped.

 

SQL> create index andrews_index

  2  on andrews_table(table_name)

  3  nosort

  4  /

on andrews_table(table_name)

   *

ERROR at line 2:

ORA-01409: NOSORT option may not be used; rows are not

in ascending order

 

SQL>

 

Finally, I dropped and recreated the table in table_name order, counted the number of sorts my session had done, created an index with the NOSORT option on the sorted table_name column, counted the number of sorts again and saw that it had not increased:

 

SQL> drop table andrews_table

  2  /

 

Table dropped.

 

SQL> create table andrews_table

  2  as select owner, table_name

  3  from dba_tables

  4  order by table_name

  5  /

 

Table created.

 

SQL> select a.name, b.value

  2  from v$sysstat a, v$mystat b

  3  where a.statistic# = b.statistic#

  4  and a.name like '%sorts%'

  5  /

 

NAME                      VALUE

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

sorts (memory)               35

sorts (disk)                  0

sorts (rows)              26085

 

SQL> create index andrews_index

  2  on andrews_table(table_name)

  3  nosort

  4  /

 

Index created.

 

SQL> select a.name, b.value

  2  from v$sysstat a, v$mystat b

  3  where a.statistic# = b.statistic#

  4  and a.name like '%sorts%'

  5  /

 

NAME                      VALUE

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

sorts (memory)               35

sorts (disk)                  0

sorts (rows)              26085

 

SQL>

ORA-01702

This was tested on an Oracle 11 database. A view is like a stored select statement. It contains no data and its contents need to be recalculated each time you query it. You therefore cannot add an index to a view:

 

SQL> create table table_list

  2  as select * from dba_tables

  3  /

 

Table created.

 

SQL> create view view1 as

  2  select owner, count(*) number_found

  3  from table_list

  4  group by owner

  5  /

 

View created.

 

SQL> create index view_index

  2  on view1(owner)

  3  /

on view1(owner)

   *

ERROR at line 2:

ORA-01702: a view is not appropriate here

 

SQL>

Allocate Extent

This post, which was tested on a version 9 database, shows how you can allocate extents to a table or index manually as opposed to allowing Oracle to create them automatically. Start by creating a table and looking at the size of its first extent:

 
SQL> create table andrews_table
  2  (col1 varchar2(10))
  3  tablespace user_data
  4  /

Table created.

SQL> select extent_id, bytes from dba_extents
  2  where segment_name = 'ANDREWS_TABLE'
  3  /

 EXTENT_ID      BYTES
---------- ----------
         0      20480

SQL> 

Now do the same with an index:

SQL> create index andrews_index
  2  on andrews_table (col1)
  3  tablespace user_data
  4  /

Index created.

SQL> select extent_id, bytes from dba_extents
  2  where segment_name = 'ANDREWS_INDEX'
  3  /

EXTENT_ID      BYTES
---------- ----------
        0      20480

SQL>

When you add an extent manually, you can allow Oracle to decide its size: 

SQL> alter table andrews_table allocate extent
  2  /

Table altered.

SQL> select extent_id, bytes from dba_extents
  2  where segment_name = 'ANDREWS_TABLE'
  3  order by 1
  4  /

 EXTENT_ID      BYTES
---------- ----------
         0      20480
         1      20480

SQL>

... or you can specify it yourself like this: 

SQL> alter index andrews_index allocate extent
  2  (size 40k)
  3  /

Index altered.

SQL> select extent_id, bytes from dba_extents
  2  where segment_name = 'ANDREWS_INDEX'
  3  order by 1
  4  /

 EXTENT_ID      BYTES
---------- ----------
         0      20480
         1      40960

SQL>

You can even tell Oracle which datafile to put the extent in. However, in this example it makes no difference as the tablespace only has 1 datafile:

SQL> alter table andrews_table allocate extent
  2  (size 60k
  3  datafile '/datafiles/user_data.dbf')
  4  /

Table altered.

SQL> select extent_id, file_id, bytes from dba_extents
  2  where segment_name = 'ANDREWS_TABLE'
  3  order by 1
  4  /

 EXTENT_ID    FILE_ID      BYTES
---------- ---------- ----------
         0         22      20480
         1         22      20480
         2         22      61440

SQL>

But, if you specify a datafile which does not exist or, as in this case, belongs to another tablespace, you get an ORA-03283:

SQL> alter index andrews_index allocate extent
  2  (datafile '/datafiles/system.dbf')
  3  /
alter index andrews_index allocate extent
*
ERROR at line 1:
ORA-03283: specified datafile /datafiles/system.dbf does not exist

SQL>