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: SQL Power !!

RE: SQL Power !!

From: Stephane Paquette <stephane.paquette_at_standardlife.ca>
Date: Fri, 09 May 2003 12:50:44 -0800
Message-ID: <F001.00595CFD.20030509125044@fatcity.com> 





It run in dev but you had to put a very specific filter in the where clause
to avoid ORA-4030.


In prod it crashed with ora-4030 even with the very specific filter in the
where clause.



The developper wanted to show the rows as columns and he did not know how to
"pivot" the table.





Stephane Paquette


Administrateur de bases de donnees


Database Administrator


Standard Life


www.standardlife.ca


Tel. (514) 925-7187


stephane.paquette_at_standardlife.ca <mailto:stephane.paquette_at_standardlife.ca>





-----Original Message-----


Ian A.


Sent: Friday, May 09, 2003 4:05 PM


To: Multiple recipients of list ORACLE-L




22 eh?  Did it ever run?  Was Oracle forever trying to acquire a cache
buffer chain latch.  People or query  generators needlessly joining views to
their base tables is a big headache for us.  Peoplesoft (in-the-head) loves
to do this.



Ian MacGregor


Stanford Linear Accelerator Center


ian_at_SLAC.Stanford.edu




-----Original Message-----


Sent: Friday, May 09, 2003 10:27 AM


To: Multiple recipients of list ORACLE-L




Kirti,



I can challenge that !



I just finished optimising a query produced by developpement, once printed
it was 6 pages long and accessed many tables.



But the best part is that a table was accessed 22 times. Yes 22 times


(inline views madness).




After spending 1 hour trying to understand the beast without success, I just
call the guy and ask what was the qestion the query was supposed to answer.




Stephane Paquette


Administrateur de bases de donnees


Database Administrator


Standard Life


www.standardlife.ca


Tel (514) 925-7187


stephane.paquette_at_standardlife.ca <mailto:stephane.paquette_at_standardlife.ca>





-----Original Message-----


Kirti


Sent: Friday, May 09, 2003 12:22 PM


To: Multiple recipients of list ORACLE-L




Here is what was caught by my co-worker recently.
One of the Developers, of our Preferred Vendor, actually wrote this stuff.
This is invoked by the one of the forms on the intranet Web application.  I
was told there are quite a few scripts like this one. Performance issues? No
one is complaining (yet)  .... so who cares :) Enjoy...
- Kirti


SELECT DISTINCT CUST1.customer_id, LIST1.finding_name, NVL(LIST1.atn_npa, '
'), NVL(LIST1.atn_cop, ' '), NVL(LIST1.atn_line_no, ' '), LIST1.lni,

NVL(LIST1.listed_tn_npa, ' '), NVL(LIST1. listed_tn_cop, ' '),
NVL(LIST1.listed_tn_line_no, ' '), NVL(ADDR1.house_no, ' '),
NVL(ADDR1.house_no_suff, ' '), NVL(ADDR1.pre_directional, ' '),
NVL(ADDR1.street_name, ' '), NVL(ADDR1.post_directional, ' '),
NVL(ADDR1.sub_location, ' '), NVL(ADDR1.Community, ' '), NVL(ADDR1.zip_five,


' '), NVL(ADDR1.state_abbr_override, ' '), LIST1.listing_status,

NVL(CUST1.customer_type, ' '), NVL(CUST1.home_data_base, ' '),
NVL(CUST1.default_market_code, ' '), NVL(TELCO.TELCO_CODE, ' '),
NVL(TELCO.TELCO_NAME, ' '), NVL(TO_CHAR(LIST1.establish_date,'YYYYMMDD'),'


'), NVL((select 'Y' from query q, dir_curr_issue where q.customer_id =
cust1.customer_id and q.query_status in (1,3,4) and q.product_code =
dir_curr_issue.directory_code and q.product_issue_num >=
dir_curr_issue.directory_issue_num


and rownum = 1), 'N' ) ,

