Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> re: Query from hell

re: Query from hell

From: Paula Stankus <paulastankus_at_yahoo.com>
Date: Thu, 19 Oct 2006 06:51:38 -0700 (PDT)
Message-ID: <20061019135138.58722.qmail@web33214.mail.mud.yahoo.com>


Listed below is a sample of some horrific SQL. As you can see it joins multiple tables together (large ones) 67 times only to add a literal (the county number) to a specific field each time. I know that there is a better way to do this. I am thinking a temporary table with the joined data then just reading through the data to produce the one concatenated field but I was wondering if an inline view would be better. Any suggestions would be greatly appreciated. prompt create mv_plan_county_factor; DROP MATERIALIZED VIEW MV_PLAN_COUNTY_FACTOR; alter session set transaction use rollback segment= CREATE MATERIALIZED VIEW MV_PLAN_COUNTY_FACTOR TABLESPACE USERS NOLOGGING BUILD IMMEDIATE REFRESH FORCE NEXT TRUNC(SYSDATE) + 200 + 3 / 24 AS SELECT sd.sercs_data_id PLAN_ID ,sd2.sercs_data_id || '01' COUNTY_FACTOR_ID ,ds.created_by_id ,SYSDATE date_created FROM idcs.sercs_data sd ,idcs.sercs_data sd2 ,idcs.sercs_data sd3 ,idcs.data_submission ds ,idcs.data_collection dc ,ifass.filing f ,ifass.filing_component fc ,ifass.filing_authority fa ,edms.filing_main fm WHERE ds.status_ind = 1 -- APPROVED AND f.filing_status_id = 5 -- FINAL ACTION AND ds.data_collection_id = 6 -- SERCS AND ds.no_data_ind = 0 -- DATA FILING AND NOT sd.column_fn IS NULL AND sd2.column_bv = sd.column_fw AND SERC.PKG_COMMON.County_MV_Check(ds.data_submission_id,fa.authority_id,sd3.column_d) = 1 AND fm.final_disposition = 'APPROVED' -- JOINS AND sd.data_submission_id = ds.data_submission_id AND sd2.data_submission_id = sd.data_submission_id AND sd3.data_submission_id = sd.data_submission_id AND ds.data_collection_id = dc.data_collection_id AND ds.filing_id = f.filing_id AND f.filing_id = fc.filing_id AND fc.filing_component_id = fa.filing_component_id AND f.file_log_num = fm.file_log_num UNION SELECT sd.sercs_data_id PLAN_ID ,sd2.sercs_data_id || '02' COUNTY_FACTOR_ID ,ds.created_by_id ,SYSDATE date_created FROM idcs.sercs_data sd ,idcs.sercs_data sd2 ,idcs.sercs_data sd3 ,idcs.data_submission ds ,idcs.data_collection dc ,ifass.filing f ,ifass.filing_component fc ,ifass.filing_authority fa ,edms.filing_main fm WHERE ds.status_ind = 1 AND f.filing_status_id = 5 AND ds.data_collection_id = 6 AND ds.no_data_ind = 0 AND NOT sd.column_fn IS NULL AND sd2.column_bv = sd.column_fw AND SERC.PKG_COMMON.County_MV_Check(ds.data_submission_id,fa.authority_id,sd3.column_d) = 1 AND fm.final_disposition = 'APPROVED' AND sd.data_submission_id = ds.data_submission_id AND sd2.data_submission_id = sd.data_submission_id AND sd3.data_submission_id = sd.data_submission_id AND ds.data_collection_id = dc.data_collection_id AND ds.filing_id = f.filing_id AND f.filing_id = fc.filing_id AND fc.filing_component_id = fa.filing_component_id AND f.file_log_num = fm.file_log_num UNION SELECT sd.sercs_data_id PLAN_ID ,sd2.sercs_data_id || '03' COUNTY_FACTOR_ID ,ds.created_by_id ,SYSDATE date_created FROM idcs.sercs_data sd ,idcs.sercs_data sd2 ,idcs.sercs_data sd3 ,idcs.data_submission ds ,idcs.data_collection dc ,ifass.filing f ,ifass.filing_component fc ,ifass.filing_authority fa ,edms.filing_main fm WHERE ds.status_ind = 1 AND f.filing_status_id = 5 AND ds.data_collection_id = 6 AND ds.no_data_ind = 0 AND NOT sd.column_fn IS NULL AND sd2.column_bv = sd.column_fw AND SERC.PKG_COMMON.County_MV_Check(ds.data_submission_id,fa.authority_id,sd3.column_d) = 1 AND fm.final_disposition = 'APPROVED' AND sd.data_submission_id = ds.data_submission_id AND sd2.data_submission_id = sd.data_submission_id AND sd3.data_submission_id = sd.data_submission_id AND ds.data_collection_id = dc.data_collection_id AND ds.filing_id = f.filing_id AND f.filing_id = fc.filing_id AND fc.filing_component_id = fa.filing_component_id AND f.file_log_num = fm.file_log_num

--
http://www.freelists.org/webpage/oracle-l
Received on Thu Oct 19 2006 - 08:51:38 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US