asked on

How can I make this union select view work and go faster?

Hi

I'm out of my league here, but I've run into problems using this attached view in Oracle 10.2.0.4. Before creating the view I built a master table from the union of 4 select statements/tables. It worked fine and fast, but I had sync problems updating the 5 tables so I opted for a single master view based on the 4 tables.

At first the view would not work and I encountered the following error whenever using it:
ORA-00600: internal error code, arguments: [qctopn1], [],[],[]

I found a way to avoid the error by adding the /*_ no_merge */ hint, but even though the view works performance is lousy. When used here:

(select v.ee_indv_id,
v.org_org_cd,
v.ee_mc_usualfullname_noinit full_name,
rtrim(substr(v.ebu_phonenumber,1,3) || '-' || substr(v.ebu_phonenumber,4,8),'-') phone_number,
rtrim(substr(v.ebu_cellnumber,1,3) || '-' || substr(v.ebu_cellnumber,4,8),'-') cell_number,
v.edir_nds_name client_id,
v.job_mc_name title,
v.divisionname_pub division,
v.departmentname_pub department,
v.branchname_pub branch,
v.sectionname_pub section,
v.location_name location,
rtrim(substr(v.bu_faxnumber,1,3) || '-' || substr(v.bu_faxnumber,4,8),'-') department_fax,
v.ebu_faxnumber personal_fax
from employee_contact_v v
where ( :b is null or ( :b is not null and regexp_like(v.ee_mc_usualfrname, :b, 'i') or regexp_like(v.ee_mc_frname, :b, 'i') ) )
and ( :b is null or ( :b is not null and regexp_like(v.edir_nds_name, :b, 'i') ) )
and ( :b is null or ( :b is not null and regexp_like(v.job_mc_name, :b, 'i') ) )
and ( :b is null or ( :b is not null and v.divisioncode = :b ) )
and ( :b is null or ( :b is not null and v.departmentcode = :b ) )
and ( :b is null or ( :b is not null and v.branchcode = :b ) )
and ( :b is null or ( :b is not null and v.sectioncode = :b ) )
and ( :b is null or ( :b is not null and v.site_id = :b ) )
and ( :b is null or ( :b is not null and v.short_facility_id = :b ) )
and ( :b is null or ( :b is not null and v.short_floor_id = :b ) )
and ( :b is null or ( :b is not null and regexp_like(v.ee_mc_srname, :b, 'i') ) )
and ( :b is null or ( :b is not null and regexp_like(v.ebu_phonenumber, :b, 'i') ) )
order by v.ee_mc_srname, nvl(v.ee_mc_usualfrname,v.ee_mc_frname));

The employee_contact_v view used here is a filtered view another view which uses the master_city_location_v view. So it this code in conjunction with the attached view that I need to optimize. I have also tried creating the view as a materialized view, but there is no improvement in performance.

Suggestions?

Thanks and take care,
Shayne

