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)

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

Making a Hot Backup and Doing an Incomplete Recovery

This post shows how to do a hot backup followed by an incomplete recovery. I ran it on an Oracle 11.2.0.4 test database. First I checked that the database was in ARCHIVELOG mode:

SQL> select log_mode from v$database;

LOG_MODE
------------
ARCHIVELOG

SQL>

Then I decided where to copy the hot backup.

The directory listing below shows a sub-directory called backup. This contains a cold backup I made earlier in case the test goes horribly wrong and I need to start again.

After this, you can see the database’s 3 control files. In a real life situation, these would be on separate physical disks but this is only a test so having them in the same place is OK.

Next comes a directory called hot_backup. The hot backup will go in here.

Finally you can see the database’s redo logs and datafiles:

GBASRDB1 /database/ANDREW/DB1 > ls -l
total 6585680
drwxr-xr-x   2 oracle   dba         4096 Jun  3 17:51 backup
-rw-r-----   1 oracle   dba      11517952 Aug  9 16:59 control01.ctl
-rw-r-----   1 oracle   dba      11517952 Aug  9 16:59 control02.ctl
-rw-r-----   1 oracle   dba      11517952 Aug  9 16:59 control03.ctl
drwxr-xr-x   2 oracle   dba         4096 Jun  7 18:40 hot_backup
-rw-r-----   1 oracle   dba      52429312 Aug  9 16:50 redo01a.log
-rw-r-----   1 oracle   dba      52429312 Aug  9 16:50 redo02a.log
-rw-r-----   1 oracle   dba      52429312 Aug  9 16:59 redo03a.log
-rw-r-----   1 oracle   dba      796925952 Aug  9 16:55 sysaux01.dbf
-rw-r-----   1 oracle   dba      796925952 Aug  9 16:58 system01.dbf
-rw-r-----   1 oracle   dba      524296192 Aug  9 16:50 temp01.dbf
-rw-r-----   1 oracle   dba      524296192 Aug  9 16:55 undotbs01.dbf
-rw-r-----   1 oracle   dba      524296192 Aug  9 16:50 users01.dbf
GBASRDB1 /database/ANDREW/DB1 >

I identified the files to backup:

SQL> l
  1  select tablespace_name, file_name
  2  from dba_data_files
  3* order by 1,2
SQL> /

TABLESPACE_NAME FILE_NAME
--------------- -----------------------------------
SYSAUX          /database/ANDREW/DB1/sysaux01.dbf
SYSTEM          /database/ANDREW/DB1/system01.dbf
UNDOTBS1        /database/ANDREW/DB1/undotbs01.dbf
USERS           /database/ANDREW/DB1/users01.dbf

SQL>

... and checked the sequence number of the current redo log:

SQL> l
  1  select group#, status, sequence#
  2  from v$log
  3* order by 1
SQL> /

    GROUP# STATUS            SEQUENCE#
---------- ---------------- ----------
         1 INACTIVE               1423
         2 CURRENT                1424
         3 INACTIVE               1422

SQL>

I put the database into hot backup mode:

SQL> alter database begin backup
  2  /

Database altered.

SQL>

I copied the datafiles into the hot_backup directory:

GBASRDB1 /database/ANDREW/DB1 > cp sysaux01.dbf hot_backup
GBASRDB1 /database/ANDREW/DB1 > cp system01.dbf hot_backup
GBASRDB1 /database/ANDREW/DB1 > cp undotbs01.dbf hot_backup
GBASRDB1 /database/ANDREW/DB1 > cp users01.dbf hot_backup
GBASRDB1 /database/ANDREW/DB1 >

I created a marker table:

SQL> create table system.andrew_was_here(col1 number)
  2  /

Table created.

SQL> insert into system.andrew_was_here values(1234567)
  2  /

1 row created.

SQL> commit
  2  /

Commit complete.

SQL>

I took the database out of hot backup mode:

