Not to use tmp tables

by: mmoorePosted on 2008-08-26 at 11:57:24ID: 22318106

I have not taken the time to completely understand your code but in general, each of your temporary tables can be replaced by an in-line view. The structure would look something like

insert into whatever
SELECT *
FROM ab_listing@abcd l
JOIN
( SELECT *
FROM ab_dir_issue@abcd m
JOIN
ab_product@abcd j
ON m.dummy = j.dummy) n
ON l.dummy = n.dummy;
In other, you don't need temporary tables.

by: sdstuberPosted on 2008-08-26 at 12:23:50ID: 22318402

can you post the entire procedure.

everything in the partial code posted above deletes from or writes to a temp table, so it never gets around to really doing anything, unless these temp tables are then going to be used by something else, like a user screen.

by: Medicine1Posted on 2008-08-26 at 12:39:03ID: 22318555

Attached to full proc. I like solution one with inline but not that familiar with. I am new at this - this is someone else's code. There are serveral tmp tables created and not sure how to put together for an inline. It looks like the last tmp table is the final table and could all the data just on in that one table. Thanks in advance.

PROCEDURE-vi1.doc
- 46 KB
Proc2

by: sdstuberPosted on 2008-08-26 at 12:53:38ID: 22318702

give this a try...

note, you're pulling lots of information across a databaselink then using local indexes.
So, it is possible that you'll get better performance with the temp tables.

Something else to consider is gathering statistics on the temp table after you populate each one.

PROCEDURE vis_customers(p_break_market IN VARCHAR2, p_break_year IN VARCHAR2)
IS
BEGIN
    DECLARE
        v_errorcode VARCHAR2(20);
        v_errortext VARCHAR2(50);
    BEGIN
--        -- delete all rows from tmp_expmkt_dir_issue table
 
--        DELETE   USER.tmp_expmkt_dir_issue
--         WHERE   break_market = p_break_market AND break_year = p_break_year;
--        COMMIT;
 
--        -- insert data into tmp_expmkt_dir_issue table
 
--        INSERT INTO
--            USER.tmp_expmkt_dir_issue
--            SELECT   p_break_market,
--                     p_break_year,
--                     dir_num,
--                     dir_issue_num,
--                     source_system_cde
--              FROM   ab_dir_issue@abcd
--             WHERE   TO_CHAR(dir_issue_dte, 'YYYYMMDD') <= TO_CHAR(SYSDATE, 'YYYYMMDD')
--                     AND source_system_cde = 'V';
--        COMMIT;
 
--        -- delete all rows from tmp_expmkt_DIR_ISSUE_MAX table
 
--        DELETE   USER.tmp_expmkt_dir_issue_max
--         WHERE   break_market = p_break_market AND break_year = p_break_year;
 
--        COMMIT;
 
--        -- insert data into tmp_expmkt_DIR_ISSUE_MAX table
 
--        INSERT INTO USER.tmp_expmkt_dir_issue_max
--              SELECT   break_market,
--                       break_year,
--                       dir_num,
--                       MAX(dir_issue_num) dir_issue_num,
--                       'V' source_system_cde
--                FROM   (SELECT   *
--                          FROM   USER.tmp_expmkt_dir_issue
--                         WHERE   break_market = p_break_market AND break_year = p_break_year)
--            GROUP BY   break_market, break_year, dir_num;
--        COMMIT;
 
--        -- delete all rows from tmp_expmkt_dir_cust_id table
 
--        DELETE   USER.tmp_expmkt_dir_cust_id
--         WHERE   break_market = p_break_market AND break_year = p_break_year;
 
--        COMMIT;
 
--        -- insert data inot tmp_expmkt_dir_cust_id table
 
--        INSERT INTO
--            USER.tmp_expmkt_dir_cust_id
--            SELECT                                  /*+ INDEX (ab_listing@ABCD  AB_LISTING_NDX01) */
--                     p_break_market,
--                     p_break_year,
--                     l.cust_id,
--                     l.atn_tele_area_cde area_cde,
--                     l.atn_tele_prfx_num prfx_num,
--                     l.atn_tele_base_num base_num,
--                        TRIM(l.atn_tele_area_cde)
--                     || TRIM(l.atn_tele_prfx_num)
--                     || TRIM(l.atn_tele_base_num)
--                         phone,
--                     'V'
--              FROM   ab_listing@abcd l, tmp_expmkt_phone_list x
--             WHERE       x.break_market = p_break_market
--                     AND x.break_year = p_break_year
--                     AND l.atn_tele_area_cde = x.area_code
--                     AND l.atn_tele_prfx_num = x.prefix
--                     AND l.atn_tele_base_num = x.base_num
--                     AND l.source_system_cde = 'V';
 
--        COMMIT;
 
--        -- delete all rows from tmp_expmkt_dir_cust_id2 table
 
--        DELETE   USER.tmp_expmkt_dir_cust_id2
--         WHERE   break_market = p_break_market AND break_year = p_break_year;
 