CREATE OR REPLACE VIEW MASTER_CITY_LOCATION_V AS (
  SELECT /*+ no_merge */ DISTINCT 
         MCS.SITE_ID AS LOCATION_ID,
         DECODE(MCS.SITE_NAME, '', '', MCS.SITE_NAME) AS LOCATION_NAME,          
         DECODE(MCS.SITE_NAME, '', '', MCS.SITE_NAME) AS LOCATION_NAME_SHORT,
         NULL AS SHORT_ROOM_ID,
         NULL AS ROOM_NAME,
         NULL AS ROOM_NUMBER,
         NULL AS ROOM_ADDR,
         NULL AS ROOM_LOC,
         NULL AS ROOM_TYPE,
         NULL AS ROOM_FUNCTION,
         NULL AS ROOM_CAPACITY,
         NULL AS BOOK_THROUGH_GROUPWISE,
         NULL AS NETWORK_CONNECTION,
         NULL AS TELEPHONE_AVAILABLE,
         NULL AS TELEPHONE_NUMBER,
         NULL AS TELEPHONE_LINE_ONLY,
         NULL AS INSTALLED_PROJECTOR,
         NULL AS ROOM_GIS_ID,
         NULL AS ROOM_SDXP_ID,
         NULL AS ROOM_SPL_ID,
         NULL AS SHORT_FLOOR_ID,
         NULL AS FLOOR_NAME,
         NULL AS FLOOR_TYPE,
         NULL AS FLOOR_LOC,
         NULL AS FLOOR_GIS_ID,
         NULL AS FLOOR_SDXP_ID,
         NULL AS FLOOR_SPL_ID,
         NULL AS SHORT_FACILITY_ID,
         NULL AS FACILITY_NAME,
         NULL AS FACILITY_TYPE,
         NULL AS FACILITY_ADDR,
         NULL AS ALTERNATE_FACILITY_ADDR,
         NULL AS FACILITY_LOC,
         NULL AS FACILITY_GIS_ID,
         NULL AS FACILITY_SDXP_ID,
         NULL AS FACILITY_SPL_ID,
         MCS.SITE_ID AS SITE_ID,
         MCS.SITE_NAME,
         MCS.SITE_TYPE,
         MCS.SITE_ADDR,
         MCS.ALTERNATE_SITE_ADDR,
         MCS.GIS_ID AS SITE_GIS_ID,
         MCS.SDXP_ID AS SITE_SDXP_ID,
         MCS.SPL_ID AS SITE_SPL_ID
    FROM MASTER_CITY_SITE MCS
UNION
  SELECT /*+ no_merge */ DISTINCT
         MCF.FACILITY_ID AS LOCATION_ID,  
         DECODE(MCF.FACILITY_NAME, '', '', MCF.FACILITY_NAME || ', ') || 
            DECODE(MCS.SITE_NAME, '', '', MCS.SITE_NAME) AS LOCATION_NAME,          
         DECODE(MCF.FACILITY_NAME, '', '', MCF.FACILITY_NAME || ', ') || 
            DECODE(MCS.SITE_NAME, '', '', MCS.SITE_NAME) AS LOCATION_NAME_SHORT,          
         NULL AS SHORT_ROOM_ID,
         NULL AS ROOM_NAME,
         NULL AS ROOM_NUMBER,
         NULL AS ROOM_ADDR,
         NULL AS ROOM_LOC,
         NULL AS ROOM_TYPE,
         NULL AS ROOM_FUNCTION,
         NULL AS ROOM_CAPACITY,
         NULL AS BOOK_THROUGH_GROUPWISE,
         NULL AS NETWORK_CONNECTION,
         NULL AS TELEPHONE_AVAILABLE,
         NULL AS TELEPHONE_NUMBER,
         NULL AS TELEPHONE_LINE_ONLY,
         NULL AS INSTALLED_PROJECTOR,
         NULL AS ROOM_GIS_ID,
         NULL AS ROOM_SDXP_ID,
         NULL AS ROOM_SPL_ID,
         NULL AS SHORT_FLOOR_ID,
         NULL AS FLOOR_NAME,
         NULL AS FLOOR_TYPE,
         NULL AS FLOOR_LOC,
         NULL AS FLOOR_GIS_ID,
         NULL AS FLOOR_SDXP_ID,
         NULL AS FLOOR_SPL_ID,
         MCF.SHORT_FACILITY_ID,
         MCF.FACILITY_NAME,
         MCF.FACILITY_TYPE,
         MCF.FACILITY_ADDR,
         MCF.ALTERNATE_FACILITY_ADDR,
         MCF.FACILITY_LOC,
         MCF.GIS_ID AS FACILITY_GIS_ID,
         MCF.SDXP_ID AS FACILITY_SDXP_ID,
         MCF.SPL_ID AS FACILITY_SPL_ID,
         MCS.SITE_ID AS SITE_ID,
         MCS.SITE_NAME,
         MCS.SITE_TYPE,
         MCS.SITE_ADDR,
         MCS.ALTERNATE_SITE_ADDR,
         MCS.GIS_ID AS SITE_GIS_ID,
         MCS.SDXP_ID AS SITE_SDXP_ID,
         MCS.SPL_ID AS SITE_SPL_ID
    FROM MASTER_CITY_SITE MCS INNER JOIN MASTER_CITY_FACILITY MCF ON MCS.SITE_ID = MCF.SITE_ID
UNION
  SELECT /*+ no_merge */ DISTINCT
         MCFL.FLOOR_ID AS LOCATION_ID, 
         DECODE(MCFL.FLOOR_NAME, '', '', MCFL.FLOOR_NAME || ', ') || 
            DECODE(MCF.FACILITY_NAME, '', '', MCF.FACILITY_NAME || ', ') || 
            DECODE(MCS.SITE_NAME, '', '', MCS.SITE_NAME) AS LOCATION_NAME,  
         DECODE(MCFL.FLOOR_NAME, '', '', MCFL.FLOOR_NAME || ', ') || 
            DECODE(MCF.FACILITY_NAME, '', '', MCF.FACILITY_NAME || ', ') || 
            DECODE(MCS.SITE_NAME, '', '', MCS.SITE_NAME) AS LOCATION_NAME_SHORT,  
         NULL AS SHORT_ROOM_ID,
         NULL AS ROOM_NAME,
         NULL AS ROOM_NUMBER,
         NULL AS ROOM_ADDR,
         NULL AS ROOM_LOC,
         NULL AS ROOM_TYPE,
         NULL AS ROOM_FUNCTION,
         NULL AS ROOM_CAPACITY,
         NULL AS BOOK_THROUGH_GROUPWISE,
         NULL AS NETWORK_CONNECTION,
         NULL AS TELEPHONE_AVAILABLE,
         NULL AS TELEPHONE_NUMBER,
         NULL AS TELEPHONE_LINE_ONLY,
         NULL AS INSTALLED_PROJECTOR,
         NULL AS ROOM_GIS_ID,
         NULL AS ROOM_SDXP_ID,
         NULL AS ROOM_SPL_ID,
         MCFL.SHORT_FLOOR_ID,
         MCFL.FLOOR_NAME,
         MCFL.FLOOR_TYPE,
         MCFL.FLOOR_LOC,
         MCFL.GIS_ID AS FLOOR_GIS_ID,
         MCFL.SDXP_ID AS FLOOR_SDXP_ID,
         MCFL.SPL_ID AS FLOOR_SPL_ID,
         MCF.SHORT_FACILITY_ID,
         MCF.FACILITY_NAME,
         MCF.FACILITY_TYPE,
         MCF.FACILITY_ADDR,
         MCF.ALTERNATE_FACILITY_ADDR,
         MCF.FACILITY_LOC,
         MCF.GIS_ID AS FACILITY_GIS_ID,
         MCF.SDXP_ID AS FACILITY_SDXP_ID,
         MCF.SPL_ID AS FACILITY_SPL_ID,
         MCS.SITE_ID AS SITE_ID,
         MCS.SITE_NAME,
         MCS.SITE_TYPE,
         MCS.SITE_ADDR,
         MCS.ALTERNATE_SITE_ADDR,
         MCS.GIS_ID AS SITE_GIS_ID,
         MCS.SDXP_ID AS SITE_SDXP_ID,
         MCS.SPL_ID AS SITE_SPL_ID
    FROM MASTER_CITY_SITE MCS INNER JOIN MASTER_CITY_FACILITY MCF ON MCS.SITE_ID = MCF.SITE_ID
         INNER JOIN MASTER_CITY_FLOOR MCFL ON MCF.FACILITY_ID = MCFL.FACILITY_ID
UNION
  SELECT /*+ no_merge */ DISTINCT
         MCR.ROOM_ID AS LOCATION_ID,
         DECODE(MCR.ROOM_NAME, '', MCR.SHORT_ROOM_ID || ', ', MCR.ROOM_NAME || ', ') || 
            DECODE(MCFL.FLOOR_NAME, '', '', MCFL.FLOOR_NAME || ', ') || 
            DECODE(MCF.FACILITY_NAME, '', '', MCF.FACILITY_NAME || ', ') || 
            DECODE(MCS.SITE_NAME, '', '', MCS.SITE_NAME) AS LOCATION_NAME,
         DECODE(MCFL.FLOOR_NAME, '', '', MCFL.FLOOR_NAME || ', ') || 
            DECODE(MCF.FACILITY_NAME, '', '', MCF.FACILITY_NAME || ', ') || 
            DECODE(MCS.SITE_NAME, '', '', MCS.SITE_NAME) AS LOCATION_NAME_SHORT,
         MCR.SHORT_ROOM_ID,
         MCR.ROOM_NAME,
         MCR.ROOM_NUMBER,
         MCR.ROOM_ADDR,
         MCR.ROOM_LOC,
         MCR.ROOM_TYPE,
         MCR.ROOM_FUNCTION,
         MCR.ROOM_CAPACITY,
         MCR.BOOK_THROUGH_GROUPWISE,
         MCR.NETWORK_CONNECTION,
         MCR.TELEPHONE_AVAILABLE,
         MCR.TELEPHONE_NUMBER,
         MCR.TELEPHONE_LINE_ONLY,
         MCR.INSTALLED_PROJECTOR,
         MCR.GIS_ID AS ROOM_GIS_ID,
         MCR.SDXP_ID AS ROOM_SDXP_ID,
         MCR.SPL_ID AS ROOM_SPL_ID,
         MCFL.SHORT_FLOOR_ID,
         MCFL.FLOOR_NAME,
         MCFL.FLOOR_TYPE,
         MCFL.FLOOR_LOC,
         MCFL.GIS_ID AS FLOOR_GIS_ID,
         MCFL.SDXP_ID AS FLOOR_SDXP_ID,
         MCFL.SPL_ID AS FLOOR_SPL_ID,
         MCF.SHORT_FACILITY_ID,
         MCF.FACILITY_NAME,
         MCF.FACILITY_TYPE,
         MCF.FACILITY_ADDR,
         MCF.ALTERNATE_FACILITY_ADDR,
         MCF.FACILITY_LOC,
         MCF.GIS_ID AS FACILITY_GIS_ID,
         MCF.SDXP_ID AS FACILITY_SDXP_ID,
         MCF.SPL_ID AS FACILITY_SPL_ID,
         MCS.SITE_ID AS SITE_ID,
         MCS.SITE_NAME,
         MCS.SITE_TYPE,
         MCS.SITE_ADDR,
         MCS.ALTERNATE_SITE_ADDR,
         MCS.GIS_ID AS SITE_GIS_ID,
         MCS.SDXP_ID AS SITE_SDXP_ID,
         MCS.SPL_ID AS SITE_SPL_ID
    FROM MASTER_CITY_SITE MCS INNER JOIN MASTER_CITY_FACILITY MCF ON MCS.SITE_ID = MCF.SITE_ID
         INNER JOIN MASTER_CITY_FLOOR MCFL ON MCF.FACILITY_ID = MCFL.FACILITY_ID
                    INNER JOIN MASTER_CITY_ROOM MCR ON MCFL.FLOOR_ID = MCR.FLOOR_ID
    );

