RE: Query from hell
- From: "Jacques Kilchoer" <Jacques.Kilchoer@xxxxxxxxx>
- To: <ineyman@xxxxxxxxxxxxxx>, <paulastankus@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 19 Oct 2006 14:50:35 -0700
I got the impression, from the QFH (Query From Hell (TM)), that the user wanted
the identical bunch of rows back 67 times, with one value being changed in each
67th occurrence, from $VALUE to $VALUE_01, $VALUE_02, etc..
I would change the QFT to this (adding a cartesian join to 67 rows selected
from dual):
SELECT sd.sercs_data_id PLAN_ID
,sd2.sercs_data_id || to_char (counter_table.rn, 'FM09') as 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
/*** addition here ***/ , (select rn from (select rownum as rn from dual
connect by level <= 67)) counter_table
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
________________________________
De : oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] De la
part de Igor Neyman
Envoyé : jeudi, 19. octobre 2006 09:56
À : paulastankus@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Objet : RE: Query from hell
Is there some criteria affecting which county_factor_id ('01', '02', etc.) is
concatenated?
I couldn't find one looking at your query, must be missing something.
But, if there is one, you could use DECODE instead of 67 UNIONs:
sd2.sercs_data_id || DECODE(<criteria>, <val1>, '01', <val2>, '02',...)
Igor
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Paula Stankus
Sent: Thursday, October 19, 2006 9:52 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: re: Query from hell
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
Other related posts:
