Re: View hangs up

In article <1996Mar26.210119.4813_at_nosc.mil>, pilarski_at_nosc.mil says...
>
>This is a multi-part message in MIME format.
>
>---------------------------------23861544710296
>Content-Transfer-Encoding: 7bit
>Content-Type: text/plain; charset=us-ascii
>
>Can someone help with this one???
>
>When I select from this view, occassionally
>it hangs up indefinetely. The base tables
>are pretty small and I thought a primary
>key constraint on the two base table might
>be the problem but I dropped them and still
>get hung up.
>
>Any clues to possible DBA system settings
>
>Thanks in advance
>
>Jon Pilarski
>
>---------------------------------23861544710296
>Content-Transfer-Encoding: quoted-printable
>Content-Type: zz-application/zz-winassoc-SQL
>
>DROP VIEW LABOR_SELECT_TRANSMIT_VIEW;
>DROP VIEW LABOR_SELECT_TRANSMIT_VIEW1;
>
>CREATE VIEW LABOR_SELECT_TRANSMIT_VIEW AS
>(SELECT c.USER_NAME, c.TRANSACTION_ID,
> c.LABOR_HOURS, c.TRANSACTION_AMOUNT,
 c.GENERAL_OVERHEAD_AMOUNT=
>,
> c.INDIRECT_OVERHEAD_AMOUNT, c.STANDARD_LABOR_AMOUNT,
> c.JOURNAL_VOUCHER_ID,c.SELECT_FOR_BATCHING =
>
> FROM CIVILIAN_LABOR_ADJUSTMENTS c
> UNION ALL
> SELECT m.USER_NAME, m.TRANSACTION_ID,
> m.LABOR_HOURS, m.TRANSACTION_AMOUNT,
 m.GENERAL_OVERHEAD_AMOUNT=
>,
> m.INDIRECT_OVERHEAD_AMOUNT, m.STANDARD_LABOR_AMOUNT,
> m.JOURNAL_VOUCHER_ID,m.SELECT_FOR_BATCHING =
>
>FROM military_LABOR_ADJUSTMENTS m );
>
>CREATE VIEW LABOR_SELECT_TRANSMIT_VIEW1 AS
>(select JOURNAL_VOUCHER_ID, =
>
>JOB_ORDER_NUMBER, =
>
>REFERENCE_NAME , =
>
>OBJECT_OF_EXPENSE , =
>
>COST_CENTER_WORK_CENTER_CODE , =
>
>FISCAL_YEAR, =
>
>WEEK_NUMBER , =
>
>LABOR_HOURS, =
>
>TRANSACTION_AMOUNT , =
>
>GENERAL_OVERHEAD_AMOUNT , =
>
>INDIRECT_OVERHEAD_AMOUNT, =
>
>STANDARD_LABOR_AMOUNT
>from civilian_labor_adjustments
>union all =
>
>select JOURNAL_VOUCHER_ID, =
>
>JOB_ORDER_NUMBER, =
>
>REFERENCE_NAME , =
>
>OBJECT_OF_EXPENSE , =
>
>COST_CENTER_WORK_CENTER_CODE , =
>
>FISCAL_YEAR, =
>
>WEEK_NUMBER , =
>
>LABOR_HOURS, =
>
>TRANSACTION_AMOUNT , =
>
>GENERAL_OVERHEAD_AMOUNT , =
>
>INDIRECT_OVERHEAD_AMOUNT, =
>
>STANDARD_LABOR_AMOUNT
>from military_labor_adjustments);
>--ORDER BY REFERENCE_NAME;
>
>grant all on LABOR_SELECT_TRANSMIT_VIEW1 to nfade_user;
>grant all on labor_select_transmit_view to nfade_user; =
>

Do an EXPLAIN PLAN on the SELECTs which lock up. Also do EXPLAIN PLANs on SELECTs which do not lock up. Compare them. You should do the EXPLAIN PLANs on the SELECTs which run against the VIEW as well as doing an EXPLAIN PLAN on the SELECT portion of you CREATE VIEW statement (ie. do an EXPLAIN PLAN on you CREATE VIEW statement minus the CREATE VIEW portion, only the SELECT statement part).

Hope this will lead you to a solution. Received on Fri Mar 29 1996 - 00:00:00 CET