Unable to Select from the View

[Shreenath]

Hi,

I created the below VIEW. It is bit complicated and there are 19 tables involved. As far as i debugged the select(the code is below) i see all the joins between tables intact. But i am not able to select from this VIEW at all. I thought there might be some performance issue so i tried creating a SNAPSHOT instead of a VIEW. But SNAPSHOT creation goes into a HANG state. I doubt there is a missing join.
Please help me in suggesting how to debug this SELECT. Even a bit of idea is o.k as i have struck the wall.
The VIEW code is: (Please note i have not given the list of selected columns as the message in this box cannot contain more than 10000 chars. And in the select colum list i have 8 DB function calls)

CREATE OR REPLACE VIEW bsr_code1.v_bus_denormalised_trades (
system_date,
slt_slot_number,
slt_reporting_date,
slt_full_name,
src_source_id,
src_short_name,
src_full_name,
src_reporting_date_offset,
ctm_remote_city_code,
ctm_city_code,
ctm_cres_city_code,
ctm_full_name,
ctm_db_entity_branch_code,
ctm_db_entity_ccdb_id1,
ctm_trading_entity_ccdb_id,
wsc_internet_no,
wsc_full_name,
wsc_party_type,
wsc_short_name,
wsc_location,
wsc_city,
wsc_manual_party_id,
wsc_manual_office_id,
wsc_manual_internal_flag,
wsc_auto_party_id,
wsc_auto_office_id,
wsc_auto_internal_flag,
pty_db_party_id,
pty_db_short_code,
pty_db_party_tit le,
pty_db_location_id,
pty_db_grundsatz_type,
pty_db_internal_flag,
ofc_db_office_id,
ofc_db_title,
ofc_db_reporting_currency,
ofc_db_city,
ofc_db_lec_cost_centre,
ofc_db_lec_include_flag,
pty_cpty_party_id,
pty_cpty_party_short_code,
pty_cpty_party_title,
pty_cpty_party_location_id,
pty_cpty_grundsatz_type,
pty_cpty_party_internal_flag,
ofc_cpty_office_id,
ofc_cpty_office_title,
ofc_cpty_office_city_code,
ofc_cpty_lec_cost_centre,
ofc_cpty_lec_include_flag,
area_sub_division,
area_business_group,
area_code,
area_description,
area_base_currency_code,
ifx_name,
prfo_portfolio,
prfo_portfolio_description,
grp_business_code,
cur_currency_code,
trd_slt_number,
trd_source,
trd_db_party_id,
trd_db_office_id,
trd_trade_id,
trd_deal_number,
trd_counterparty,
trd_city,
trd_area,
trd_portfolio,
trd_trade_date,
trd_maturity_date,
trd_buy_currency_code,
trd_buy_amount,
trd_sell_currency_code,
trd_sell_amount,
trd_mtm_currency_code,
trd_mtm_amount,
trd_product_type,
trd_transaction_type,
trd_forwards_flag,
trd_status,
mark_to_market_from_currency,
mark_to_market_fx_rate,
mark_to_market_buy_fx_rate,
mark_to_market_sell_fx_rate,
wgt_weighting,
wgt_maturity_premium,
wgt_grundsatz_type,
wgt_grundsatz_zone,
loc_location_id )
AS
SELECT

/* SELECT COLUMN LIST HERE IN ACTUAL SCRIPT */

