Alternative way to write the query [message #345660] |
Thu, 04 September 2008 07:08  |
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 #345671 is a reply to message #345668] |
Thu, 04 September 2008 07:30   |
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   |
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 #345688 is a reply to message #345668] |
Thu, 04 September 2008 08:12   |
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 #345706 is a reply to message #345702] |
Thu, 04 September 2008 10:15   |
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   |
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   |
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   |
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 !,
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  |
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
|
|
|