--        COMMIT;
 
--        -- insert only distinct rows into tmp_expmkt_dir_cust_id2 table
 
--        INSERT INTO USER.tmp_expmkt_dir_cust_id2
--            SELECT   DISTINCT *
--              FROM   USER.tmp_expmkt_dir_cust_id
--             WHERE   break_market = p_break_market AND break_year = p_break_year;
 
--        COMMIT;
 
--        -- delete all rows from tmp_expmkt_dir_custid3 table
---- concerning current market break
 
--        DELETE   USER.tmp_expmkt_dir_custid3
--         WHERE   break_market = p_break_market AND break_year = p_break_year;
 
--        COMMIT;
 
--        -- insert into tmp_expmkt_dir_custid3 table
---- joins product table to dircustid2 table
 
--        INSERT INTO
--            tmp_expmkt_dir_custid3
--              SELECT                                 /*+ INDEX (ab_product@ABCD ab_product_ndx02) */
--                       /*+ INDEX (tmp_expmkt_dir_cust_id2 pk_tmp_expmkt_dircustid2) */
--                       l.break_market,
--                       l.break_year,
--                       p.cust_id,
--                       SUM(NVL(p.ni_rev_amt, 0)),
--                       p.dir_num,
--                       p.dir_issue_num,
--                       l.area_cde,
--                       l.prfx_num,
--                       l.base_num,
--                       l.phone
--                FROM   tmp_expmkt_dir_cust_id2 l, ab_product@abcd p
--               WHERE       l.break_market = p_break_market
--                       AND l.break_year = p_break_year
--                       AND l.source_system_cde = 'V'
--                       AND p.source_system_cde = l.source_system_cde
--                       AND p.cust_id = l.cust_id
--                       AND(NVL(p.prospect_cde, 'N') = 'A' OR NVL(p.ni_rev_amt, 0) > 0)
--            GROUP BY   l.break_market,
--                       l.break_year,
--                       p.cust_id,
--                       p.dir_num,
--                       p.dir_issue_num,
--                       l.area_cde,
--                       l.prfx_num,
--                       l.base_num,
--                       l.phone;
 
 
--        COMMIT;
 
--        -- delete all rows fom tmp_expmkt_vis_customers table
 
        DELETE   USER.tmp_expmkt_vis_customers
         WHERE   break_market = p_break_market AND break_year = p_break_year;
 
        COMMIT;
 
        -- insert vis customers for current break
 
        INSERT INTO
            tmp_expmkt_vis_customers
              SELECT   c.break_market,
                       c.break_year,
                       c.cust_id,
                       SUM(c.sum_rev_amt),
                       c.area_cde,
                       c.prfx_num,
                       c.base_num,
                       c.phone
                FROM   (  SELECT                     /*+ INDEX (ab_product@ABCD ab_product_ndx02) */
                                   /*+ INDEX (tmp_expmkt_dir_cust_id2 pk_tmp_expmkt_dircustid2) */
                                   l.break_market,
                                   l.break_year,
                                   p.cust_id,
                                   SUM(NVL(p.ni_rev_amt, 0)),
                                   p.dir_num,
                                   p.dir_issue_num,
                                   l.area_cde,
                                   l.prfx_num,
                                   l.base_num,
                                   l.phone
                            FROM   (SELECT   DISTINCT *
                                      FROM   (SELECT /*+ INDEX (ab_listing@ABCD  AB_LISTING_NDX01) */
                                                       p_break_market break_market,
                                                       p_break_year break_year,
                                                       l.cust_id,
                                                       l.atn_tele_area_cde area_cde,
                                                       l.atn_tele_prfx_num prfx_num,
                                                       l.atn_tele_base_num base_num,
                                                          TRIM(l.atn_tele_area_cde)
                                                       || TRIM(l.atn_tele_prfx_num)
                                                       || TRIM(l.atn_tele_base_num)
                                                           phone,
                                                       'V'
                                                FROM   ab_listing@abcd l, tmp_expmkt_phone_list x
                                               WHERE       x.break_market = p_break_market
                                                       AND x.break_year = p_break_year
                                                       AND l.atn_tele_area_cde = x.area_code
                                                       AND l.atn_tele_prfx_num = x.prefix
                                                       AND l.atn_tele_base_num = x.base_num
                                                       AND l.source_system_cde = 'V')
                                     WHERE   break_market = p_break_market
                                             AND break_year = p_break_year) l,
                                   ab_product@abcd p
                           WHERE       l.break_market = p_break_market
                                   AND l.break_year = p_break_year
                                   AND l.source_system_cde = 'V'
                                   AND p.source_system_cde = l.source_system_cde
                                   AND p.cust_id = l.cust_id
                                   AND(NVL(p.prospect_cde, 'N') = 'A' OR NVL(p.ni_rev_amt, 0) > 0)
                        GROUP BY   l.break_market,
                                   l.break_year,
                                   p.cust_id,
                                   p.dir_num,
                                   p.dir_issue_num,
                                   l.area_cde,
                                   l.prfx_num,
                                   l.base_num,
                                   l.phone) c,
                       (  SELECT   break_market,
                                   break_year,
                                   dir_num,
                                   MAX(dir_issue_num) dir_issue_num,
                                   'V' source_system_cde
                            FROM   (SELECT   *
                                      FROM   (SELECT   p_break_market,
                                                       p_break_year,
                                                       dir_num,
                                                       dir_issue_num,
                                                       source_system_cde
                                                FROM   ab_dir_issue@abcd
                                               WHERE   TO_CHAR(dir_issue_dte, 'YYYYMMDD') <=
                                                           TO_CHAR(SYSDATE, 'YYYYMMDD')
                                                       AND source_system_cde = 'V')
                                     WHERE   break_market = p_break_market
                                             AND break_year = p_break_year)
                        GROUP BY   break_market, break_year, dir_num) x
               WHERE       c.break_market = p_break_market
                       AND c.break_year = p_break_year
                       AND c.break_market = x.break_market
                       AND c.break_year = x.break_year
                       AND c.dir_num = x.dir_num
                       AND c.dir_issue_num = x.dir_issue_num
            GROUP BY   c.break_market,
                       c.break_year,
                       c.cust_id,
                       c.area_cde,
                       c.prfx_num,
                       c.base_num,
                       c.phone;
 
        COMMIT;
    EXCEPTION
        WHEN NO_DATA_FOUND
        THEN
            DBMS_OUTPUT.put_line('Error - No data found');
        WHEN OTHERS
        THEN
            -- raise other exceptions and display if found
            v_errorcode := SQLCODE;
            v_errortext := SUBSTR(SQLERRM, 1, 50);
            DBMS_OUTPUT.put_line('Error is ' || v_errorcode || v_errortext);
 
            COMMIT;
    END;
