Home » SQL & PL/SQL » SQL & PL/SQL » Alternative way to write the query (Oracle9i)
Alternative way to write the query [message #345660] Thu, 04 September 2008 07:08 Go to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member

select  count(distinct p.item_id)
             frpm item p, order c
        where item_id in (select  order_item_id from order_item b 
		where b.last_ins_timestmp between To_date('01/04/2008','DD/MM/YYYY') and To_date('08/05/2008', 'DD/MM/YYYY')
        and b.tranfer_id in 
		(select  tranfer_id from transfer_data_tbl
        where ins_timestmp between To_date('01/04/2008',  'DD/MM/YYYY')  and To_date('08/05/2008', 'DD/MM/YYYY')))
        and p.item_id = c.item_id
        and p.destination_id not in ('OHI')
        and p.source_id = 'ALB';





Can the query be modified instead of using NOT IN?
[/code]
and p.destination_id not in ('OHI')
[code]

Regards,
Oli
Re: Alternative way to write the query [message #345662 is a reply to message #345660] Thu, 04 September 2008 07:10 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
and p.destination_id != 'OHI'
Re: Alternative way to write the query [message #345665 is a reply to message #345662] Thu, 04 September 2008 07:19 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
JRowbottom wrote on Thu, 04 September 2008 07:10
and p.destination_id != 'OHI'




@Jrowbottom
Using Outer join?

Thanks for the reply...
Reagrds,

Re: Alternative way to write the query [message #345667 is a reply to message #345665] Thu, 04 September 2008 07:20 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
An outer join would return a different set of results to the query you've got.

What are you trying to achieve?
Re: Alternative way to write the query [message #345668 is a reply to message #345667] Thu, 04 September 2008 07:22 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
I want to use any alternative other than NOT IN in where clause for the same.




Regards,
Oli
Re: Alternative way to write the query [message #345671 is a reply to message #345668] Thu, 04 September 2008 07:30 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I seem to remember that the optimizer translates singleton INs and (perhaps) NOT INs to = and !=.

If I'm right in this recollection, you should see it in the FILTER CRITERIA of Explain Plan.

Anyway, what's wrong with a NOT IN list?

Ross Leishman
Re: Alternative way to write the query [message #345679 is a reply to message #345671] Thu, 04 September 2008 07:43 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Here is the plan:


----------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------
| Id  | Operation                         |  Name                       | Rows  | Bytes | Cost  |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                             |     1 |    70 |   874 |
|   3 |  TEMP TABLE TRANSFORMATION        |                             |       |       |       |
|   1 |   RECURSIVE EXECUTION             | SYS_LE_3_0                  |       |       |       |
|   0 |    INSERT STATEMENT               |                             |  1643 | 31217 |   861 |
|   1 |     LOAD AS SELECT                |                             |       |       |       |
|   2 |      TABLE ACCESS FULL            | ORDER_ITEM          	|  1643 | 31217 |   861 |
|   2 |   RECURSIVE EXECUTION             | SYS_LE_3_1                  |       |       |       |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|   0 |    INSERT STATEMENT               |                             |  1990K|    45M|   151K|
|   1 |     LOAD AS SELECT                |                             |       |       |       |
|   2 |      TABLE ACCESS FULL            | ITEM			|  1990K|    45M|   151K|
|   4 |   SORT GROUP BY                   |                             |     1 |    70 |       |
|   5 |    TABLE ACCESS BY INDEX ROWID    | TRANSFER_DATA_TBL	        |     1 |    18 |     4 |
|   6 |     NESTED LOOPS                  |                             |     1 |    70 |   874 |
|   7 |      NESTED LOOPS                 |                             |     1 |    52 |   870 |
|   8 |       NESTED LOOPS                |                             |     1 |    43 |   868 |
|   9 |        TABLE ACCESS FULL          | ORDER_ITEM		        |     1 |    19 |   861 |
|  10 |        TABLE ACCESS BY INDEX ROWID| ITEM		        |     1 |    24 |     7 |
|  11 |         INDEX RANGE SCAN          | PK_ITEM		        |     4 |       |     3 |


Regards,
Oli

[Updated on: Thu, 04 September 2008 07:45]

Report message to a moderator

Re: Alternative way to write the query [message #345685 is a reply to message #345679] Thu, 04 September 2008 08:02 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Check the FILTER_PREDICATES and ACCESS_PREDICATES columns of PLAN_TABLE
Re: Alternative way to write the query [message #345688 is a reply to message #345668] Thu, 04 September 2008 08:12 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
Hi,

Try using a NOT EXISTS

Add the following code:
     where  NOT EXISTS 
         (Select destination_id 
               from item IN_Q 
                 where INQ.item_id = p.item_id and 
                 destination_id = 'OHI')
      -- rest of your conditions in the where clause.
   


I believe the above query is more efficient because Oracle can employ an anti-join access path. (I am kind of a beginner in Performance Tuning. So i suggest you wait for a comment from a Senior regarding this.)

Also we are always asked to avoid NOT IN as much as possible because of the problems NOT IN faces when dealing with NULL values.

I hope this helps,

Regards,
Jo
Re: Alternative way to write the query [message #345702 is a reply to message #345668] Thu, 04 September 2008 09:33 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
1) Why don't you want to use NOT IN
2) What's wrong with !=
3) What is the whoe query trying to achieve.
Re: Alternative way to write the query [message #345706 is a reply to message #345702] Thu, 04 September 2008 10:15 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
JRowbottom wrote on Thu, 04 September 2008 09:33
1) 3) What is the whoe query trying to achieve.



Thanks for the response.
The query is taking long time.Response time is quite high its
00:00:26.00 Sec

Also the use of distinct clause is taking much time.


Regards,
Oli
Re: Alternative way to write the query [message #345729 is a reply to message #345660] Thu, 04 September 2008 11:10 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Olivia wrote on Thu, 04 September 2008 14:08

select  count(distinct p.item_id)
             frpm item p, order c
        where item_id in (select  order_item_id from order_item b 
		where b.last_ins_timestmp between To_date('01/04/2008','DD/MM/YYYY') and To_date('08/05/2008', 'DD/MM/YYYY')
        and b.tranfer_id in 
		(select  tranfer_id from transfer_data_tbl
        where ins_timestmp between To_date('01/04/2008',  'DD/MM/YYYY')  and To_date('08/05/2008', 'DD/MM/YYYY')))
        and p.item_id = c.item_id
        and p.destination_id not in ('OHI')
        and p.source_id = 'ALB';





If I read your query correctly, I think you are not showing us the exact query you are executing.
In the first where-clause you refer to item_id without prefixing it. Yet, both p and c have a column item_id...
Re: Alternative way to write the query [message #345732 is a reply to message #345729] Thu, 04 September 2008 11:32 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Frank wrote on Thu, 04 September 2008 11:10
Olivia wrote on Thu, 04 September 2008 14:08

select  count(distinct p.item_id)
             frpm item p, order c
        where item_id in (select  order_item_id from order_item b 
		where b.last_ins_timestmp between To_date('01/04/2008','DD/MM/YYYY') and To_date('08/05/2008', 'DD/MM/YYYY')
        and b.tranfer_id in 
		(select  tranfer_id from transfer_data_tbl
        where ins_timestmp between To_date('01/04/2008',  'DD/MM/YYYY')  and To_date('08/05/2008', 'DD/MM/YYYY')))
        and p.item_id = c.item_id
        and p.destination_id not in ('OHI')
        and p.source_id = 'ALB';





If I read your query correctly, I think you are not showing us the exact query you are executing.
In the first where-clause you refer to item_id without prefixing it. Yet, both p and c have a column item_id...



Honestly, thats true..I candid! Yes, I am not being able to post the adject code due to restrictions imposed! Hope you will understand

Quote:
and p.item_id = c.item_id


should be
Quote:
and p.item_id = c.order_item_id



When I try using
SELECT COUNT(*) FROM 
(select  count( p.item_id)
             frpm item p, order c
        where item_id in (select  order_item_id from order_item b 
		where b.last_ins_timestmp between To_date('01/04/2008','DD/MM/YYYY') and To_date('08/05/2008', 'DD/MM/YYYY')
        and b.tranfer_id in 
		(select  tranfer_id from transfer_data_tbl
        where ins_timestmp between To_date('01/04/2008',  'DD/MM/YYYY')  and To_date('08/05/2008', 'DD/MM/YYYY')))
        and p.item_id = c.order_item_id
        and p.destination_id not in ('OHI')
        and p.source_id = 'ALB'
GROUP BY p.item_id );


The response time is quite good.Its 00:00:02.00



Regards,
Oli

[Updated on: Thu, 04 September 2008 11:34]

Report message to a moderator

Re: Alternative way to write the query [message #345801 is a reply to message #345732] Thu, 04 September 2008 19:47 Go to previous messageGo to next message
harrysmall3
Messages: 109
Registered: April 2008
Location: Massachusetts
Senior Member
The 'Not In' caveat is that nulls cannot be evaluated
with equality or inequality.

So if you treat 'Not In' as !=, then if your NOT IN clause is
a select clause and the column queried returns a null, the
entire condition fails - so just use NVL() or add IS NOT NULL
to the criteria.

I had tended to preference NOT IN/IN over EXISTS as most of
my work is on table joins where I never benefit from an Index,
that Nested Loop is like a cancer to my execution plan Smile!,
and I was under the impression that Oracle could
"push" the subquery up, turn it into a join,
with an on the fly bitmap index to boot. (Something I thought
not possible with WHERE EXISTS and I don't recall the
version I was on when I had that impression).

WHERE EXISTS tended to favor index usage. i say 'under the
impression' and 'tended' because since I have been coming
to this site, the knowledge gained has shown me that what i thought I knew was only 'my impression'.

I've only played around with WHERE EXISTS to force an
INDEX fast full scan (not that I have had any need
found any where I have needed it)

Anyway I noticed a lot of nested loops in the execution plan,
wanted to first add my two cents on the NOT IN/Null topic,
and then see if you were curious to examine how a hash join
would perform in place:


Select count(*) from 

(SELECT /*+ use_hash(p,c) */ COUNT(p.item_id) FROM ITEM P, 
			                           ORDER C
WHERE 
	p.item_id = c.order_item_id 	and
	p.destination_id not in ('OHI') and
	p.source_id = 'ALB'		and
	p.item_id IN
(
SELECT /*+ use_hash(OI,TD) */
	OI.order_item_id FROM ORDER_ITEM OI,
       			      TRANSFER_DATA_TBL TD
WHERE    OI.transfer_id = TD.transfer_id 
AND      OI.last_ins_timestmp between 
         To_date('01/04/2008','DD/MM/YYYY') and 
	 To_date('08/05/2008', 'DD/MM/YYYY')
)
GROUP BY p.item_id);


I suggest nothing with the above, it's only for curiosity sake.
I am not at a terminal so I apologize for any fat fingering.

I am intrigued very much by the DISTINCT and GROUP BY performance
difference and will be putting those candidates on my
"Truthes to unravel" list.

Best Regards,
Harry
Re: Alternative way to write the query [message #345818 is a reply to message #345679] Thu, 04 September 2008 22:42 Go to previous message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Olivia wrote on Thu, 04 September 2008 22:43
Here is the plan:


----------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------
| Id  | Operation                         |  Name                       | Rows  | Bytes | Cost  |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                             |     1 |    70 |   874 |
|   3 |  TEMP TABLE TRANSFORMATION        |                             |       |       |       |
|   1 |   RECURSIVE EXECUTION             | SYS_LE_3_0                  |       |       |       |
|   0 |    INSERT STATEMENT               |                             |  1643 | 31217 |   861 |
|   1 |     LOAD AS SELECT                |                             |       |       |       |
|   2 |      TABLE ACCESS FULL            | ORDER_ITEM          	|  1643 | 31217 |   861 |
|   2 |   RECURSIVE EXECUTION             | SYS_LE_3_1                  |       |       |       |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|   0 |    INSERT STATEMENT               |                             |  1990K|    45M|   151K|
|   1 |     LOAD AS SELECT                |                             |       |       |       |
|   2 |      TABLE ACCESS FULL            | ITEM			|  1990K|    45M|   151K|
|   4 |   SORT GROUP BY                   |                             |     1 |    70 |       |
|   5 |    TABLE ACCESS BY INDEX ROWID    | TRANSFER_DATA_TBL	        |     1 |    18 |     4 |
|   6 |     NESTED LOOPS                  |                             |     1 |    70 |   874 |
|   7 |      NESTED LOOPS                 |                             |     1 |    52 |   870 |
|   8 |       NESTED LOOPS                |                             |     1 |    43 |   868 |
|   9 |        TABLE ACCESS FULL          | ORDER_ITEM		        |     1 |    19 |   861 |
|  10 |        TABLE ACCESS BY INDEX ROWID| ITEM		        |     1 |    24 |     7 |
|  11 |         INDEX RANGE SCAN          | PK_ITEM		        |     4 |       |     3 |


Regards,
Oli


This plan looks suspect to me. You shouldn't have rows with the same ID, and the IDs should be sequentially numbered from 0.

I suspect this is actually the result of not having cleared your plan table after previous statements.

Ross Leishman
Previous Topic: Stored procedure to delete information within number of days?
Next Topic: sum multiple column with different criteria
Goto Forum:
  


Current Time: Sun Feb 16 07:14:06 CST 2025