OraFAQ Forum: SQL & PL/SQL » How to avoid OR condition in the below scenario, how we can write in different ways

Home » SQL & PL/SQL » SQL & PL/SQL » How to avoid OR condition in the below scenario, how we can write in different ways (Oracle 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to avoid OR condition in the below scenario, how we can write in different ways [message #625218]

Fri, 03 October 2014 02:47

na.dharma@gmail.com
Messages: 82
Registered: May 2008
Location: bangalore

Member

begin
create table prod(profile_type varchar2(15)) tablespace warehouse_big_Data;

insert into prod values(null);
insert into prod values('Prod');
insert into prod values('Prodparallel');
insert into prod values(null);
insert into prod values('Prod');
insert into prod values(null);
insert into prod values('Prodparallel');
insert into prod values('Prod');
end;

select * from prod where profile_type='Prod' or profile_type is null

Report message to a moderator

Re: How to avoid OR condition in the below scenario, how we can write in different ways [message #625219 is a reply to message #625218]

Fri, 03 October 2014 02:51

Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

What's wrong with OR?

Anyway: as you currently put it, this returns the same result:

select * from prod where nvl(profile_type, 'Prod') = 'Prod';

Report message to a moderator

Re: How to avoid OR condition in the below scenario, how we can write in different ways [message #625228 is a reply to message #625219]

Fri, 03 October 2014 07:25

na.dharma@gmail.com
Messages: 82
Registered: May 2008
Location: bangalore

Member

Thanks

If i pass 'Prod' then the select query should return null and prod record
if i pass 'Prodparallel' then the select query should return null and Prodparallel record.

but this query can not achive the above requirement.

select * from prod where nvl(profile_type, 'Prod') = 'Prod';

Can you please tell. the above requirement how can achive with your above query

Report message to a moderator

Re: How to avoid OR condition in the below scenario, how we can write in different ways [message #625229 is a reply to message #625228]

Fri, 03 October 2014 07:27

pablolee
Messages: 2882
Registered: May 2007
Location: Scotland

Senior Member

First sho3w us how your example from your first post meets the requirements that you stated in your second post;

Report message to a moderator

Re: How to avoid OR condition in the below scenario, how we can write in different ways [message #625232 is a reply to message #625229]

Fri, 03 October 2014 07:35

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

You need your original query with the OR. Why do you not want to use it?

[Updated on: Fri, 03 October 2014 07:35]

Report message to a moderator

Re: How to avoid OR condition in the below scenario, how we can write in different ways [message #625234 is a reply to message #625229]

Fri, 03 October 2014 07:38

na.dharma@gmail.com
Messages: 82
Registered: May 2008
Location: bangalore

Member

this is my sample data

begin
create table prod(profile_type varchar2(15)) tablespace warehouse_big_Data;

insert into prod values(null);
insert into prod values('Prod');
insert into prod values('Prodparallel');
insert into prod values(null);
insert into prod values('Prod');
insert into prod values(null);
insert into prod values('Prodparallel');
insert into prod values('Prod');
end;

my requirement is

If i pass 'Prod' then the select query should return null and prod record
if i pass 'Prodparallel' then the select query should return null and Prodparallel record.

Note:- OR condition should not use.

Report message to a moderator

Re: How to avoid OR condition in the below scenario, how we can write in different ways [message #625235 is a reply to message #625234]

Fri, 03 October 2014 07:39

pablolee
Messages: 2882
Registered: May 2007
Location: Scotland

Senior Member

How are you passing the value?

[Updated on: Fri, 03 October 2014 07:42]

Report message to a moderator

Re: How to avoid OR condition in the below scenario, how we can write in different ways [message #625242 is a reply to message #625235]

Fri, 03 October 2014 07:45

na.dharma@gmail.com
Messages: 82
Registered: May 2008
Location: bangalore

Member

Through parameter

Example

Select * from prod where profile_type = p_profile_type

Report message to a moderator

Re: How to avoid OR condition in the below scenario, how we can write in different ways [message #625245 is a reply to message #625242]

Fri, 03 October 2014 08:03

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

Simple modification of LFs query:

Select * from prod where nvl(profile_type , p_profile_type) = p_profile_type

By the way your table create script doesn't work - we don't have your tablespace and you can't use create table inside a pl/sql block like that.
Also it should in code tags as described here: How to use [code] tags and make your code easier to read?

Report message to a moderator

Re: How to avoid OR condition in the below scenario, how we can write in different ways [message #625279 is a reply to message #625245]

Fri, 03 October 2014 10:17

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

@OP, After several requests you have not yet told us "why" you want to avoid "OR".

I tried to understand the entire thread, and I think a `CASE` would also work(not a good idea really), but this is what I could understand about the requirement so far.

Report message to a moderator

Re: How to avoid OR condition in the below scenario, how we can write in different ways [message #625338 is a reply to message #625279]

Mon, 06 October 2014 04:33

na.dharma@gmail.com
Messages: 82
Registered: May 2008
Location: bangalore

Member

OR is performance issue, that is reason I want avoid OR logical operator

Report message to a moderator

Re: How to avoid OR condition in the below scenario, how we can write in different ways [message #625339 is a reply to message #625338]

Mon, 06 October 2014 04:37

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

It is no more of a performance issue than any of the alternatives.

Report message to a moderator

Re: How to avoid OR condition in the below scenario, how we can write in different ways [message #625347 is a reply to message #625338]

Mon, 06 October 2014 06:32

sandeep_orafaq
Messages: 88
Registered: September 2014

Member

It is really not clear why OR is causing Performance issue for you. Anyways can you please Elaborate more on why you Need this Kind of result pattern, what is the Business case for it.
Anyways, if you really do not want to use 'OR' then probably you can use 'case' as suggested earlier.

Select * from prod where
case when p_profile_type = 'Prod' then nvl(profile_type , p_profile_type)
else profile_type end = p_profile_type

here p_profile_type is the Parameter that you pass. But not sure if this can give you any better Performance.

[Updated on: Mon, 06 October 2014 06:33]

Report message to a moderator

Re: How to avoid OR condition in the below scenario, how we can write in different ways [message #625353 is a reply to message #625347]

Mon, 06 October 2014 07:33

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

OPs requirement is return all records where profile_type = parameter or profile_type is null, so that case is wrong, and fixing it leaves you with the nvl example I posted above.
I would imagine that it's slower than just doing profile_type = parameter as the OR stops it using an index on profile_type, but all the alternatives have the same problem.

Report message to a moderator

Re: How to avoid OR condition in the below scenario, how we can write in different ways [message #625354 is a reply to message #625353]

Mon, 06 October 2014 07:59

sandeep_orafaq
Messages: 88
Registered: September 2014

Member

I think your nvl example

Select * from prod where nvl(profile_type , p_profile_type) = p_profile_type

will not solve your requirement, it will work same way for all passed Parameters.

Where as

Select * from prod where
case when p_profile_type = 'Prod' then nvl(profile_type , p_profile_type)
else profile_type end = p_profile_type

should work based on your Output requirement.

Somehow this Looks like a homework question.

Can you explain why you have a Performance issue using 'OR' Statement. How many rows you have in your table? whar the Indexes? etc

We can give proper solution if we know the correct Problem. not using 'OR' is not solution to the Performance Problem. I guess.

Report message to a moderator

Re: How to avoid OR condition in the below scenario, how we can write in different ways [message #625355 is a reply to message #625354]

Mon, 06 October 2014 08:19

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

sandeep_orafaq wrote on Mon, 06 October 2014 13:59

I think your nvl example

Select * from prod where nvl(profile_type , p_profile_type) = p_profile_type

will not solve your requirement, it will work same way for all passed Parameters.

That's what the OP said they wanted, twice:
na.dharma@gmail.com wrote on Fri, 03 October 2014 13:25

Thanks

If i pass 'Prod' then the select query should return null and prod record
if i pass 'Prodparallel' then the select query should return null and Prodparallel record.

na.dharma@gmail.com wrote on Fri, 03 October 2014 13:38

my requirement is

If i pass 'Prod' then the select query should return null and prod record
if i pass 'Prodparallel' then the select query should return null and Prodparallel record.

Note:- OR condition should not use.

The example selects OP posted are not the requriement.

Report message to a moderator

Re: How to avoid OR condition in the below scenario, how we can write in different ways [message #625356 is a reply to message #625354]

Mon, 06 October 2014 08:27

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

How many cases would you go on writing if OP wants all the rows for any input parameter value? That is why I said that CASE is just an option, but not a good idea.

Report message to a moderator

Re: How to avoid OR condition in the below scenario, how we can write in different ways [message #625357 is a reply to message #625218]

Mon, 06 October 2014 08:38

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

If performance is an issue, then I would have thought that an OR construct would likely be best. Using a bitmap index would give an efficient access path if the cardinality of the query is not too high, or if the CBO chooses a scan then that would be quicker if it does not have to evaluate a function.

Report message to a moderator

Re: How to avoid OR condition in the below scenario, how we can write in different ways [message #625366 is a reply to message #625357]

Mon, 06 October 2014 16:07

Bill B
Messages: 1971
Registered: December 2004

Senior Member

even if you index the profile_type column it will always do a full table scan because nulls by them self are not included in indexes. Using an OR clause or an nvl will both have to do a full table scan. However if your table was something like

create table prod(profile_type varchar2(15), description varchar2(30) not null) tablespace warehouse_big_Data;

insert into prod values(null,'a');
insert into prod values('Prod','b');
insert into prod values('Prodparallel','a');
insert into prod values(null,'c');
insert into prod values('Prod','x');
create index prod_i1 on prod(profile_type,description);

The nulls will be indexed and your query will go much faster (assuming that you have more then 5 records..lol)

[Updated on: Mon, 06 October 2014 16:08]

Report message to a moderator

Re: How to avoid OR condition in the below scenario, how we can write in different ways [message #625371 is a reply to message #625366]

Tue, 07 October 2014 00:36

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Or, if the table look like the way OP mentioned, then we could make the leaves of the b-tree index a constant. Thus, making use of index while querying for NULL.

The first scenario won't use the index due to the OR is null condition :

SQL> SELECT * FROM PROD_NEW;

PROFILE_TYPE
---------------

Prod
Prodparallel

Prod

SQL> CREATE INDEX PROD_NEW_I1 ON PROD_NEW
  2    (PROFILE_TYPE
  3    );

Index created.

SQL> EXPLAIN PLAN FOR SELECT * FROM PROD_NEW WHERE PROFILE_TYPE = 'Prod' OR PROFILE_TYPE IS NULL;

Explained.

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2121244107

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

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

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

   1 - filter("PROFILE_TYPE" IS NULL OR "PROFILE_TYPE"='Prod')

13 rows selected.

Let's make the leaves constant :

SQL> DROP INDEX PROD_NEW_I1;

Index dropped.

SQL> CREATE INDEX PROD_NEW_I1 ON PROD_NEW
  2    (PROFILE_TYPE,1
  3    );

Index created.

SQL> EXPLAIN PLAN FOR SELECT * FROM PROD_NEW WHERE PROFILE_TYPE = 'Prod' OR PROFILE_TYPE IS NULL;

Explained.

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1272076902

--------------------------------------------------------------------------------
| Id  | Operation        | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |             |     3 |    15 |     1   (0)| 00:00:01 |
|*  1 |  INDEX FULL SCAN | PROD_NEW_I1 |     3 |    15 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------

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

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

   1 - filter("PROFILE_TYPE" IS NULL OR "PROFILE_TYPE"='Prod')

13 rows selected.

SQL>

So, basically, the NULLs are all together, at the top/bottom of the index. Oracle can use the index forwards or backwards, so doesn't really matter. And it does a full scan of the index.

Report message to a moderator

Re: How to avoid OR condition in the below scenario, how we can write in different ways [message #625373 is a reply to message #625366]

Tue, 07 October 2014 00:53

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

Quote:

it will always do a full table scan because nulls by them self are not included in indexes.

Indexing NULLs is no problem - if you use a bitmap index. Much easier than a function based ndex.

Report message to a moderator

Re: How to avoid OR condition in the below scenario, how we can write in different ways [message #625384 is a reply to message #625373]

Tue, 07 October 2014 03:36

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

true, but if it's an OLTP system and that table gets modified a lot then bitmaps aren't a good idea.

Report message to a moderator

Re: How to avoid OR condition in the below scenario, how we can write in different ways [message #625386 is a reply to message #625371]

Tue, 07 October 2014 03:53

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

Lalit, your solution is interesting - but I do not think it is actually going to work. Consider what happens if the index does not cover all the projected columns:

12c orclz> SELECT * FROM PROD_NEW WHERE PROFILE_TYPE = 'Prod' OR PROFILE_TYPE IS NULL;

Execution Plan
----------------------------------------------------------
Plan hash value: 1272076902

--------------------------------------------------------------------------------
| Id  | Operation        | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |             |    24 |   216 |     1   (0)| 00:00:01 |
|*  1 |  INDEX FULL SCAN | PROD_NEW_I1 |    24 |   216 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------

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

   1 - filter("PROFILE_TYPE" IS NULL OR "PROFILE_TYPE"='Prod')

12c orclz> alter table prod_new add(c2 date);

Table altered.

12c orclz> SELECT * FROM PROD_NEW WHERE PROFILE_TYPE = 'Prod' OR PROFILE_TYPE IS NULL;

Execution Plan
----------------------------------------------------------
Plan hash value: 2121244107

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

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

   1 - filter("PROFILE_TYPE" IS NULL OR "PROFILE_TYPE"='Prod')

Of course I can hint it,

12c orclz> SELECT /*+ index(prod_new PROD_NEW_I1) */ * FROM PROD_NEW WHERE PROFILE_TYPE = 'Prod' OR PROFILE_TYPE IS NULL;

Execution Plan
----------------------------------------------------------
Plan hash value: 1168636225

---------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |             |    24 |   432 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| PROD_NEW    |    24 |   432 |     2   (0)| 00:00:01 |
|*  2 |   INDEX FULL SCAN                   | PROD_NEW_I1 |    24 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

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

   2 - filter("PROFILE_TYPE" IS NULL OR "PROFILE_TYPE"='Prod')

but that would be single block reads for every index leaf node at id 2 plus a single block read for every row at id 1, and I can't see the CBO ever choosing that. I did try hinting an index fast full scan to make id 2 a bit more efficient, but it doesn't happen

Report message to a moderator

Re: How to avoid OR condition in the below scenario, how we can write in different ways [message #625389 is a reply to message #625386]

Tue, 07 October 2014 04:24

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

John, yes my solution would only work with the single column as I mentioned that OP's table has single column.

In your example, after you alter the table to add new column, adding a NOT NULL column to the index will get NULL profile_type indexed. So, column c2 NOT NULL would definitely work. That's what Bill already suggested I believe.

SQL> CREATE TABLE prod
  2    (profile_type VARCHAR2(15), c2 DATE NOT NULL
  3    );

Table created.

SQL> CREATE INDEX prod_indx ON prod
  2    (profile_type,c2
  3    );

Index created.

SQL> EXPLAIN PLAN FOR SELECT * FROM PROD WHERE PROFILE_TYPE = 'Prod' OR PROFILE_TYPE IS NULL;

Explained.

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2945788133

------------------------------------------------------------------------------
| Id  | Operation        | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |           |     1 |    18 |     1   (0)| 00:00:01 |
|*  1 |  INDEX FULL SCAN | PROD_INDX |     1 |    18 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------

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

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

   1 - filter("PROFILE_TYPE" IS NULL OR "PROFILE_TYPE"='Prod')

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

17 rows selected.

SQL>

Ed : Added test case

[Updated on: Tue, 07 October 2014 04:29]

Report message to a moderator

Previous Topic:	Outer Join on Values
Next Topic:	Session specific data using gtt

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 23:58:16 CDT 2024