END vis_customers;

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:
101:
102:
103:
104:
105:
106:
107:
108:
109:
110:
111:
112:
113:
114:
115:
116:
117:
118:
119:
120:
121:
122:
123:
124:
125:
126:
127:
128:
129:
130:
131:
132:
133:
134:
135:
136:
137:
138:
139:
140:
141:
142:
143:
144:
145:
146:
147:
148:
149:
150:
151:
152:
153:
154:
155:
156:
157:
158:
159:
160:
161:
162:
163:
164:
165:
166:
167:
168:
169:
170:
171:
172:
173:
174:
175:
176:
177:
178:
179:
180:
181:
182:
183:
184:
185:
186:
187:
188:
189:
190:
191:
192:
193:
194:
195:
196:
197:
198:
199:
200:
201:
202:
203:
204:
205:
206:
207:
208:
209:
210:
211:
212:
213:
214:
215:
216:
217:
218:
219:
220:
221:
222:
223:
224:
225:
226:
227:
228:
229:
230:
231:
232:
233:
234:
235:
236:
237:
238:
239:
240:
241:
242:
243:
244:
245:
246:
247:
248:
249:
250:
251:
252:
253:
254:
255:
256:
257:
258:

Select all Open in new window

by: mmoorePosted on 2008-08-26 at 14:20:18ID: 22319657

"inline views" can be useful but as sdstuber has mentioned, the code pulls data across a database link. There is often a difference between "can this be done" and "should this be done".

Also, consider this code snippet which is part of your code:

-- delete all rows from tmp_expmkt_dir_issue table<o:p></o:p>

<o:p></o:p>

DELETEmkmt_user.TMP_EXPMKT_DIR_ISSUE<o:p></o:p>

WHERE break_market =p_break_market <o:p></o:p>

AND break_year = p_break_year;<o:p></o:p>

COMMIT;<o:p></o:p>

The comment says "ALL" rows, but is it really? What if there are rows that don't match the WHERE criteria?

If you are just doing this as a learning exercise, then try out the inline views approach to see how much code can be removed. If you are talking about a real production solution, then there is much more to consider. If you Google "inline view" you will find lots of good examples including:

http://www.orafaq.com/wiki/Inline_view

<o:p></o:p>

Question

Not to use tmp tables

Subscribe now for full access to Experts Exchange and get

Instant Access to this Solution

PL / SQL

Oracle 10.x

Trusted by hundreds of thousands everyday for fast, accurate and reliable tech support.

See what Experts Exchange can do for you.

Got a question?

We've got the answer.

Need individual assistance?

Our experts are ready to help.

Want to learn from the best?

Read articles from industry experts.

Working on a long term project?

Store your work and research.

What Makes Experts Exchange Unique?

Trusted by the world's most respected brands.

Faithfully serving IT professionals since 1996.

Related Solutions

Free Tech Articles

Cloud Class Webinars

Join the Community

Give a Little. Get a Lot.

Answers

3 Ways to Join

The Experts

The Experts

Testimonials

Testimonials

Testimonials

Business Clients

Business Clients

In the Press

In the Press

In the Press