NVL(CUST1.TELCO_CREDIT_STOP, ' '), NVL(CUST1.BARS_CREDIT_STOP, 0),
NVL(CUST1.EAST_CREDIT_STOP, ' '), NVL(CUST1.EARLY_CONT_CUST_IND, ' '),
NVL(LIST1.SOHO_LST_IND, ' '), NVL(CUST1.NI_AMOUNT, 0), NVL(CUST1.PI_AMOUNT,


0), HEADING.short_heading_name, NVL(CUST1.CUST_PROSPECT_CODE, 0),
NVL(CUST1.RETIREMENT_STATUS, ' '), (SELECT

nvl(sum(DIRECTORY_SUMMARY0.pi_amount),0)
FROM



DIRECTORY_ISSUE e, DIRECTORY_SUMMARY DIRECTORY_SUMMARY0, ASSIGNMENT
ASSIGNMENT_C, assignment ASSIGNMENT_T,DIR_CURR_ISSUE DIR_CURR_ISSUE0 WHERE

DIRECTORY_SUMMARY0.customer_id = CUST1.customer_id and
DIRECTORY_SUMMARY0.product_code = DIR_CURR_ISSUE0.directory_code and
DIRECTORY_SUMMARY0.product_issue_num = DIR_CURR_ISSUE0.directory_issue_num


and DIRECTORY_SUMMARY0.PRODUCT_CODE = E.DIRECTORY_CODE
and DIRECTORY_SUMMARY0.PRODUCT_ISSUE_NUM = e.directory_issue_num and
e.directory_del_ind != :1 and DIRECTORY_SUMMARY0.canvass_code = '' and
DIRECTORY_SUMMARY0.T_SPLIT_ASSIGN_ID = ASSIGNMENT_T.assignment_id (+) and
DIRECTORY_SUMMARY0.current_assign_id = ASSIGNMENT_C.assignment_id (+) AND :2
in (ASSIGNMENT_C.sales_rep,ASSIGNMENT_T.sales_rep))
,(CASE WHEN EXISTS( SELECT 1



FROM



DIR_CURR_ISSUE DC, DIRECTORY_SUMMARY DS



WHERE


CUST1.customer_id = DS.customer_id
AND DS.product_code = DC.directory_code
AND DS.PRODUCT_ISSUE_NUM >= DC.DIRECTORY_ISSUE_NUM


AND DS.curr_special_flag = 20 and rownum = 1)
THEN 'Y'



ELSE 'N' END )



, (CASE WHEN EXISTS ( SELECT 1



FROM



ITEMS_IN_CLAIM IC, DIRECTORY_SUMMARY DIR1

WHERE



IC.customer_id = CUST1.customer_id


AND DIR1.customer_id = CUST1.customer_id

AND IC.PRODUCT = DIR1.product_code
AND IC.issue_number = DIR1.product_issue_num
AND IC.DELETE_INDICATOR ='N' )




THEN 'Y'



ELSE 'N' END ),



(CASE WHEN EXISTS ( SELECT 1




FROM



DIRECTORY_ISSUE DI, DIRECTORY_SUMMARY DIR1

WHERE



DIR1.customer_id = CUST1.customer_id


AND DIR1.product_code = DI.directory_code
AND DIR1.product_issue_num = DI.directory_issue_num
AND ADD_MONTHS(TO_DATE(:3,'YYYYMMDD'),1) > DI.TELCO_CLOSE_DATE AND

TO_DATE(:4,'YYYYMMDD') < DI.TELCO_CLOSE_DATE AND DI.TELCO_CLOSE_DATE IS NOT
NULL) THEN 'Y' ELSE 'N' END ) , NVL((select CONTACT.CONTACT_NAME from
CONTACT where CUST1.CONTACT_ID = CONTACT.CONTACT_ID(+)), ' ') FROM LISTING
LIST1, CUSTOMER CUST1, ADDRESS ADDR1, DIRECTORY_SUMMARY, TELCO,

DIR_CURR_ISSUE, ASSIGNMENT, HEADING, ITEM_DIR WHERE


CUST1.main_main_listing_id = LIST1.listing_id(+) AND
LIST1.last_version_ind(+) = :5 AND
LIST1.listed_address_id = ADDR1.address_id(+) AND TELCO.TELCO_CODE =
LIST1.TELCO_ID AND DIRECTORY_SUMMARY.customer_id = CUST1.customer_id AND


DIRECTORY_SUMMARY.PRODUCT_CODE = DIR_CURR_ISSUE.DIRECTORY_CODE AND

DIRECTORY_SUMMARY.PRODUCT_ISSUE_NUM >= DIR_CURR_ISSUE.DIRECTORY_ISSUE_NUM

AND



ASSIGNMENT.SALES_REP = :6 AND



CUST1.DOMINANT_HEADING = HEADING.HEADING_CODE (+)AND

NOT EXISTS



(SELECT 1 FROM DIRECTORY_SUMMARY DS, ASSIGNMENT ASSIGNMENT_C, assignment

ASSIGNMENT_T,DIR_CURR_ISSUE DC WHERE DS.customer_id = CUST1.customer_id AND
DS.PRODUCT_CODE = DC.DIRECTORY_CODE AND DS.PRODUCT_ISSUE_NUM >=

DC.DIRECTORY_ISSUE_NUM AND DS.T_SPLIT_ASSIGN_ID = ASSIGNMENT_T.assignment_id


(+) AND DS.current_assign_id = ASSIGNMENT_C.assignment_id (+) AND :7 in

(ASSIGNMENT_C.sales_rep,ASSIGNMENT_T.sales_rep)

AND DS.RETIREMENT_STATUS!= :8 ) AND DIRECTORY_SUMMARY.RETIREMENT_STATUS in


(:9, :10) AND CUST1.customer_id = ITEM_DIR.customer_id AND

DIRECTORY_SUMMARY.PRODUCT_CODE =ITEM_DIR.PRODUCT_CODE AND

DIRECTORY_SUMMARY.PRODUCT_ISSUE_NUM =ITEM_DIR.PRODUCT_ISSUE_NUM AND

ITEM_DIR.LAST_VERSION_IND = 'Y' AND ( ITEM_DIR.heading_code = :11) AND
rownum <= :12 AND DIRECTORY_SUMMARY.CURRENT_ASSIGN_ID =
ASSIGNMENT.ASSIGNMENT_ID UNION SELECT DISTINCT CUST1.customer_id,
LIST1.finding_name, NVL(LIST1.atn_npa, ' '), NVL(LIST1.atn_cop, ' '),

NVL(LIST1.atn_line_no, ' '), LIST1.lni, NVL(LIST1.listed_tn_npa, ' '),
NVL(LIST1. listed_tn_cop, ' '), NVL(LIST1.listed_tn_line_no, ' '),
NVL(ADDR1.house_no, ' '), NVL(ADDR1.house_no_suff, ' '),
NVL(ADDR1.pre_directional, ' '), NVL(ADDR1.street_name, ' '),
NVL(ADDR1.post_directional, ' '), NVL(ADDR1.sub_location, ' '),
NVL(ADDR1.Community, ' '), NVL(ADDR1.zip_five, ' '),
NVL(ADDR1.state_abbr_override, ' '), LIST1.listing_status,
NVL(CUST1.customer_type, ' '),
NVL(CUST1.home_data_base, ' '), NVL(CUST1.default_market_code, ' '),
NVL(TELCO.TELCO_CODE, ' '), NVL(TELCO.TELCO_NAME, ' '),
NVL(TO_CHAR(LIST1.establish_date,'YYYYMMDD'),' '), NVL((select 'Y' from


query q, dir_curr_issue where q.customer_id = cust1.customer_id and
q.query_status in (1,3,4) and q.product_code = dir_curr_issue.directory_code
and q.product_issue_num >= dir_curr_issue.directory_issue_num
and rownum = 1), 'N' ) ,

NVL(CUST1.TELCO_CREDIT_STOP, ' '), NVL(CUST1.BARS_CREDIT_STOP, 0),
NVL(CUST1.EAST_CREDIT_STOP, ' '), NVL(CUST1.EARLY_CONT_CUST_IND, ' '),
NVL(LIST1.SOHO_LST_IND, ' '), NVL(CUST1.NI_AMOUNT, 0), NVL(CUST1.PI_AMOUNT,


0), HEADING.short_heading_name, NVL(CUST1.CUST_PROSPECT_CODE, 0),
NVL(CUST1.RETIREMENT_STATUS, ' '), (SELECT

nvl(sum(DIRECTORY_SUMMARY0.pi_amount),0)
FROM



DIRECTORY_ISSUE e, DIRECTORY_SUMMARY DIRECTORY_SUMMARY0, ASSIGNMENT
ASSIGNMENT_C, assignment ASSIGNMENT_T,DIR_CURR_ISSUE DIR_CURR_ISSUE0 WHERE

DIRECTORY_SUMMARY0.customer_id = CUST1.customer_id and
DIRECTORY_SUMMARY0.product_code = DIR_CURR_ISSUE0.directory_code and
DIRECTORY_SUMMARY0.product_issue_num = DIR_CURR_ISSUE0.directory_issue_num


and DIRECTORY_SUMMARY0.PRODUCT_CODE = E.DIRECTORY_CODE
and DIRECTORY_SUMMARY0.PRODUCT_ISSUE_NUM = e.directory_issue_num and
e.directory_del_ind != :13 and DIRECTORY_SUMMARY0.canvass_code = '' and

DIRECTORY_SUMMARY0.T_SPLIT_ASSIGN_ID = ASSIGNMENT_T.assignment_id (+) and
DIRECTORY_SUMMARY0.current_assign_id = ASSIGNMENT_C.assignment_id (+) AND
:14 in (ASSIGNMENT_C.sales_rep,ASSIGNMENT_T.sales_rep))


,(CASE WHEN EXISTS( SELECT 1



FROM



DIR_CURR_ISSUE DC, DIRECTORY_SUMMARY DS



WHERE


CUST1.customer_id = DS.customer_id
AND DS.product_code = DC.directory_code
AND DS.PRODUCT_ISSUE_NUM >= DC.DIRECTORY_ISSUE_NUM


AND DS.curr_special_flag = 20 and rownum = 1)
THEN 'Y'



ELSE 'N' END )