Open in new window

Franck Pachot

Hi,
Your error looks like following bug in metalink:
Bug 7579079 OERI [qctopn1] during join predicate push down
It is fixed in 10.2.0.5
Suggested workaround is the "_optimizer_push_pred_cost_based" parameter, but I suppose you will have same issue than with no_merge hint.
Regards,
Franck.

Devinder Singh Virdi

I believe you need to use outer join because
you are getting all records with few columns from MASTER_CITY_SITE, then MASTER_CITY_FACILITY and so one.
Use the following
select t1.col1, t1.col2, t2.col1, t2.col2, t3.col1, t3.col2
from t1, t2, t3
where t1.SITE_ID= t2.SITE_ID(+)
and t1.SITE_ID= t3.SITE_ID(+)

shaynegw

ASKER

Hi franchpachot

Do you have a link to that bug, I can't find it anywhere in metalink.

Thanks and take care,
Shayne

shaynegw

ASKER

Hi virdi_ds

I'm not sure I follow. I tried and replaced my inner joins with left outer joins but it makes no difference.

Thanks and take care,
Shayne

Devinder Singh Virdi

I mean instead of many union, I belive we can make it as single statement with outer join, in that way instead of scanning talble ilke MASTER_CIDY_SITE 4 times, oracle will use Hash join once.

ASKER CERTIFIED SOLUTION

Franck Pachot

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial