Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Use like clause more then once

Re: Use like clause more then once

From: stephen O'D <stephen.odonnell_at_gmail.com>
Date: 29 May 2007 13:51:48 -0700
Message-ID: <1180471908.654519.235600@q75g2000hsh.googlegroups.com>


On May 29, 7:17 pm, Jeremy Smith <godto..._at_hotmail.com> wrote:
> Currently 9i, will be moving to 10g this year. I tried joining the
> tables and doing to like statements, but it would give back zero
> records.
>
> SELECT distinct srv.SERVER_ID,srv.SERVER_NAME,srv
> FROM serverdb.servers srv inner join services svc on srv.SERVER_ID =
> svc.SERVER_ID
> WHERE (svc_display_name LIKE '%Microsoft Exchange%' and
> svc_display_name LIKE '%TSM%') and
> status_id = 1
>
> If I did only one like at a time it should have around 200 records
> returned. So I assume that oracle doesn't like 2 or more like
> staments in the same where statement.

OK, I am not sure what you are trying to achive here, but I will give it a shot. In the future, you will get more help if you post example table create scripts and inserts to save peoples time and help their understanding :o)

SQL> create table servers (id integer, name varchar2(50));

Table created.

SQL> create table services (id integer, display_name varchar2(50));

Table created.

SQL> insert into servers values (1, 'server1');

1 row created.

SQL> insert into servers values (2,'server2');

1 row created.

SQL> insert into servers values (3,'server4'); ** made a typo here, oops.

1 row created.

SQL> insert into servers values (4, 'server5');

1 row created.

SQL> insert into services values (1, 'MICROSOFT EXCHANGE');

1 row created.

SQL> insert into services values (2, 'TSM');

1 row created.

SQL> insert into services values (3, 'MICROSOFT EXCHANGE');

1 row created.

SQL> insert into services values (4, 'SOMETHING ELSE');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from servers;

        ID NAME

---------- --------------------------------------------------
         1 server1
         2 server2
         3 server4
         4 server5

SQL> select * from services;

        ID DISPLAY_NAME

---------- --------------------------------------------------
         1 MICROSOFT EXCHANGE
         2 TSM
         3 MICROSOFT EXCHANGE
         4 SOMETHING ELSE

I think this is what you had. Your first query was pretty much like this:

SQL> select id, name
  2 from servers
  3 where id IN (select id

  4               from services
  5               where display_name like '%MICROSOFT EXCHANGE')
  6  and id in (select id
  7             from services
  8             where display_name like '%TSM%')
  9*
SQL> / no rows selected

I don't know how you got any rows with that query either, as its basically the same as this:

SQL> select s.id, s.name
  2 from servers s, services sr
  3 where s.id = sr.id
  4 and sr.display_name like '%MICROSOFT EXCHANGE'   5* and sr.display_name like '%TSM%'
SQL> / no rows selected

I guess this is because you want the rows where display name is like MICRO... or TSM - not AND:

SQL> select s.id, s.name
  2 from servers s, services sr
  3 where s.id = sr.id
  4 and (sr.display_name like '%MICROSOFT EXCHANGE'   5* or sr.display_name like '%TSM%') SQL> /         ID NAME

---------- --------------------------------------------------
         1 server1
         2 server2
         3 server4

Am I correct?

Oracle only needs one pass over the services table in this case (unlike in the case where the query was written the original way, when it need two).

SQL> explain plan for
  2 select s.id, s.name
from servers s, services sr
where s.id = sr.id
and (sr.display_name like '%MICROSOFT EXCHANGE'

     or sr.display_name like '%TSM%') 3 4 5 6 ;

Explained.

SQL> SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY); 2 PLAN_TABLE_OUTPUT



Plan hash value: 1600243009
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)|
Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     3 |   240 |     5  (20)|
00:00:01 |
|*  1 |  HASH JOIN         |          |     3 |   240 |     5  (20)|
00:00:01 |
|*  2 |   TABLE ACCESS FULL| SERVICES |     3 |   120 |     2   (0)|
00:00:01 |
|   3 |   TABLE ACCESS FULL| SERVERS  |     4 |   160 |     2   (0)|
00:00:01 |

PLAN_TABLE_OUTPUT



Predicate Information (identified by operation id):
   1 - access("S"."ID"="SR"."ID")
   2 - filter("SR"."DISPLAY_NAME" LIKE '%MICROSOFT EXCHANGE' OR
              "SR"."DISPLAY_NAME" LIKE '%TSM%')

If services contains any significant amount of rows, you will probably need some sort of function based index on display_name, or fix the data so that the column data starts with the value you are looking for (some sample data would be helpful here).

Let me know if this is what you are looking for! Received on Tue May 29 2007 - 15:51:48 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US