SQL> alter database end backup
  2  /

Database altered.

SQL> 

I archived any outstanding redo and forced a log switch:

SQL> alter system archive log current
  2  /

System altered.

SQL>

I checked the sequence number of the current redo log:

SQL> l
  1  select group#, status, sequence#
  2  from v$log
  3* order by 1
SQL> /

    GROUP# STATUS            SEQUENCE#
---------- ---------------- ----------
         1 INACTIVE               1423
         2 ACTIVE                 1424
         3 CURRENT                1425

SQL>

All the redo needed for recovery should be in log no 1424 so I copied this into the hot_backup directory: 

GBASRDB1 /database/ANDREW/DB1 > cp 1_1424_913830195.dbf hot_backup
GBASRDB1 /database/ANDREW/DB1 > 

Finally, I took a backup of the controlfile:

SQL> alter database backup controlfile
  2  to '/database/ANDREW/DB1/hot_backup/bkup.ctl'
  3  /

Database altered.

SQL>

As a first example, I decided to restore to this hot backup so I deleted the database’s datafiles, control files and online redo logs. Obviously, this loses any changes made after the backup:

GBASRDB1 /database/ANDREW/DB1 > rm *
rm: backup is a directory
rm: hot_backup is a directory
GBASRDB1 /database/ANDREW/DB1 > ls
backup      hot_backup
GBASRDB1 /database/ANDREW/DB1 > 

Then I closed the database:

GBASRDB1 /database/ANDREW/DB1 > sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Aug 10 17:39:59 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shutdown abort
ORACLE instance shut down.
SQL>

I restored the files from the hot_backup directory and made sure there were 3 control files again:

GBASRDB1 /database/ANDREW/DB1 > cp hot_backup/* .
GBASRDB1 /database/ANDREW/DB1 > ls
1_1424_913830195.dbf  bkup.ctl              sysaux01.dbf          undotbs01.dbf
backup                hot_backup            system01.dbf          users01.dbf
GBASRDB1 /database/ANDREW/DB1 > mv bkup.ctl control01.ctl
GBASRDB1 /database/ANDREW/DB1 > cp control01.ctl control02.ctl
GBASRDB1 /database/ANDREW/DB1 > cp control01.ctl control03.ctl
GBASRDB1 /database/ANDREW/DB1 >

I mounted the database:

GBASRDB1 /database/ANDREW/DB1 > sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Aug 10 18:02:36 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area  521936896 bytes
Fixed Size                  2252448 bytes
Variable Size             322961760 bytes
Database Buffers          188743680 bytes
Redo Buffers                7979008 bytes
Database mounted.
SQL>

I recovered the database using the backup controlfile, applying all the archived redo at my disposal:

SQL> recover database using backup controlfile until cancel
ORA-00279: change 4101278 generated at 08/09/2016 17:47:29 needed for thread 1
ORA-00289: suggestion : /database/ANDREW/DB1/1_1424_913830195.dbf
ORA-00280: change 4101278 for thread 1 is in sequence #1424

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00279: change 4102126 generated at 08/09/2016 18:06:23 needed for thread 1
ORA-00289: suggestion : /database/ANDREW/DB1/1_1425_913830195.dbf
ORA-00280: change 4102126 for thread 1 is in sequence #1425
ORA-00278: log file '/database/ANDREW/DB1/1_1424_913830195.dbf' no longer
needed for this recovery

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL>

I opened the database:

SQL> alter database open resetlogs
  2  /

Database altered.

SQL> 

... and checked that my table was still there: 

SQL> select * from system.andrew_was_here
  2  /

      COL1
----------
   1234567

SQL> 

In the process, Oracle recreated the online redo log files for me:

SQL> select group#, sequence#, status
  2  from v$log
  3  /

    GROUP#  SEQUENCE# STATUS
---------- ---------- ----------------
         1          1 CURRENT
         2          0 UNUSED
         3          0 UNUSED

SQL>