composite index

[sysdba]

Hi

If I have a table test, which looks like:
(col1_10...
Indx_col1 varchar2(32),
Indx_col2 varchar2(32))

My query is:
select * from test
where Indx_col1 = 'ABC' and SUBSTR(Indx_col2, 1, 3) = 'XYZ';

The index is used.

If I change the above query:
select * from test
where Indx_col1 = 'ABC' and Indx_col2 like 'XYZ%';

The index is also used.

My question is:
What is performance impact if both queries use the same index?

Thank you!

[abhaysk]

logically, 2nd query wud take much time.

Abhay.

[slimdave]

I think it is difficult to say for sure. We know by looking at the queries that they are logically equivalent, and there is no reason why Oracle shouldn't. It could also use the index for ...

Indx_col1 = 'ABC' and SUBSTR(Indx_col2, 2, 3) = 'XYZ'

... although it might use it in a different way.

I suspect that more recent versions of Oracle will produce the same query plan and will perform the same.

[ggnanaraj]

Originally posted by sysdba

My question is:
What is performance impact if both queries use the same index?

Time both the queries individually...

1. alter session set timed_statistics = true;
2. alter session set sql_trace = true;
3. run sql
4. turn off sql trace & timing
5. tkprof the trace file generated with explain.

HTH.

[abhaysk]

Originally posted by slimdave
I think it is difficult to say for sure. We know by looking at the queries that they are logically equivalent, and there is no reason why Oracle shouldn't. It could also use the index for ...

Indx_col1 = 'ABC' and SUBSTR(Indx_col2, 2, 3) = 'XYZ'

... although it might use it in a different way.

I suspect that more recent versions of Oracle will produce the same query plan and will perform the same.

2nd query uses wildcard search with LIKE operator & that will be slow.


Abhay.

[jmodic]

Originally posted by abhaysk
2nd query uses wildcard search with LIKE operator & that will be slow.

Wildcard search does not necesserily mean anything bad, particulary in the given example where whildcard is the last character in the LIKE expression.

Let's see what we have:

1) ... where Indx_col1 = 'ABC' and SUBSTR(Indx_col2, 1, 3) = 'XYZ';
2) ... where Indx_col1 = 'ABC' and Indx_col2 like 'XYZ%';

Based on the title of this thread I'm assuming there is a composite index INDX1(Indx_col1,Indx_col2). Now for both the above queries optimizer will probably choose INDEX RANGE SCAN operation on INDX1. But will the range of index entries being searched be equal in both cases? Probably not. In case 1) it'll have to go trough entire range of col1='ABC', because the SUBSTR function on col2 will suppress it to narrow the range to only col2 entries that begin with 'XYZ'. On the other hand, in case 2) it will directly narrow the index scan to only those leaf blocks that contain col1='ABC' and where col2 begins with 'XYZ'. So the execution plan might be exactly the same, but the number of I/O operations performed in case 1 might be much larger than that of case 2. Hence case 1 could not perform better than case 2, yet it could perform much worse! So nothing bad in using '%' in the LIKE operation like in the above example.

Now let's say our composite index is composed like INDX2(Indx_col2,Indx_col1). In this situation, case 1) will probably be unable to use the index at all, hence it'll need to do full table scan. On the other hand, case 2) will be able to use that index. So again, notthing bad with using '%'.

I' can see almost no situation where query 2 could perform worse than query 1 (if we do not speak about function based indexes). But I can see many situations where query 2 will significantly outperform query 1.

[abhaysk]

Originally posted by jmodic
Wildcard search does not necesserily mean anything bad, particulary in the given example where whildcard is the last character in the LIKE expression.

Based on the title of this thread I'm assuming there is a composite index INDX1(Indx_col1,Indx_col2). Now for both the above queries optimizer will probably choose INDEX RANGE SCAN operation on INDX1. 1.

Index range scan will not be used if query contains LIKE operator....rather wud go for FTS
so the second query wud take more time.

Abhay.

[DaPi]

Hi Abhay,
I think you should read a bit and experiment a bit.
Jurij knows his stuff - make sure you know yours before you pick a "fight"!

[adewri]

Originally posted by abhaysk
Index range scan will not be used if query contains LIKE operator....rather wud go for FTS
so the second query wud take more time.

Abhay.

Abhay check this

1  select  EQUIPMENT_NO,CONTAINER_IND,POO_POOL_CODE
  2  from
  3  eq_equipments
  4  where
  5  STY_EQPSIZE=20
  6  and
  7* STY_TYPE_CODE='GP'

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1547 Card=12681 Bytes=240939)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'EQ_EQUIPMENTS' (Cost=1547 Card=12681 Bytes=240939)
   2    1     INDEX (RANGE SCAN) OF 'EQ_EQUIPMENTS_NU2' (NON-UNIQUE) (Cost=64 Card=12681)


+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

  1  select  EQUIPMENT_NO,CONTAINER_IND,POO_POOL_CODE
  2  from
  3  eq_equipments
  4  where
  5  STY_EQPSIZE=20
  6  and
  7* STY_TYPE_CODE  LIKE 'GP%'
SQL> /

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1552 Card=12711 Bytes=241509)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'EQ_EQUIPMENTS' (Cost=1552 Card=12711 Bytes=241509)
   2    1     INDEX (RANGE SCAN) OF 'EQ_EQUIPMENTS_NU2' (NON-UNIQUE) (Cost=65 Card=12711)


+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

  1  select  EQUIPMENT_NO,CONTAINER_IND,POO_POOL_CODE
  2  from
  3  eq_equipments
  4  where
  5  STY_EQPSIZE=20
  6  and
  7* SUBSTR(STY_TYPE_CODE,1,2)='GP'

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1384 Card=2283 Bytes=43377)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'EQ_EQUIPMENTS' (Cost=1384 Card=2283 Bytes=43377)
   2    1     INDEX (RANGE SCAN) OF 'EQ_EQUIPMENTS_NU2' (NON-UNIQUE) (Cost=1117 Card=2283)

HTH

[jmodic]

Originally posted by abhaysk
Index range scan will not be used if query contains LIKE operator....rather wud go for FTS
so the second query wud take more time.

That is total and absolute nonsence. And it's so easy to prove the absurdness of your claims.

So let's not speculate, let's test it on a simple example. First, let's create a test table with a composite index:

Connected to:
Oracle8i Enterprise Edition Release 8.1.7.1.1 - Production
With the Partitioning option
JServer Release 8.1.7.1.1 - Production

SQL> CREATE TABLE test (indx_col1, indx_col2, col3) NOLOGGING AS
  2  SELECT owner, object_name, ROWNUM FROM DBA_OBJECTS;

Table created.

SQL> CREATE INDEX indx1 ON test (indx_col1, indx_col2) NOLOGGING;

Index created.

SQL> select count(*) from test;

  COUNT(*)
----------
     38397

SQL>

Now let's see the explain plan for both queries:

SQL> set autotrace traceonly explain
SQL> SELECT * FROM test
  2  WHERE Indx_col1 = 'ABC' AND SUBSTR(Indx_col2, 1, 3) = 'XYZ';

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEST'
   2    1     INDEX (RANGE SCAN) OF 'INDX1' (NON-UNIQUE)



SQL> 
SQL> SELECT * FROM test
  2  WHERE Indx_col1 = 'ABC' AND Indx_col2 LIKE 'XYZ%';

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEST'
   2    1     INDEX (RANGE SCAN) OF 'INDX1' (NON-UNIQUE)

So, did both queries used index range scan? YES! So, did the LIKE operator suppressed the usage of index and forced a FTS? OF COURSE NOT!

Now let's se which of the two queries (which use the same execution plan) is more efficient. We'll do this by observing the number of I/O operations each query needs to execute. let's start with the first one that uses SUBSTR function on Indx_col2:

set autotrace traceonly statistics
SQL> SELECT * FROM test
  2  WHERE Indx_col1 = 'SYS'  AND SUBSTR(Indx_col2, 1, 3) = 'DBM';

245 rows selected.


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        105  consistent gets
          0  physical reads
          0  redo size
      14767  bytes sent via SQL*Net to client
       2323  bytes received via SQL*Net from client
         19  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        245  rows processed

SQL> /

245 rows selected.


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        105  consistent gets
          0  physical reads
          0  redo size
      14767  bytes sent via SQL*Net to client
       2323  bytes received via SQL*Net from client
         19  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        245  rows processed

SQL> /

245 rows selected.


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        105  consistent gets
          0  physical reads
          0  redo size
      14767  bytes sent via SQL*Net to client
       2323  bytes received via SQL*Net from client
         19  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        245  rows processed

So it repeatedly needed 105 logical I/O operation to return those 245 rows. Now let's check with the second query which uses LIKE operator for Indx_col2:

SQL> SELECT * FROM test
  2  WHERE Indx_col1 = 'SYS'  AND Indx_col2 LIKE 'DBM%';

245 rows selected.


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         39  consistent gets
          0  physical reads
          0  redo size
      14767  bytes sent via SQL*Net to client
       2313  bytes received via SQL*Net from client
         19  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        245  rows processed

SQL> /

245 rows selected.


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         39  consistent gets
          0  physical reads
          0  redo size
      14767  bytes sent via SQL*Net to client
       2313  bytes received via SQL*Net from client
         19  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        245  rows processed

SQL> /

245 rows selected.


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         39  consistent gets
          0  physical reads
          0  redo size
      14767  bytes sent via SQL*Net to client
       2313  bytes received via SQL*Net from client
         19  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        245  rows processed

So the second query with LIKE repeatedly needed only 39 logical I/O operations. So like I said, not only that it used the same execution path, it was also about 3 times more efficient!