| to reduce the disk reads [message #461879] |
Tue, 22 June 2010 01:24  |
akilabaskaran
Messages: 29
Registered: May 2007
Location: chennai
|
Junior Member |
|
|
Hi, i have a code as below......
it uses lots of disk reads as the tables are used again and again.
is it possible to rewrite the code to such an extent that the tables are used minimum.
Kindly let me know your suggestions
select col1
from (select distinct a.col1 from a inner join b on a.col1 = b.col1
inner join c on c.col2 = b.col2
left join d on d.col1 = a.col1
and not exists (select 1 from b inner join c on c.col2 = b.col2
where c.status in (1,2,3)
and b.col1 = a.col1).
and exists (select 1 from b inner join c on c.col2 = b.col2
where c.status in (4,5)
and b.col1 = a.col1)
where rownum <=10 [Updated on: Tue, 22 June 2010 01:26] Report message to a moderator |
|
|
|
|
|
| Re: to reduce the disk reads [message #461907 is a reply to message #461896] |
Tue, 22 June 2010 01:57  |
akilabaskaran
Messages: 29
Registered: May 2007
Location: chennai
|
Junior Member |
|
|
explain plan is as follows
SELECT STATEMENT ALL_ROWSCost: 108 Bytes: 78 Cardinality: 6
24 COUNT STOPKEY
23 VIEW PLAYWEBORDER. Cost: 108 Bytes: 78 Cardinality: 6
22 SORT GROUP BY STOPKEY Cost: 108 Bytes: 468 Cardinality: 6
21 FILTER
16 NESTED LOOPS Cost: 90 Bytes: 546 Cardinality: 7
13 NESTED LOOPS Cost: 76 Bytes: 469 Cardinality: 7
11 NESTED LOOPS Cost: 64 Bytes: 336 Cardinality: 6
8 NESTED LOOPS Cost: 52 Bytes: 276 Cardinality: 6
6 FILTER
5 NESTED LOOPS OUTER Cost: 40 Bytes: 210 Cardinality: 6
2 PARTITION LIST SINGLE Cost: 39 Bytes: 126 Cardinality: 6 Partition #: 11 Partitions determined by Key Values
1 TABLE ACCESS FULL TABLE a Cost: 39 Bytes: 126 Cardinality: 6 Partition #: 12 Partitions accessed #4
4 TABLE ACCESS BY INDEX ROWID TABLE d Cost: 1 Bytes: 14 Cardinality: 1
3 INDEX UNIQUE SCAN INDEX (UNIQUE) pk_d Cost: 0 Cardinality: 1
7 INDEX RANGE SCAN INDEX (UNIQUE) pk_b Cost: 2 Bytes: 11 Cardinality: 1
10 TABLE ACCESS BY INDEX ROWID TABLE c Cost: 2 Bytes: 10 Cardinality: 1
9 INDEX UNIQUE SCAN INDEX (UNIQUE) pk_c Cost: 1 Cardinality: 1
12 INDEX RANGE SCAN INDEX (UNIQUE) pk_b Cost: 2 Bytes: 11 Cardinality: 1
15 TABLE ACCESS BY INDEX ROWID TABLE cN Cost: 2 Bytes: 11 Cardinality: 1
14 INDEX UNIQUE SCAN INDEX (UNIQUE)pk_c Cost: 1 Cardinality: 1
20 NESTED LOOPS Cost: 5 Bytes: 22 Cardinality: 1
17 INDEX RANGE SCAN INDEX (UNIQUE) pk_b Cost: 3 Bytes: 11 Cardinality: 1
19 TABLE ACCESS BY INDEX ROWID TABLE c Cost: 2 Bytes: 11 Cardinality: 1
18 INDEX UNIQUE SCAN INDEX (UNIQUE) pk_c Cost: 1 Cardinality: 1 |
|
|
|
| Re: to reduce the disk reads [message #461913 is a reply to message #461907] |
Tue, 22 June 2010 02:07 |
 |
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
|
Senior Member |
|
|
select col1
from (select distinct a.col1 from a inner join b on a.col1 = b.col1
inner join c on c.col2 = b.col2
left join d on d.col1 = a.col1
and not exists (select 1 from b inner join c on c.col2 = b.col2
where c.status in (1,2,3)
and b.col1 = a.col1).
and exists (select 1 from b inner join c on c.col2 = b.col2
where c.status in (4,5)
and b.col1 = a.col1)
where rownum <=10
exists (select 1 from b inner join c on c.col2 = b.col2 where c.status in (1,2,3)
AND
and exists (select 1 from b inner join c on c.col2 = b.col2 where c.status in (4,5)
are contradictory you need just exists (select 1 from b inner join c on c.col2 = b.col2 where c.status in (4,5)
this query will NOT get the values for 1,2,3
|
|
|
|
| Re: to reduce the disk reads [message #461917 is a reply to message #461913] |
Tue, 22 June 2010 02:14 |
akilabaskaran
Messages: 29
Registered: May 2007
Location: chennai
|
Junior Member |
|
|
Hi,
Let me explain a scenario ..
i have many records with the same col1 values with different status as 1,2,3,4,5.
what i want to do is to select those records which have (4,5)
as their status but do not have 1,2,3 in them.
Eg: col1 status
100 4
100 4
100 5
100 1
this record should not be returned since it has 1 in its status
Eg: col1 status
200 4
200 4
200 5
This record should be selected.
|
|
|
|
|
|
| Re: to reduce the disk reads [message #461929 is a reply to message #461926] |
Tue, 22 June 2010 03:00 |
akilabaskaran
Messages: 29
Registered: May 2007
Location: chennai
|
Junior Member |
|
|
I just need records which have their status only as 4 or 5(in the whole table)
if the history records(of the same table) for the same col1 value ever had 1,2,3
then it should never be selected
|
|
|
|
| Re: to reduce the disk reads [message #461958 is a reply to message #461929] |
Tue, 22 June 2010 04:32 |
cookiemonster
Messages: 13921
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Why are you joining to d since you don't use it in query? Why is it a left join? And why have to tacked the exists sub-queries onto the outer join clause - I suspect it doesn't do what you think it does.
Also you should have an order by - distinct will probably give you the values in ascending order but it isn't guaranteed.
Suspect your query should be:
select col1
from (select distinct a.col1
from a inner join b on a.col1 = b.col1
inner join c on c.col2 = b.col2
WHERE c.status IN (4,5)
AND not exists (select 1
from b b2 inner join c c2 on c2.col2 = b2.col2
where c2.status in (1,2,3)
and b2.col1 = a.col1)
ORDER BY col1)
where rownum <=10;
@rahulvb - I think you missed the NOT in the original query.
|
|
|
|
| Re: to reduce the disk reads [message #461959 is a reply to message #461958] |
Tue, 22 June 2010 04:39 |
akilabaskaran
Messages: 29
Registered: May 2007
Location: chennai
|
Junior Member |
|
|
Actually i have further conditions based on the table d so the actual query is like
select col1
from (select distinct a.col1 from a inner join b on a.col1 = b.col1
inner join c on c.col2 = b.col2
left join d on d.col1 = a.col1
and not exists (select 1 from b inner join c on c.col2 = b.col2
where c.status in (1,2,3)
and b.col1 = a.col1).
and exists (select 1 from b inner join c on c.col2 = b.col2
where c.status in (4,5)
and b.col1 = a.col1
and d.dat_req is not null OR
((SYSDATE - d.dat_req) *24*60) >10))
where rownum <=10
|
|
|
|
| Re: to reduce the disk reads [message #461964 is a reply to message #461959] |
Tue, 22 June 2010 05:03 |
cookiemonster
Messages: 13921
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
You're asking us to rewrite a query and you haven't given the full query?!?!?
What exactly do you expect us to do with that?
The new addition to your query makes no sense:
and d.dat_req is not null OR
((SYSDATE - d.dat_req) *24*60) >10))
Why do you have an OR there? It means that if dat_req is null it'll do the second line, which can never be true if dat_req is null.
|
|
|
|
| Re: to reduce the disk reads [message #461978 is a reply to message #461964] |
Tue, 22 June 2010 05:20 |
akilabaskaran
Messages: 29
Registered: May 2007
Location: chennai
|
Junior Member |
|
|
sorry for not giving the complete query
i was thinking that iam simplifying the work for you...but it has actually made your work tough. sorry again.
here is the complete query
select col1
from (select distinct a.col1 from a inner join b on a.col1 = b.col1
inner join c on c.col2 = b.col2
left join d on d.col1 = a.col1
where a.siteid = 1000
and a.status = 'Authorise'
and not exists (select 1 from b inner join c on c.col2 = b.col2
where c.status in (1,2,3)
and b.col1 = a.col1).
and exists (select 1 from b inner join c on c.col2 = b.col2
where c.status in (4,5)
and b.col1 = a.col1
and (c.dat_charging_requested IS NULL OR
((SYSDATE - c.dat_charging_requested) *24*60) > 10)
AND ( d.dat_requested IS NULL OR
((SYSDATE - d.dat_requested) *24*60) > 10);
where rownum <=10
|
|
|
|
| Re: to reduce the disk reads [message #461983 is a reply to message #461978] |
Tue, 22 June 2010 05:24 |
cookiemonster
Messages: 13921
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
No it isn't - you've got mismatched brackets, a rogue semi-colon and a dot.
Suggest you check the query you want to post will actually run before you post it.
|
|
|
|
| Re: to reduce the disk reads [message #461990 is a reply to message #461983] |
Tue, 22 June 2010 05:41 |
akilabaskaran
Messages: 29
Registered: May 2007
Location: chennai
|
Junior Member |
|
|
Final query .........
I am really sorry for wasting your time by giving invalid sql
SELECT col1
FROM
(
SELECT DISTINCT a.col1
FROM a
INNER JOIN b dto ON b.col1 = a.col1
INNER JOIN c dt ON c.col2 = b.col2
LEFT JOIN d op ON d.col1 = a.col1
WHERE a.site IN ( 4097, 4098 )
AND a.status = 'AUTHORISE'
AND NOT EXISTS( SELECT *
FROM b dto
INNER JOIN c dt ON c.col2 = b.col2
WHERE c.int_transaction_type_id = 1
AND c.status IN (1,2,3)
AND b.col1 = a.col1)
AND EXISTS ( SELECT *
FROM b dto
INNER JOIN c dt ON c.col2 = b.col2
WHERE dt.int_transaction_type_id = 1
AND c.status IN( 4,5)
AND b.col1 = a.col1)
AND (c.dat_charging_requested IS NULL OR
(SYSDATE - c.dat_charging_requested) *24*60 >10)
AND ( d.dat_requested IS NULL OR
((SYSDATE - d.dat_requested) *24*60) >10)
)
WHERE ROWNUM <=10; |
|
|
|
| Re: to reduce the disk reads [message #462004 is a reply to message #461990] |
Tue, 22 June 2010 06:09 |
cookiemonster
Messages: 13921
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
That'll give invalid identifier errors since you've declared table aliases and then not bothered to use them.
You don't need the exists sub-query, you can do that check in the main where clause.
And next time just copy and paste the original query unaltered!!!!!
[Updated on: Tue, 22 June 2010 06:09] Report message to a moderator |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
