Re: My View performance is very slow

From: <siddu_htc_at_yahoo.com>
Date: 1 Sep 2005 22:27:07 -0700
Message-ID: <1125638826.989295.309200@g43g2000cwa.googlegroups.com>

My actual View:

---

The critical column is Updated in the view ,which is not a databse column,
but hardcoded to identify set of data with plan_id = -1. And my queries will be based on this column.

CREATE OR REPLACE FORCE VIEW MSC_ITEM_ATTRIBUTES_V (SOURCE_ROW_ID,PLAN_ID, SR_INSTANCE_ID, ORGANIZATION_ID,  .. .. .. DESTINATION_ROW_ID, ...... UPDATED) AS
SELECT MSI.ROWID,

      MSI.PLAN_ID,
      MSI.SR_INSTANCE_ID,
      MSI.ORGANIZATION_ID,


      ..

      ..

      null,
      null,


      ..

      ..

      'No'
FROM msc_system_items msi,
     msc_item_categories mic
WHERE msi.plan_id = -1
  and msi.sr_instance_id = mic.sr_instance_id

  and msi.inventory_item_id = mic.inventory_item_id   and msi.organization_id = mic.organization_id union all

select MSI.ROWID,
       MSI.PLAN_ID,
       MSI.SR_INSTANCE_ID,
       MSI.ORGANIZATION_ID,
       ..
       ..
       MSIS.ROWID,
       MSIS.SIMULATION_SET_ID,
       'Yes'
FROM MSC_SYSTEM_ITEMS MSI,
     MSC_ITEM_CATEGORIES MIC,
     MSC_ITEM_ATTRIBUTES MSIS

WHERE MSIS.PLAN_ID = MSI.PLAN_ID
AND MSIS.SR_INSTANCE_ID = MSI.SR_INSTANCE_ID AND MSIS.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID AND MSIS.ORGANIZATION_ID = MSI.ORGANIZATION_ID

AND MSI.SR_INSTANCE_ID = MIC.SR_INSTANCE_ID
AND MSI.INVENTORY_ITEM_ID = MIC.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = MIC.ORGANIZATION_ID;

Received on Fri Sep 02 2005 - 00:27:07 CDT