Home » Other » Training & Certification » [noob] mixing the result of 2 queries
[noob] mixing the result of 2 queries [message #268405] Tue, 18 September 2007 08:00 Go to next message
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 #268408 is a reply to message #268405] Tue, 18 September 2007 08:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Please always post your Oracle version (4 decimals).

Post a description of the table and their relations.
Post a test case.

Regards
Michel
Re: [noob] mixing the result of 2 queries [message #268500 is a reply to message #268405] Tue, 18 September 2007 23:30 Go to previous messageGo to next message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
Please give us your sample output.

Re: [noob] mixing the result of 2 queries [message #268660 is a reply to message #268405] Wed, 19 September 2007 05:07 Go to previous messageGo to next message
b_52globemaster
Messages: 51
Registered: July 2005
Member
thanks , i found the answer , thanks a lot for your help
Re: [noob] mixing the result of 2 queries [message #268675 is a reply to message #268660] Wed, 19 September 2007 06:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
i found the answer

So post it.

Regards
Michel
Re: [noob] mixing the result of 2 queries [message #268678 is a reply to message #268405] Wed, 19 September 2007 06:17 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #268685 is a reply to message #268405] Wed, 19 September 2007 06:32 Go to previous messageGo to next message
b_52globemaster
Messages: 51
Registered: July 2005
Member
hi muzahidul islam

thanks man

[Updated on: Wed, 19 September 2007 06:32]

Report message to a moderator

Re: [noob] mixing the result of 2 queries [message #268687 is a reply to message #268683] Wed, 19 September 2007 06:36 Go to previous messageGo to next message
Frank
Messages: 7880
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 #268689 is a reply to message #268405] Wed, 19 September 2007 06:41 Go to previous messageGo to next message
b_52globemaster
Messages: 51
Registered: July 2005
Member
Frank ,

hm , so the parentheses don't have any effect , hm ok
thanks
Re: [noob] mixing the result of 2 queries [message #268834 is a reply to message #268405] Wed, 19 September 2007 21:53 Go to previous messageGo to next message
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 #268836 is a reply to message #268405] Wed, 19 September 2007 21:59 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>At second query there is no disk access as well as no elapsed time for fetch call.
If the 1st query brought all the rows into the SGA, then the 2nd query avoids going out to disk for the data.

What happens if after running the 2nd query, you immediately re-run the 1st query again, again.
Re: [noob] mixing the result of 2 queries [message #268853 is a reply to message #268405] Wed, 19 September 2007 22:41 Go to previous message
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?
Previous Topic: Clinical oracle training
Next Topic: show 2 columns selecting on 1 table.
Goto Forum:
  


Current Time: Sat Dec 03 11:56:09 CST 2016

Total time taken to generate the page: 0.04787 seconds