| [noob] mixing the result of 2 queries [message #268405] |
Tue, 18 September 2007 08:00  |
b_52globemaster
Messages: 51
Registered: July 2005
|
Member |
|
|
hi
i got 2 queries
select a.num_projet , a.num_ressources as leadteam from
projet_management a where a.num_ressources=41;
----------------------------------------------------------
NUM_PROJET LEADTEAM
---------- ----------
1 41
------------------------------------------------------------
second query :
select a.num_projet , a.num_employe as developer from taux_projet a where num_projet = 1;
--------------------------------------------------------
NUM_PROJET DEVELOPER
---------- ----------
1 1
1 41
--------------------------------------------------------
i want to get the lead dev and the developers that are working with him on the same project?
and thanks a lot
[Updated on: Tue, 18 September 2007 10:19] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
| Re: [noob] mixing the result of 2 queries [message #268678 is a reply to message #268405] |
Wed, 19 September 2007 06:17  |
b_52globemaster
Messages: 51
Registered: July 2005
|
Member |
|
|
select
distinct(c.num_ressource) as emp , c.nom , b.num_projet , b. num_ressources as leadteam from projet_management b,taux_projet a , employes c
where b.num_projet = a .num_projet
and c.num_ressource = a. num_employe
and b.num_ressources= 41 ;
it's not optimized but
output:
EMP NOM NUM_PROJET LEADTEAM
----------------------------------------------------------------
41 user 1 41
1 sky 1 41
1 sky 3 41
and thanks
|
|
|
|
| Re: [noob] mixing the result of 2 queries [message #268683 is a reply to message #268678] |
Wed, 19 September 2007 06:24 |
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
|
Senior Member |
|
|
| b_52globemaster wrote on Wed, 19 September 2007 06:17 | select
distinct(c.num_ressource) as emp , c.nom , b.num_projet , b. num_ressources as leadteam from projet_management b,taux_projet a , employes c
where b.num_projet = a .num_projet
and c.num_ressource = a. num_employe
and b.num_ressources= 41 ;
|
If that satisfy your requirement then you can swap 2 criteria in where clause, it will optimize your performance.
select distinct(c.num_ressource) as emp , c.nom , b.num_projet ,
b. num_ressources as leadteam
from projet_management b,taux_projet a , employes c
where c.num_ressource = a. num_employe
and b.num_projet = a .num_projet
and b.num_ressources= 41 ;
|
|
|
|
|
|
| Re: [noob] mixing the result of 2 queries [message #268687 is a reply to message #268683] |
Wed, 19 September 2007 06:36 |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
| muzahidul islam wrote on Wed, 19 September 2007 13:24 | | b_52globemaster wrote on Wed, 19 September 2007 06:17 | select
distinct(c.num_ressource) as emp , c.nom , b.num_projet , b. num_ressources as leadteam from projet_management b,taux_projet a , employes c
where b.num_projet = a .num_projet
and c.num_ressource = a. num_employe
and b.num_ressources= 41 ;
|
If that satisfy your requirement then you can swap 2 criteria in where clause, it will optimize your performance.
select distinct(c.num_ressource) as emp , c.nom , b.num_projet ,
b. num_ressources as leadteam
from projet_management b,taux_projet a , employes c
where c.num_ressource = a. num_employe
and b.num_projet = a .num_projet
and b.num_ressources= 41 ;
|
What makes you thing that this wil increase performance??
@b_52globemaster:
Don't expect that the parentheses around c.num_ressource) will mean that distinct only has an effect on that column.
In Oracle, distinct works on the row as a whole.
|
|
|
|
|
|
| Re: [noob] mixing the result of 2 queries [message #268834 is a reply to message #268405] |
Wed, 19 September 2007 21:53 |
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
|
Senior Member |
|
|
| Quote: |
What makes you thing that this will increase performance??
|
See one of my test query and tkprof output
Query1:
select a.*
from t_trans_ledger a, t_trans b ,t_account c
where
c.acc_no = b.acc_no
and a.tr_id = b.tr_id
and c.acc_type='07'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 10 0.00 1.57 114 874 0 132
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 12 0.00 1.59 114 874 0 132
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 89
Rows Row Source Operation
------- ---------------------------------------------------
132 TABLE ACCESS BY INDEX ROWID T_TRANS_LEDGER
234 NESTED LOOPS
101 NESTED LOOPS
12 TABLE ACCESS FULL T_ACCOUNT
101 TABLE ACCESS BY INDEX ROWID T_TRANS
101 INDEX RANGE SCAN IK_TRANS_ACC_NO (object id 71199)
132 INDEX RANGE SCAN IK_TRANS_LEDGER_TR_ID (object id 71221)
Query2:
select a.*
from t_trans_ledger a, t_trans b ,t_account c
where
a.tr_id = b.tr_id
and c.acc_no = b.acc_no
and c.acc_type='07'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 10 0.00 0.00 0 874 0 132
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 12 0.00 0.00 0 874 0 132
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 89
Rows Row Source Operation
------- ---------------------------------------------------
132 TABLE ACCESS BY INDEX ROWID T_TRANS_LEDGER
234 NESTED LOOPS
101 NESTED LOOPS
12 TABLE ACCESS FULL T_ACCOUNT
101 TABLE ACCESS BY INDEX ROWID T_TRANS
101 INDEX RANGE SCAN IK_TRANS_ACC_NO (object id 71199)
132 INDEX RANGE SCAN IK_TRANS_LEDGER_TR_ID (object id 71221)
At second query there is no disk access as well as no elapsed time for fetch call.
|
|
|
|
|
|
| Re: [noob] mixing the result of 2 queries [message #268853 is a reply to message #268405] |
Wed, 19 September 2007 22:41 |
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
|
Senior Member |
|
|
Thanks
| Quote: |
What happens if after running the 2nd query, you immediately re-run the 1st query again, again.
|
But is there no impact on ordering at where clause? How sql execute, in which order?
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
