Home » SQL & PL/SQL » SQL & PL/SQL » Procedure with multiple search filter (Oracle 10g)
Procedure with multiple search filter [message #667514] Wed, 03 January 2018 07:34 Go to next message
Danny Freeman
Messages: 21
Registered: December 2015
Location: UK
Junior Member
An example required the sql procedure to show based on multiple search filters. Is it bad practice to have all where conditions with like parameters? Some say it adds complexities, other call it overhead. Well then how do they expect an SP to handle this? I am just curious to know about this

Report message to a moderator

Re: Procedure with multiple search filter [message #667515 is a reply to message #667514] Wed, 03 January 2018 07:39 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

>Well then how do they expect an SP to handle this?
It is a challenge to comment on code that can NOT be seen.

What problem are you trying to solve?

Report message to a moderator

Re: Procedure with multiple search filter [message #667516 is a reply to message #667515] Wed, 03 January 2018 07:53 Go to previous messageGo to next message
Danny Freeman
Messages: 21
Registered: December 2015
Location: UK
Junior Member
Well, I am not trying to solve any problem. I just want to know Is it bad practice to have all where conditions with like parameters? I just want to know how procedure handle such conditions. What will be the impact on performance?

Report message to a moderator

Re: Procedure with multiple search filter [message #667517 is a reply to message #667516] Wed, 03 January 2018 08:14 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Danny Freeman wrote on Wed, 03 January 2018 05:53
Well, I am not trying to solve any problem. I just want to know Is it bad practice to have all where conditions with like parameters? I just want to know how procedure handle such conditions. What will be the impact on performance?
What is alternative coding to avoid "all where conditions with like parameters" & still meet the requirements?
If procedure does NOT satisfy the requirements, then it is a waste of time worrying about "performance".
First make it work, then make it fast.

Report message to a moderator

Re: Procedure with multiple search filter [message #667518 is a reply to message #667514] Wed, 03 January 2018 10:15 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
Danny Freeman wrote on Wed, 03 January 2018 07:34
An example required the sql procedure to show based on multiple search filters. Is it bad practice to have all where conditions with like parameters? Some say it adds complexities, other call it overhead. Well then how do they expect an SP to handle this? I am just curious to know about this
any supposed complexities or overhead are secondary to getting the correct result set.
How would you use a LIKE operator to select all purchase orders with a PO date earlier than a given date?
How would you use a LIKE operator to select all employees who make a salary of greater than a given amount? Or all employees older than a given age?

Report message to a moderator

Re: Procedure with multiple search filter [message #667534 is a reply to message #667514] Thu, 04 January 2018 08:03 Go to previous messageGo to next message
John Watson
Messages: 8929
Registered: January 2010
Location: Global Village
Senior Member
An example of a problem:
orclx> set autot on exp
orclx> select * from emp where empno=7369;

     EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 1980-12-17        800                    20


Execution Plan
----------------------------------------------------------
Plan hash value: 2949544139

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    38 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    38 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("EMPNO"=7369)

orclx> select * from emp where empno like 7369;

     EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 1980-12-17        800                    20


Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    38 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    38 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(TO_CHAR("EMPNO")='7369')

orclx>

Report message to a moderator

Re: Procedure with multiple search filter [message #667536 is a reply to message #667534] Thu, 04 January 2018 13:50 Go to previous message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
And if the OP doesn't understand the example ..

LIKE only works with strings, but EMPNO is a number. So EMPNO LIKE 7369 forces an implied TO_CHAR so that it can be compared. This is clearly shown in the predicate information of the second EXPLAIN. And putting a function (like to_char) on the predicate eliminates the possibility of using an index. Thus the FTS on the second EXPLAIN.

Report message to a moderator

Previous Topic: Why does Oracle rename the bind variable?
Next Topic: printing first day of every month
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Apr 18 21:05:35 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.