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

shaynegwAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Franck PachotCommented:
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.
0
Devinder Singh VirdiLead Oracle DBA TeamCommented:
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(+)
0
shaynegwAuthor Commented:
Hi franchpachot

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

Thanks and take care,
Shayne
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

shaynegwAuthor Commented:
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
0
Devinder Singh VirdiLead Oracle DBA TeamCommented:
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.
0
Franck PachotCommented:
Hi,
Metalink has a search for ORA-600:
Troubleshoot an ORA-600 or ORA-7445 Error Using the Error Lookup Tool (Doc ID 153788.1)

 Bug 4626659: ORA-600 [QCTOPN1] ON QUERY USING NESTED OUTER JOINS
 https://supporthtml.oracle.com/ep/faces/secure/km/BugDisplay.jspx?id=4626659&bugProductSource=Oracle&h=Y

Regards,
Franck.

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.