Performance in a query

[maxgiusti]

Hi,
i have a query where i must to control a column (colA) that can assume two values:

'Dlsw Peer state : 6 ( 6 : disconnected - 3 : connected )'
'Dlsw Peer state : 3 ( 6 : disconnected - 3 : connected )'

The difference between this values is only the 20th character (6 or 3). My question is:
In this case (the table has approximately 10000 record) for performance is better

colA = 'Dlsw Peer state : 6 ( 6 : disconnected - 3 : connected )';

or

substr(colA, 20, 1) = '6';

or

colA like '%: 6 %';

or i don't know what?

Thank's in advance,
MaX

[sai_sathish]

Do you have an index for the ColA?

[Sameer]

As the cardinality is low, I guess a functional bitmap index should help...

SQL>create bitmap index ixfunc on temp (substr(columns,20,1));
Index Creaded.

SQL>select /*+ INDEX(temp ixfunc) */ * from temp where substr(columns,20,1) = '3';

82355 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=172 Card=1057 Bytes=
          75047)

   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'temp' (Cost=172 Card=
          1057 Bytes=75047)

   2    1     BITMAP CONVERSION (TO ROWIDS)
   3    2       BITMAP INDEX (SINGLE VALUE) OF 'IXFUNC'

Statistics
----------------------------------------------------------
          6  recursive calls
          4  db block gets
     148100  consistent gets
          0  physical reads
        592  redo size
    3898031  bytes sent via SQL*Net to client
     609815  bytes received via SQL*Net from client
      10984  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      82355  rows processed

Without Index..

SQL> select * from temp where columns = 'Dlsw Peer state : 3 ( 6 : disconnected - 3 : connected )';

82355 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'temp'

Statistics
----------------------------------------------------------
          6  recursive calls
         10  db block gets
       6746  consistent gets
          0  physical reads
        592  redo size
    3934019  bytes sent via SQL*Net to client
     609815  bytes received via SQL*Net from client
      10984  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      82355  rows processed

Sameer

[maxgiusti]

Thank you very much!!!