Home » SQL & PL/SQL » SQL & PL/SQL » Query rewrite (oracle 9.2.0.3)
Query rewrite [message #445120] Thu, 25 February 2010 23:31 Go to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member

SELECT   ins_cde
       , ins_nme
       , item_create_dte i_dte
       , b.d_cde
       , q.d_completion completion_dt
	   , COUNT (a.u_reg_nbr) reg_cnt
       , COUNT(a.u_completed_yr) complete_cnt
       , SUM(DECODE(NVL(TO_CHAR(a.u_completed_yr),'NUL'),'NUL',1,0)) noncomplete_cnt
FROM     items a
       , student std
       , qualification q
       , edition b
       , term c
       , order e
       , institution f
WHERE    a.n_id = b.n_id
AND      b.d_cde IN ('PHD', 'PDOC')
AND      q.s_nbr = (SELECT MAX (s_nbr)
                                    FROM   qualification qual1
                                    WHERE  qual1.std_id = std.std_id)
AND      a.m_id = e.m_id
AND      e.osn_id = c.osn_id
AND      c.inst_id = f.inst_id
AND      item_create_dte BETWEEN TO_DATE('01/02/2004','DD/MM/YYYY') AND TO_DATE('31/12/2009 23:59:59','dd/mm/yyyy hh24:mi:ss') 
AND      a.item_id = std.i_id
AND      std.std_id = q.std_id
GROUP BY inst_code
       , ins_nme
       , item_create_dte
       , b.d_cde
       , q.qualification_completion       
UNION
SELECT   inst_code
       , ins_nme
       , item_create_dte i_dte
       , b.d_cde
       , q.d_completion completion_dt
       , COUNT (a.u_reg_nbr) reg_cnt
       , COUNT(a.u_completed_yr) complete_cnt
       , SUM(DECODE(NVL(TO_CHAR(a.u_completed_yr),'NUL'),'NUL',1,0)) noncomplete_cnt
FROM     items a
       , student std
       , qualifiaction q
       , edition b
       , institution c
WHERE    a.n_id = b.n_id
AND      b.d_cde IN ('PHD', 'PDOC')
AND      q.s_nbr = (SELECT MAX (s_nbr)
                                    FROM   qualification qual1
                                    WHERE  qual1.std_id = std.std_id)
AND      a.inst_id = c.inst_id
AND      item_create_dte BETWEEN TO_DATE('01/02/2004','DD/MM/YYYY') AND TO_DATE('31/12/2009 23:59:59','dd/mm/yyyy hh24:mi:ss') 
AND      a.item_id = std.i_id
AND      std.std_id = q.std_id
GROUP BY s_cde
       , ins_nme
       , item_create_dte
       , b.d_cde
       , q.d_completion;
      
 

Can the sql be written in a better way?
Re: Query rewrite [message #445122 is a reply to message #445120] Thu, 25 February 2010 23:34 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No one can unless you provide:
- what's the query is intended to return
- number of rows in each table
- ... (cf http://www.orafaq.com/forum/mv/msg/84315/433888/102589/#msg_433888)

Regards
Michel
Re: Query rewrite [message #445139 is a reply to message #445122] Fri, 26 February 2010 03:25 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
can not we modify the sql into a single one using OR a.inst_id = c.inst_id in the first sql.
The only differenec in the 2nd sql is that it has less tables usedthen the 1st one.

and also when a.inst_id is null then the value was fetched from the 2nd sql..
thanks
Re: Query rewrite [message #445140 is a reply to message #445139] Fri, 26 February 2010 03:36 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First, are you sure you need a UNION and not a UNION ALL?

Regards
Michel
Re: Query rewrite [message #445141 is a reply to message #445120] Fri, 26 February 2010 03:51 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
Something else that'd really help is if you copied and pasted the real code. That's got typos in it.
eg.
query1:
       , qualification q
query2:
       , qualifiaction q

There are others as well.
Re: Query rewrite [message #445142 is a reply to message #445120] Fri, 26 February 2010 03:53 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
And don't use the same alias for different tables, it's really confusing.
Re: Query rewrite [message #445183 is a reply to message #445140] Fri, 26 February 2010 05:58 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
UNION not required.Can be replaced by UNION ALL. Other way to modify the sql?
Re: Query rewrite [message #445187 is a reply to message #445120] Fri, 26 February 2010 06:08 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
Possibly. If you give use the correct sql (I.e. one that'll actually compile) we might even take a look and make some suggestions.
Re: Query rewrite [message #445193 is a reply to message #445187] Fri, 26 February 2010 06:19 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
but i can not post the real sql(as i am not permitted to do ) and to make test cases for the tables are difficult.Hope you understand
Re: Query rewrite [message #445194 is a reply to message #445193] Fri, 26 February 2010 06:23 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
did you read what michel said....
http://www.orafaq.com/forum/mv/msg/155486/445122/136607/#msg_445122

sriram
Re: Query rewrite [message #445195 is a reply to message #445193] Fri, 26 February 2010 06:31 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member

SELECT   ins_cde
       , ins_nme
       , item_create_dte i_dte
       , b.d_cde
       , q.d_completion completion_dt
	   , COUNT (a.u_reg_nbr) reg_cnt
       , COUNT(a.u_completed_yr) complete_cnt
       , SUM(DECODE(NVL(TO_CHAR(a.u_completed_yr),'NUL'),'NUL',1,0)) noncomplete_cnt
FROM     items a
       , student std
       , qualification q
       , edition b
       , term c
       , order e
       , institution f
WHERE    a.n_id = b.n_id
AND      b.d_cde IN ('PHD', 'PDOC')
AND      q.s_nbr = (SELECT MAX (s_nbr)
                                    FROM   qualification qual1
                                    WHERE  qual1.std_id = std.std_id)
AND      a.m_id = e.m_id
AND      e.osn_id = c.osn_id
AND      c.inst_id = f.inst_id
AND      item_create_dte BETWEEN TO_DATE('01/02/2004','DD/MM/YYYY') AND TO_DATE('31/12/2009 23:59:59','dd/mm/yyyy hh24:mi:ss') 
AND      a.item_id = std.i_id
AND      std.std_id = q.std_id
GROUP BY inst_code
       , ins_nme
       , item_create_dte
       , b.d_cde
       , q.qualification_completion       
UNION
SELECT   inst_code
       , ins_nme
       , item_create_dte i_dte
       , b.d_cde
       , q.d_completion completion_dt
       , COUNT (a.u_reg_nbr) reg_cnt
       , COUNT(a.u_completed_yr) complete_cnt
       , SUM(DECODE(NVL(TO_CHAR(a.u_completed_yr),'NUL'),'NUL',1,0)) noncomplete_cnt
FROM     items a
       , student std
       , qualification q
       , edition b
       , institution c
WHERE    a.n_id = b.n_id
AND      b.d_cde IN ('PHD', 'PDOC')
AND      q.s_nbr = (SELECT MAX (s_nbr)
                                    FROM   qualification qual1
                                    WHERE  qual1.std_id = std.std_id)
AND      a.inst_id = c.inst_id
AND      item_create_dte BETWEEN TO_DATE('01/02/2004','DD/MM/YYYY') AND TO_DATE('31/12/2009 23:59:59','dd/mm/yyyy hh24:mi:ss') 
AND      a.item_id = std.i_id
AND      std.std_id = q.std_id
GROUP BY s_cde
       , ins_nme
       , item_create_dte
       , b.d_cde
       , q.d_completion;
      
 


Citeria: a.inst_id can be null. second sql is if a.inst_id is not null
Re: Query rewrite [message #445199 is a reply to message #445193] Fri, 26 February 2010 07:15 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
prachij593 wrote on Fri, 26 February 2010 12:19
but i can not post the real sql(as i am not permitted to do ) and to make test cases for the tables are difficult.Hope you understand


And we can't rewrite code that doesn't even compile. Hope you understand.
And besides, performance tuning is a very specific task, if you don't paste the real query then there is no garuantee anything we suggest won't just make things worse.
And do really think anyone is going to steal a single select statement in isolation? What on earth would they do with it?

You're new version is still wrong. The column list for the group by doesn't match the select.
Also please don't use the same alias for different tables.
Make sure all column references have a table alias so we can tell where they are coming from.
And answer Michels questions while you are at it.
Re: Query rewrite [message #445209 is a reply to message #445199] Fri, 26 February 2010 07:38 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
And do really think anyone is going to steal a single select statement in isolation?


If it was worth stealing, then it wouldn't be here needing our help to improve!
Previous Topic: Analytic Functions and Ranking/Ordering
Next Topic: Result If A Subquery Returns No Data
Goto Forum:
  


Current Time: Tue Sep 27 11:07:00 CDT 2016

Total time taken to generate the page: 0.29006 seconds