Home » SQL & PL/SQL » SQL & PL/SQL » Optimize a 'SELECT'
| Optimize a 'SELECT' [message #11182] |
Wed, 10 March 2004 12:26  |
Dan
Messages: 61
Registered: February 2000
|
Member |
|
|
Hi,
I would like to optimize the following script but i'm confuse!!!I'm always back to the same solution, do you something to suggest?
Select To_char(nvl(w.startdate,w.enddate),'YYYY')||'D'||to_char(nvl(w.startdate,w.enddate),'DDD') Time,
substr(w.account, 17, 4) DimOrgAdm, nvl(l.craft,'_') Craft, nvl(w.location,'_') Location, nvl(l.labor, '_') Employee
from work w, labor wp, trans l,
(SELECT wonum, sum(actlabhrs) acthrs from workorder where wopriority = '6' group by wonum, (nvl(startdate, enddate)), account) urghre
where w.num = wp.num AND
w.num = l.num
--GROUP BY (nvl(w.schedstart,w.reportdate)), w.glaccount, l.craft, w.location, l.laborcode
order by location
Tia
|
|
|
|
| Re: Optimize a 'SELECT' [message #11184 is a reply to message #11182] |
Wed, 10 March 2004 13:34  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
You have four "tables" in the FROM clause (three tables and one inline view), but you are only joining the 3 tables - not the inline view?
How is the inline view supposed to integrate with the other tables and the SELECT list?
|
|
|
|
| Re: Optimize a 'SELECT' [message #11199 is a reply to message #11184] |
Fri, 12 March 2004 09:43 |
Dan
Messages: 61
Registered: February 2000
|
Member |
|
|
Hi Todd,
Sorry for the mistake in my script! In fact, the sql that i have to write it's an sql to calculate a sum for a variable 'X' with the selection 'A' and the same variable 'X' for the selection 'B'. The response could lookslike tis:
Order Sum A Sum B
99999 9.9 9.9
Do you have an idea how can i write it?
Tia,
Dan
|
|
|
|
|
|
| Re: Optimize a 'SELECT' [message #11201 is a reply to message #11200] |
Fri, 12 March 2004 10:43 |
Dan
Messages: 61
Registered: February 2000
|
Member |
|
|
Hi Todd,
I would like to put all on the same line for the same record.
The result should be like this:
"2003D031","2100","MEMW","BTH312FA02","0490","13",""
The script is:
Select '"'||To_char(nvl(w.schedstart,w.reportdate),'YYYY')||'D'||to_char(nvl(w.schedstart,w.reportdate),'DDD')||'","'
||substr(w.glaccount, 17, 4)||'","'
||nvl(l.craft,'_')||'","'
||nvl(w.location,'_')||'","'
||nvl(l.laborcode, '_')||'","'
||To_CHAR(sum(w.actlabhrs))||'",""' priority5
from workorder w, wplabor wp, labtrans l
where w.wonum = wp.wonum AND
wp.wonum = l.wonum AND WOPRIORITY <> '6'
GROUP BY nvl(w.schedstart,w.reportdate),
w.glaccount,
l.craft,
w.location,
l.laborcode,
w.wopriority
UNION ALL
Select '"'||To_char(nvl(w.schedstart,w.reportdate),'YYYY')||'D'||to_char(nvl(w.schedstart,w.reportdate),'DDD')||'","'
||substr(w.glaccount, 17, 4)||'","'
||nvl(l.craft,'_')||'","'
||nvl(w.location,'_')||'","'
||nvl(l.laborcode, '_')||'","","'
||To_CHAR(sum(w.actlabhrs))||'"' priority5
from workorder w, wplabor wp, labtrans l
where w.wonum = wp.wonum AND
wp.wonum = l.wonum AND WOPRIORITY = '6'
GROUP BY nvl(w.schedstart,w.reportdate),
w.glaccount,
l.craft,
w.location,
l.laborcode,
w.wopriority
Tia,
Dan
|
|
|
|
| Re: Optimize a 'SELECT' [message #11204 is a reply to message #11201] |
Sat, 13 March 2004 08:43 |
 |
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
If you don't already have indexes on the wonum columns used in the join conditions, then create the indexes. If you have not recently analyzed your tables, indexes, and indexed columns, then analyze them, in order to gather statistics, so that the cost-based optimizer can choose the best execution plan.
I gather that you want one row for priorty 6 and another row for all the others. Instead of using union all, you can use decode on the wopriority column in both the select clause and the group by clause and eliminate the =6 or <>6 from the where clause.
Please see the demonstration below.
scott@ORA92> -- create indexes:
scott@ORA92> CREATE INDEX workorder_wonum_idx ON workorder (wonum)
2 /
Index created.
scott@ORA92> CREATE INDEX wplabor_wonum_idx ON wplabor (wonum)
2 /
Index created.
scott@ORA92> CREATE INDEX labtrans_wonum_idx ON labtrans (wonum)
2 /
Index created.
scott@ORA92> -- analyze tables, indexes, and indexed colunns:
scott@ORA92> ANALYZE TABLE workorder
2 COMPUTE STATISTICS
3 FOR TABLE
4 FOR ALL INDEXES
5 FOR ALL INDEXED COLUMNS
6 /
Table analyzed.
scott@ORA92> ANALYZE TABLE wplabor
2 COMPUTE STATISTICS
3 FOR TABLE
4 FOR ALL INDEXES
5 FOR ALL INDEXED COLUMNS
6 /
Table analyzed.
scott@ORA92> ANALYZE TABLE labtrans
2 COMPUTE STATISTICS
3 FOR TABLE
4 FOR ALL INDEXES
5 FOR ALL INDEXED COLUMNS
6 /
Table analyzed.
scott@ORA92> -- run query:
scott@ORA92> SELECT DECODE (wopriority, 6, 'PRIORITY 6', 'OTHER THAN 6') AS priority,
2 '"' || TO_CHAR (NVL (w.schedstart, w.reportdate), 'YYYY')
3 || 'D' || TO_CHAR (NVL (w.schedstart, w.reportdate), 'DDD')
4 || '","' || SUBSTR (w.glaccount, 17, 4)
5 || '","' || NVL (l.craft, '_')
6 || '","' || NVL (w.location, '_')
7 || '","' || NVL (l.laborcode, '_')
8 || '","' || To_CHAR (SUM (w.actlabhrs)) || '"' AS data
9 FROM workorder w, wplabor wp, labtrans l
10 WHERE w.wonum = wp.wonum
11 AND wp.wonum = l.wonum
12 GROUP BY NVL (w.schedstart, w.reportdate),
13 w.glaccount, l.craft, w.location, l.laborcode,
14 DECODE (wopriority, 6, 'PRIORITY 6', 'OTHER THAN 6')
15 /
PRIORITY DATA
------------ ---------------------------------------------------------------------------------------
PRIORITY 6 "2003D031","2100","MEMW","BTH312FA02","0490","13"
OTHER THAN 6 "2003D031","2100","MEMW","BTH312FA02","0490","5"
|
|
|
|
| Re: Optimize a 'SELECT' [message #11213 is a reply to message #11204] |
Sun, 14 March 2004 08:32 |
Dan
Messages: 61
Registered: February 2000
|
Member |
|
|
Thanks Barbara for your help, but for the answer i need to have the 'Priority 6' and 'Other than 6' result on the same line (same record).
Tia
Dan
|
|
|
|
| Re: Optimize a 'SELECT' [message #11216 is a reply to message #11213] |
Sun, 14 March 2004 09:33 |
|
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
You may have to expand your window to the full screen to view this without wrapping the last line. Is this what you want?
scott@ORA92> SET LINESIZE 150
scott@ORA92> COLUMN "Prioority 6" FORMAT A50
scott@ORA92> COLUMN "Other than 6" FORMAT A50
scott@ORA92> SELECT '"' || TO_CHAR (NVL (w.schedstart, w.reportdate), 'YYYY')
2 || 'D' || TO_CHAR (NVL (w.schedstart, w.reportdate), 'DDD')
3 || '","' || SUBSTR (w.glaccount, 17, 4)
4 || '","' || NVL (l.craft, '_')
5 || '","' || NVL (w.location, '_')
6 || '","' || NVL (l.laborcode, '_')
7 || '","' || To_CHAR (SUM (DECODE (wopriority, 6, w.actlabhrs, 0)
8 )) || '"' AS "Priority 6",
9 '"' || TO_CHAR (NVL (w.schedstart, w.reportdate), 'YYYY')
10 || 'D' || TO_CHAR (NVL (w.schedstart, w.reportdate), 'DDD')
11 || '","' || SUBSTR (w.glaccount, 17, 4)
12 || '","' || NVL (l.craft, '_')
13 || '","' || NVL (w.location, '_')
14 || '","' || NVL (l.laborcode, '_')
15 || '","' || To_CHAR (SUM (DECODE (wopriority, 6, 0, w.actlabhrs)
16 )) || '"' AS "Other than 6"
17 FROM workorder w, wplabor wp, labtrans l
18 WHERE w.wonum = wp.wonum
19 AND wp.wonum = l.wonum
20 GROUP BY NVL (w.schedstart, w.reportdate),
21 w.glaccount, l.craft, w.location, l.laborcode
22 /
Priority 6 Other than 6
--------------------------------------------------------------------------------------- --------------------------------------------------
"2003D031","2100","MEMW","BTH312FA02","0490","13" "2003D031","2100","MEMW","BTH312FA02","0490","5"
scott@ORA92>
|
|
|
|
| Re: Optimize a 'SELECT' [message #11217 is a reply to message #11216] |
Sun, 14 March 2004 10:07 |
Dan
Messages: 61
Registered: February 2000
|
Member |
|
|
Thanks all Barbara,
You give me a good solution, the result that i need is :
"2003D031","2100","MEMW","BTH312FA02","0490","13","5"
In fact, i need the priority 6 and other than 6 in the same record!
Regards,
Dan
|
|
|
|
| Re: Optimize a 'SELECT' [message #11218 is a reply to message #11217] |
Sun, 14 March 2004 10:19 |
|
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
How about this?:
scott@ORA92> SET HEADING OFF
scott@ORA92> SELECT '"' || TO_CHAR (NVL (w.schedstart, w.reportdate), 'YYYY')
2 || 'D' || TO_CHAR (NVL (w.schedstart, w.reportdate), 'DDD')
3 || '","' || SUBSTR (w.glaccount, 17, 4)
4 || '","' || NVL (l.craft, '_')
5 || '","' || NVL (w.location, '_')
6 || '","' || NVL (l.laborcode, '_')
7 || '","' || To_CHAR (SUM (DECODE (wopriority, 6, w.actlabhrs, 0)))
8 || '","' || To_CHAR (SUM (DECODE (wopriority, 6, 0, w.actlabhrs)
9 )) || '"'
10 FROM workorder w, wplabor wp, labtrans l
11 WHERE w.wonum = wp.wonum
12 AND wp.wonum = l.wonum
13 GROUP BY NVL (w.schedstart, w.reportdate),
14 w.glaccount, l.craft, w.location, l.laborcode
15 /
"2003D031","2100","MEMW","BTH312FA02","0490","13","5"
scott@ORA92>
|
|
|
|
|
|
Goto Forum:
Current Time: Tue Apr 23 05:48:27 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
