This discussion is archived
1 2 3 Previous Next 33 Replies Latest reply: Jun 14, 2009 4:41 AM by 706713 RSS

Like Vs. Equal

385099 Newbie
Currently Being Moderated
Being my first post here, I am sending you a big HELLO!

I am experiencing a strange performance issue:
I have a table with >15M records.
When I search:
select * from TABLE_NAME where FIELD = '245400115';
71 ROWS SELECTED
IN 28.96 SECONDS

When I search:
select * from TABLE_NAME where FIELD LIKE '245400115%';
71 ROWS SELECTED
IN 0.01 SECONDS

FIELD is VARCHAR2(9) and is indexed
Database Version is Oracle 9iR2

What am I missing here?

Thanks in Advance!

Edited by: ethanasi on May 25, 2009 8:30 AM
  • 1. Re: Like Vs. Equal
    SeánMacGC Guru
    Currently Being Moderated
    Hello (and welcome!).

    Did you execute this:
    select * from TABLE_NAME where FIELD LIKE '245400115%';
    Immediately after you executed this from the same session:
    select * from TABLE_NAME where FIELD = '245400115';
    If so, the first results were stored in cache (memory), and Oracle was clever enough to know that it didn't have to go to the disk again. ;)

    If you reverse the sequence, you maysee a similar result.
  • 2. Re: Like Vs. Equal
    sybrand_b Guru
    Currently Being Moderated
    Ethanasi,

    When your query takes too long ... is a generic post on how to deal with performance problems.
    However, in your case it is probably interesting to swap the two statements.
    You are probably going to see exactly the opposite.
    The first statement 'warmed up' the buffer cache, and retrieved everything in cache at the expense of many physical I/O's
    When you ran the second statement everything was already there.

    -----------------
    Sybrand Bakker
    Senior Oracle DBA

    Experts: those who do read manuals
  • 3. Re: Like Vs. Equal
    385099 Newbie
    Currently Being Moderated
    No, this is not the case here.
    I am experiencing this problem with every query, not necessarily executed in the sequence you mentioned.
    I am filling a grid in my application and with EQUAL it takes forever, meanwhile with LIKE it is 0.01 Seconds fast for each row.
    I do not know what has changed, because it was OK until lately.
  • 4. Re: Like Vs. Equal
    SeánMacGC Guru
    Currently Being Moderated
    OK, this is a tuning request, so please read:

    How to post a tuning request:
    HOW TO: Post a SQL statement tuning request - template posting

    When your query takes too long:
    When your query takes too long ...

    And post the necessary -- that will help others to help you.
  • 5. Re: Like Vs. Equal
    385099 Newbie
    Currently Being Moderated
    Is it a tuning request?
    What should I tune to search based on an indexed field?
    Are you suggesting to replace all EQUAL -> LIKE?
    I don't think so.
    My request is not a tuning request.
    I am asking the forum members (actually members that know things other than "read previous threads"), how come LIKE performs better than EQUAL?
    Thanks

    Edited by: ethanasi on May 25, 2009 9:31 AM
  • 6. Re: Like Vs. Equal
    brtk Journeyer
    Currently Being Moderated
    Hi,

    One query may use index, the other no. If table and index segments are laying on different disks then the time of queries depent of those devices.

    Bartek
  • 7. Re: Like Vs. Equal
    385099 Newbie
    Currently Being Moderated
    I had the index and table on different tablespaces(same disk though).
    Just to make sure, dropped the index and recreated it in the same tablespace.
    Unfortunately, the result doesn't change.
  • 8. Re: Like Vs. Equal
    SomeoneElse Guru
    Currently Being Moderated
    What you've posted so far does us no good. We can't tell just by looking at the query.

    You need to post explain plans and traces for both sql statements to get an idea what is going on.

    The instructions for doing that are in the two threads posted above.
  • 9. Re: Like Vs. Equal
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    Pl post output (of both statements) of #2 thru #7 requested in thread HOW TO: Post a SQL statement tuning request - template posting above - without this info, we are all making uneducated guesses :-)

    HTH
    Srini
  • 10. Re: Like Vs. Equal
    385099 Newbie
    Currently Being Moderated
    OK, here we go:
    1. The SQL and a short description of its purpose
    SELECT * FROM my_table WHERE indexed_column='some_varchar2_value'

    2. The version of your database with 4-digits (e.g. 10.2.0.4)
    9.2.0.1.0

    3. Optimizer related parameters


    SQL*Plus: Release 9.2.0.1.0 - Production on Mon May 25 19:28:28 2009

    Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

    Enter user-name: sys@datech as sysdba
    Enter password:

    Connected to:
    Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
    With the Partitioning, OLAP and Oracle Data Mining options
    JServer Release 9.2.0.1.0 - Production

    SQL> show parameter optimizer

    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    optimizer_dynamic_sampling integer 1
    optimizer_features_enable string 9.2.0
    optimizer_index_caching integer 0
    optimizer_index_cost_adj integer 100
    optimizer_max_permutations integer 2000
    optimizer_mode string CHOOSE
    SQL> show parameter db_multi
    SQL> show parameter db_file_multi

    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    db_file_multiblock_read_count integer 16
    SQL> show parameter db_block_size

    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    db_block_size integer 8192
    SQL> column sname format a20
    SQL> column pname format a20
    SQL> column pva12 format a20

    SQL*Plus: Release 9.2.0.1.0 - Production on Mon May 25 19:28:28 2009

    Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

    Enter user-name: sys@datech as sysdba
    Enter password:

    Connected to:
    Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
    With the Partitioning, OLAP and Oracle Data Mining options
    JServer Release 9.2.0.1.0 - Production

    SQL> show parameter optimizer

    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    optimizer_dynamic_sampling integer 1
    optimizer_features_enable string 9.2.0
    optimizer_index_caching integer 0
    optimizer_index_cost_adj integer 100
    optimizer_max_permutations integer 2000
    optimizer_mode string CHOOSE
    SQL> show parameter db_multi
    SQL> show parameter db_file_multi

    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    db_file_multiblock_read_count integer 16
    SQL> show parameter db_block_size

    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    db_block_size integer 8192
    SQL> column sname format a20
    SQL> column pname format a20
    SQL> column pva12 format a20

    SQL> explain plan for select * from nota where nip='245400118';

    Explained.

    SQL> select * from table(dbms_xplan.display);

    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------


    --------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost |
    --------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 1 | 151 | 2 |
    | 1 | TABLE ACCESS FULL | NOTA | 1 | 151 | 2 |
    --------------------------------------------------------------------

    Note: cpu costing is off, 'PLAN_TABLE' is old version

    9 rows selected.

    SQL>

    4. The TIMING and AUTOTRACE output


    Connected to:
    Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
    With the Partitioning, OLAP and Oracle Data Mining options
    JServer Release 9.2.0.1.0 - Production

    SQL> set autotrace traceonly arraysize 100
    SP2-0613: Unable to verify PLAN_TABLE format or existence
    SP2-0611: Error enabling EXPLAIN report
    SQL>

    *****This far I can get
    5. The EXPLAIN PLAN output
    6. The TKPROF output snippet that corresponds to your statement
    7. If you're on 10g or later, the DBMS_XPLAN.DISPLAY_CURSOR output
  • 11. Re: Like Vs. Equal
    385099 Newbie
    Currently Being Moderated
    Can't someone explain which might be the reasons the query doesn't use the index?
  • 12. Re: Like Vs. Equal
    Nicolas.Gasparotto Oracle ACE
    Currently Being Moderated
    ethanasi wrote:
    Can't someone explain which might be the reasons the query doesn't use the index?
    Nope.

    Nicolas.
  • 13. Re: Like Vs. Equal
    brtk Journeyer
    Currently Being Moderated
    And how it is possible that full scan of >15M rows table has cost equal to 2?

    Bartek
  • 14. Re: Like Vs. Equal
    385099 Newbie
    Currently Being Moderated
    This is what is puzzling me.
1 2 3 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points