, (CASE WHEN EXISTS ( SELECT 1



FROM



ITEMS_IN_CLAIM IC, DIRECTORY_SUMMARY DIR1

WHERE



IC.customer_id = CUST1.customer_id


AND DIR1.customer_id = CUST1.customer_id

AND IC.PRODUCT = DIR1.product_code
AND IC.issue_number = DIR1.product_issue_num
AND IC.DELETE_INDICATOR ='N' )




THEN 'Y'



ELSE 'N' END ),



(CASE WHEN EXISTS ( SELECT 1




FROM



DIRECTORY_ISSUE DI, DIRECTORY_SUMMARY DIR1

WHERE



DIR1.customer_id = CUST1.customer_id


AND DIR1.product_code = DI.directory_code
AND DIR1.product_issue_num = DI.directory_issue_num
AND ADD_MONTHS(TO_DATE(:15,'YYYYMMDD'),1) > DI.TELCO_CLOSE_DATE AND

TO_DATE(:16,'YYYYMMDD') < DI.TELCO_CLOSE_DATE AND DI.TELCO_CLOSE_DATE IS NOT
NULL) THEN 'Y' ELSE 'N' END ) , NVL((select CONTACT.CONTACT_NAME from
CONTACT where CUST1.CONTACT_ID = CONTACT.CONTACT_ID(+)), ' ') FROM LISTING
LIST1, CUSTOMER CUST1, ADDRESS ADDR1, DIRECTORY_SUMMARY, TELCO,

DIR_CURR_ISSUE, ASSIGNMENT, HEADING, ITEM_DIR WHERE


CUST1.main_main_listing_id = LIST1.listing_id(+) AND
LIST1.last_version_ind(+) = :17 AND
LIST1.listed_address_id = ADDR1.address_id(+) AND TELCO.TELCO_CODE =
LIST1.TELCO_ID AND DIRECTORY_SUMMARY.customer_id = CUST1.customer_id AND


DIRECTORY_SUMMARY.PRODUCT_CODE = DIR_CURR_ISSUE.DIRECTORY_CODE AND

DIRECTORY_SUMMARY.PRODUCT_ISSUE_NUM >= DIR_CURR_ISSUE.DIRECTORY_ISSUE_NUM

AND



ASSIGNMENT.SALES_REP = :18 AND



CUST1.DOMINANT_HEADING = HEADING.HEADING_CODE (+)AND

NOT EXISTS



(SELECT 1 FROM DIRECTORY_SUMMARY DS, ASSIGNMENT ASSIGNMENT_C, assignment

ASSIGNMENT_T,DIR_CURR_ISSUE DC WHERE DS.customer_id = CUST1.customer_id AND
DS.PRODUCT_CODE = DC.DIRECTORY_CODE AND DS.PRODUCT_ISSUE_NUM >=

DC.DIRECTORY_ISSUE_NUM AND DS.T_SPLIT_ASSIGN_ID = ASSIGNMENT_T.assignment_id


(+) AND DS.current_assign_id = ASSIGNMENT_C.assignment_id (+) AND :19 in

(ASSIGNMENT_C.sales_rep,ASSIGNMENT_T.sales_rep)

AND DS.RETIREMENT_STATUS!= :20 ) AND DIRECTORY_SUMMARY.RETIREMENT_STATUS in


(:21, :22) AND CUST1.customer_id = ITEM_DIR.customer_id AND

DIRECTORY_SUMMARY.PRODUCT_CODE =ITEM_DIR.PRODUCT_CODE AND

DIRECTORY_SUMMARY.PRODUCT_ISSUE_NUM =ITEM_DIR.PRODUCT_ISSUE_NUM AND

ITEM_DIR.LAST_VERSION_IND = 'Y' AND ( ITEM_DIR.heading_code = :23) AND
rownum <= :24 AND DIRECTORY_SUMMARY.T_SPLIT_ASSIGN_ID is not NULL AND
DIRECTORY_SUMMARY.T_SPLIT_ASSIGN_ID = ASSIGNMENT.ASSIGNMENT_ID




--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Deshpande, Kirti
  INET: kirti.deshpande_at_verizon.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the
message BODY, include a line containing: UNSUB ORACLE-L (or the name of
mailing list you want to be removed from).  You may also send the HELP
command for other information (like subscribing).

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Stephane Paquette
  INET: stephane.paquette_at_standardlife.ca

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the
message BODY, include a line containing: UNSUB ORACLE-L (or the name of
mailing list you want to be removed from).  You may also send the HELP
command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: MacGregor, Ian A.
  INET: ian_at_SLAC.Stanford.EDU

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Paquette
  INET: stephane.paquette_at_standardlife.ca

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).


Received on Fri May 09 2003 - 15:50:44 CDT












Original text of this message













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