FROM
BSR_Slots Slt,
BSR_Sources Src,
BSR_Currencies Cur,
BSR_City_Mappings CtyMp,
BSR_Weightings Wgt,
BSR_Locations loc,
BSR_Business_Group BusGrp,
BSR_Parties Prty1,
BSR_Offices Offc1,
BSR_Parties CptyLegPrty,
BSR_Offices CptyLegPrtyOffc,
BSR_Business_Area BusArea,
BSR_Business_Portfolio BusPrfo,
BSR_FX_Rates MTMFX,
BSR_FX_Rates BuyFX,
BSR_FX_Rates SellFX,
BSR_WST_Counterparties Cpty,
BSR_Trades Trd
WHERE
/* Slots - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - */
Trd.slot_number = Slt.slot_number
AND Trd.source = src.source_id
/* City Mappings - - - - - - - - - - - - - - - - - - - - - - - - - - - - */
AND Trd.source = CtyMp.source_id
AND Trd.source_db_office = CtyMp.city_code
/* Business Group - - - - - - - - - - - - - - - - - - - - - - - - */
AND Trd.source = BusGrp.SOURCE
AND CtyMp.source_code = BusGrp.sub_division
/* Business Area Mappings - - - - - - - - - - - - - - - - - - - - - - - - */
AND Trd.source = BusArea.SOURCE
AND BusGrp.sub_division = BusArea.sub_division
AND BusGrp.business_group = BusArea.business_group
AND Trd.area = BusArea.business_area
/* Business Portfolio Mappings - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - */
AND Trd.source = BusPrfo.SOURCE
AND BusGrp.sub_division = BusPrfo.sub_division
AND BusGrp.business_group = BusPrfo.business_group
AND Trd.area = BusPrfo.business_area
AND Trd.portfolio = BusPrfo.portfolio
/* DB Party / Office - - - - - - - - - - - - - - - - - - - - - - - - - - */
AND Prty1.party_id = Offc1.party_id
AND Trd.deutschebank_id = Prty1.party_id
AND TRd.deutschebank_id = Offc1.party_id
AND Trd.deutschebank_office_id = Offc1.office_id
/* Wst Counterparties (Cpty)- - - - - - - - - - - - - - - - - - - - - - - */
AND Trd.source = Cpty.source
AND Trd.source_counterparty = Cpty.internet_no
AND CtyMp.remote_city_code = Cpty.LOCATION
/* Cpty Party / Office - - - - - - - - - - - - - - - - - - - - - - - - - */
AND NVL(Cpty.MANUAL_PARTY_ID,Cpty.auto_party_id) = CptyLegPrty.party_id(+)
AND NVL(Cpty.MANUAL_PARTY_ID,Cpty.auto_party_id) = CptyLegPrtyOffc.office_id(+)
AND NVL(Cpty.MANUAL_OFFICE_ID,Cpty.AUTO_OFFICE_ID) = CptyLegPrtyOffc.office_id (+)
/* MTM Rate - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - */
AND MTMFX.reporting_currency = Cur.currency_code
AND MTMFX.from_currency = Trd.mtm_currency_code
AND MTMFX.from_date = Slt.reporting_date
/* Buy Rate - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - */
AND BuyFX.reporting_currency = Cur.currency_code
AND BuyFX.from_currency = Trd.buy_currency_code
AND BuyFX.from_date = Slt.reporting_date
/* Sell Rate - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - */
AND SellFX.reporting_currency = Cur.currency_code
AND SellFX.from_currency = Trd.sell_currency_code
AND SellFX.from_date = Slt.reporting_date
/* Location - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - */
AND loc.location_id = NVL(cptyLegPrty.location_id,NVL(Cpty.manual_location_id,NVL(Cpty.auto_location_id,-1)))
/* Weightings - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - */
AND Wgt.calc_id = 3
AND Wgt.grundsatz_type = NVL(CptyLegPrty.grundsatz_type,NVL(Cpty.manual_grundsatz_type,Cpty.auto_grundsatz_type))
AND Wgt.grundsatz_zone = loc.grundsatz_zone
/

[stecal]

The view was created? You can do a desc view_name? You are trying to select from the view as the user who created it (bsr_code1)?

[Shreenath]

Thanks
1. Yes the view is created
2. I am able to DESC the VIEW
3. The user is bsr_code1

[stecal]

Tried doing an explain plan for the select statement from the view?

[Shreenath]

Thanks Steven

Yes , did the Explain Plan too. Its going for FULL TABLE SCAN for 2 tables rest has indexes and are getting used.
I am bit confused about this problem, i.e., i am able to create this VIEW but when i try to create a SNAPSHOT it hangs. i kill the process every time. This led me to doubt that there might be a missing join or mis-join.

[gandolf989]

Are you getting a cartesean product? Maybe the view will return data but just takes too long. Post your explain plan and maybe someone will have an idea. You might also run it throgh the Sql Analyze tuning pack. It might give you some better answers.