OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » Query cost (oracle, 10g, HP-UX B.11.23)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Query cost [message #537162]

Tue, 27 December 2011 01:15

aruldts
Messages: 1
Registered: December 2011
Location: Chennai

Junior Member

/* Formatted on 2011/12/26 12:20 (Formatter Plus v4.8. Cool

*/
SELECT LPAD (nodal_br_code, 7, ' ')
|| DECODE (pay_br_code,
'', RPAD (' ', 7, ' '),
RPAD (pay_br_code, 7, ' ')
)
|| DECODE (title_code,
'', RPAD (' ', 5, ' '),
RPAD (title_code, 5, ' ')
)
|| RPAD (first_name, 30, ' ')
|| DECODE (middle_name,
'', RPAD (' ', 15, ' '),
RPAD (middle_name, 15, ' ')
)
|| DECODE (last_name,
'', RPAD (' ', 15, ' '),
RPAD (last_name, 15, ' ')
)
|| RPAD (gender, 1, ' ')
|| DECODE (acc_num, '', RPAD (' ', 30, ' '), RPAD (acc_num, 30, ' '))
|| DECODE (new_ppo_num,
'', RPAD (' ', 12, ' '),
RPAD (new_ppo_num, 12, ' ')
)
|| DECODE (old_ppo_num,
'', RPAD (' ', 30, ' '),
RPAD (old_ppo_num, 30, ' ')
)
|| DECODE (org_name,
'', RPAD (' ', 50, ' '),
RPAD (org_name, 50, ' ')
)
|| DECODE (retirement_office,
'', RPAD (' ', 40, ' '),
RPAD (retirement_office, 40, ' ')
)
|| DECODE (pao_code, '', RPAD (' ', 5, ' '), RPAD (pao_code, 5, ' '))
|| DECODE (pdob, '', RPAD (' ', 8, ' '), RPAD (pdob, 8, ' '))
|| RPAD (retire_date, 8, ' ')
|| DECODE (address, '', RPAD (' ', 60, ' '), RPAD (address, 60, ' '))
|| DECODE (address2,
'', RPAD (' ', 60, ' '),
RPAD (address2, 60, ' ')
)
|| DECODE (district,
'', RPAD (' ', 30, ' '),
RPAD (district, 30, ' ')
)
|| DECODE (state, '', RPAD (' ', 20, ' '), RPAD (state, 20, ' '))
|| DECODE (pincode, '', LPAD (' ', 6, ' '), LPAD (pincode, 6, ' '))
|| DECODE (current_pension,
'', LPAD (' ', 5, ' '),
LPAD (current_pension, 5, ' ')
)
|| DECODE (revised_code,
'', LPAD (' ', 1, ' '),
LPAD (revised_code, 1, ' ')
)
|| DECODE (fp_title_code,
'', RPAD (' ', 5, ' '),
RPAD (fp_title_code, 5, ' ')
)
|| DECODE (fp_first_name,
'', RPAD (' ', 30, ' '),
RPAD (fp_first_name, 30, ' ')
)
|| DECODE (fp_middle_name,
'', RPAD (' ', 15, ' '),
RPAD (fp_middle_name, 15, ' ')
)
|| DECODE (fp_last_name,
'', RPAD (' ', 15, ' '),
RPAD (fp_last_name, 15, ' ')
)
|| RPAD (pension_class, 1, ' ')
|| DECODE (fp_dob,
'', RPAD (' ', 8, ' '),
RPAD (TO_CHAR (fp_dob, 'YYYYMMDD'), 8, ' ')
)
|| DECODE (fp_dod, '', RPAD (' ', 8, ' '), RPAD (fp_dod, 8, ' '))
|| DECODE (enhanced_rate_fp,
'', LPAD (' ', 5, ' '),
LPAD (enhanced_rate_fp, 5, ' ')
)
|| DECODE (enhanced_from_date,
'', RPAD (' ', 8, ' '),
RPAD (enhanced_from_date, 8, ' ')
)
|| DECODE (enhanced_to_date,
'', RPAD (' ', 8, ' '),
RPAD (TO_CHAR (enhanced_to_date, 'YYYYMMDD'), 8, ' ')
)
|| DECODE (normal_fp_value,
'', LPAD (' ', 5, ' '),
LPAD (normal_fp_value, 5, ' ')
)
|| DECODE (enhanced_to_date,
'', RPAD (' ', 8, ' '),
RPAD (TO_CHAR (enhanced_to_date + 1, 'YYYYMMDD'), 8, ' ')
)
|| DECODE (normal_to_date,
'', RPAD (' ', 8, ' '),
RPAD (normal_to_date, 8, ' ')
)
|| DECODE (pay_band,
'', RPAD (' ', 80, ' '),
RPAD (pay_band, 80, ' ')
)
|| DECODE (grade_pay,
'', LPAD ('0', 5, ' '),
LPAD (grade_pay, 5, ' ')
)
|| DECODE (last_pay_drawn,
'', LPAD ('0', 5, ' '),
LPAD (last_pay_drawn, 5, ' ')
)
|| DECODE (pay_commission (pp_acct_num),
'P006', LPAD (additional_allowance, 5, ' '),
LPAD ('0', 5, ' ')
)
|| DECODE (constant_att_allowance,
'', LPAD ('0', 5, ' '),
LPAD (constant_att_allowance, 4, ' ')
)
|| DECODE (death_in_harness,
'', RPAD (' ', 1, ' '),
RPAD (death_in_harness, 1, ' ')
)
|| DECODE (medical_allowance,
0, RPAD ('N', 1, ' '),
RPAD ('Y', 1, ' ')
)
|| LPAD (medical_allowance, 6, ' ')
|| DECODE (commutation_value,
'', LPAD (' ', 7, ' '),
LPAD (commutation_value, 7, ' ')
)
|| DECODE (ais_service_cadre,
'', RPAD (' ', 3, ' '),
RPAD (ais_service_cadre, 3, ' ')
)
|| DECODE (ais_state_code,
'', RPAD (' ', 2, ' '),
RPAD (ais_state_code, 2, ' ')
)
|| DECODE (ais_year_join,
'', LPAD (' ', 4, ' '),
LPAD (ais_year_join, 4, ' ')
)
|| DECODE (qualifying_service_year,
'', LPAD (' ', 2, ' '),
LPAD (qualifying_service_year, 2, ' ')
)
|| DECODE (qualifying_service_month,
'', LPAD (' ', 2, ' '),
LPAD (qualifying_service_month, 2, ' ')
)
|| DECODE (qualifying_service_day,
'', LPAD (' ', 2, ' '),
LPAD (qualifying_service_day, 2, ' ')
)
|| DECODE (pp_status,
'', RPAD (' ', 1, ' '),
RPAD (pp_status, 1, ' ')
)
|| DECODE (pp_status_date,
'', RPAD (' ', 8, ' '),
RPAD (pp_status_date, 8, ' ')
) format_g
FROM (SELECT (SELECT DISTINCT SUBSTR (uniform_br_code, 1, 7)
FROM gbm_sol_view
WHERE sol_id = '1400') nodal_br_code,
(SELECT SUBSTR (uniform_br_code, 1, 7)
FROM gbm_sol_view
WHERE sol_id = gpm.sol_id) pay_br_code,
(SELECT sub_code_desc
FROM gbm_sub_code_master
WHERE sub_code = gpm.title_code) title_code,
gpm.first_name first_name, gpm.middle_name middle_name,
gpm.last_name last_name, gpm.gender gender,
RPAD
(DECODE (fn_gbm_pension_fndopracctnum (gpm.pp_acct_num),
'ERR_RESULT', '',
fn_gbm_pension_fndopracctnum (gpm.pp_acct_num)
),
20,
' '
) acc_num,
DECODE (LENGTH (ppo_num),
12, (DECODE (fn_isnumber (ppo_num),
'TRUE', ppo_num,
''
)),
''
) new_ppo_num,
DECODE
(old_ppo_number,
NULL, (DECODE (fn_isnumber (ppo_num),
'FALSE', ppo_num,
(DECODE (GREATEST (LENGTH (ppo_num), 12),
LEAST (LENGTH (ppo_num), 12), '',
(DECODE (fn_isnumber (ppo_num),
'TRUE', ppo_num,
''
)
)
)
)
)
),
old_ppo_number
) old_ppo_num,
DECODE (pao_code, '', gpm.org_name, gpm.org_name) org_name,
DECODE (pao_code,
'', gpm.retirement_office,
gpm.retirement_office
) retirement_office,
gpm.pao_code pao_code, TO_CHAR (gpm.dob, 'YYYYMMDD') pdob,
TO_CHAR (gpm.retire_date, 'YYYYMMDD') retire_date,
(REPLACE (REPLACE (REPLACE (address, CHR (10)), CHR (34)),
CHR (13)
)
) address,
(REPLACE (REPLACE (REPLACE (address2, CHR (10)), CHR (34)),
CHR (13)
)
) address2,
gpm.district district, (SELECT gpsd.state_desc
FROM gbm_pp_state_details gpsd
WHERE sub_code = gpm.state_code)
state,
gpm.pincode pincode,
(SELECT NVL (DECODE (gpm.pp_type,
'0041', component_pp_value,
'0042', component_family_value,
0
),
0
)
FROM gbm_pp_component_details gpcd
WHERE gpcd.pp_acct_num = gpm.pp_acct_num
AND component_name = 'B'
AND is_deleted = 'N'
AND (is_active = 'Y' AND verified_status = 'Y')
AND gpcd.eff_to_date =
(SELECT MAX (gpcd.eff_to_date)
FROM gbm_pp_component_details gpcd
WHERE gpcd.pp_acct_num = gpm.pp_acct_num
AND component_name = 'B'
AND is_deleted = 'N'
AND (is_active = 'Y' AND verified_status = 'Y'
))
AND ROWNUM = 1) current_pension,
gpm.revised_code revised_code,
(SELECT sub_code_desc
FROM gbm_sub_code_master
WHERE sub_code =
(SELECT TRIM (NVL (gpfd.title_code, '')
)
FROM gbm_pp_family_details gpfd
WHERE gpfd.pp_acct_num = gpm.pp_acct_num
AND gpfd.is_deleted = 'N'
AND gpfd.verified_status = 'Y'
AND preference IN (0, 1)
AND ROWNUM = 1)) fp_title_code,
NVL ((SELECT TRIM (NVL (gpfd.first_name, ''))
FROM gbm_pp_family_details gpfd
WHERE gpfd.pp_acct_num = gpm.pp_acct_num
AND gpfd.is_deleted = 'N'
AND gpfd.verified_status = 'Y'
AND preference IN (0, 1)
AND ROWNUM = 1),
''
) fp_first_name,
NVL ((SELECT TRIM (NVL (gpfd.middle_name, ''))
FROM gbm_pp_family_details gpfd
WHERE gpfd.pp_acct_num = gpm.pp_acct_num
AND gpfd.is_deleted = 'N'
AND gpfd.verified_status = 'Y'
AND preference IN (0, 1)
AND ROWNUM = 1),
''
) fp_middle_name,
NVL ((SELECT TRIM (NVL (gpfd.last_name, ''))
FROM gbm_pp_family_details gpfd
WHERE gpfd.pp_acct_num = gpm.pp_acct_num
AND gpfd.is_deleted = 'N'
AND gpfd.verified_status = 'Y'
AND preference IN (0, 1)
AND ROWNUM = 1),
''
) fp_last_name,
fn_gbm_pension_categories_code (gpm.pp_acct_num) pension_class,
NVL ((SELECT NVL (gpfd.dob, '')
FROM gbm_pp_family_details gpfd
WHERE gpfd.pp_acct_num = gpm.pp_acct_num
AND gpfd.is_deleted = 'N'
AND gpfd.verified_status = 'Y'
AND preference IN (0, 1)
AND ROWNUM = 1),
''
) fp_dob,
TO_CHAR
((DECODE
(gpm.pp_type,
'0042', (DECODE
(gpm.type_code,
'0006', (DECODE
(gpm.dod,
'', fn_get_pp_family_eventdate
(gpm.pp_acct_num),
gpm.dod
)
),
''
)
),
''
)
),
'YYYYMMDD'
) fp_dod,
DECODE
((SELECT component_enhanced_value
FROM gbm_pp_component_details gpcd
WHERE gpcd.pp_acct_num = gpm.pp_acct_num
AND is_active = 'Y'
AND gpcd.component_name = 'B'
AND gpcd.verified_status = 'Y'
AND gpcd.eff_to_date =
(SELECT MAX (gpcd.eff_to_date)
FROM gbm_pp_component_details gpcd
WHERE gpcd.pp_acct_num = gpm.pp_acct_num
AND component_name = 'B'
AND is_deleted = 'N'
AND ( is_active = 'Y'
AND verified_status = 'Y'
))
AND ROWNUM = 1),
'', 0,
(SELECT NVL (SUM (component_enhanced_value), 0)
FROM gbm_pp_component_details gpcd
WHERE gpcd.pp_acct_num = gpm.pp_acct_num
AND is_active = 'Y'
AND gpcd.component_name = 'B'
AND gpcd.verified_status = 'Y'
AND gpcd.eff_to_date =
(SELECT MAX (gpcd.eff_to_date)
FROM gbm_pp_component_details gpcd
WHERE gpcd.pp_acct_num = gpm.pp_acct_num
AND component_name = 'B'
AND is_deleted = 'N'
AND ( is_active = 'Y'
AND verified_status = 'Y'
))
AND ROWNUM = 1)
) enhanced_rate_fp,
(DECODE
(gpm.pp_type,
'0042', (DECODE
(gpm.type_code,
'0006', (DECODE
(gpm.conversion_date,
'', TO_CHAR (gpm.pp_start_date,
'YYYYMMDD'
),
TO_CHAR
(fn_get_pp_family_eventdate
(gpm.pp_acct_num),
'YYYYMMDD'
)
)
),
''
)
),
''
)
) enhanced_from_date,
(DECODE
(gpm.pp_type,
'0042', (DECODE
(gpm.type_code,
'0006', (DECODE
(SIGN
(MONTHS_BETWEEN
(ADD_MONTHS
(gpm.dob,
( (SELECT gspt.param_value
FROM gbm_scheme_parameter_table gspt
WHERE gspt.sch_code =
'0003'
AND gspt.is_active =
'Y'
AND gspt.param_name =
'MAX_FULL_PP_AGE')
* 12
)
),
(DECODE
(gpm.pp_type,
'0042', (DECODE
(gpm.type_code,
'0006', (DECODE
(gpm.dod,
'', fn_get_pp_family_eventdate
(gpm.pp_acct_num
),
gpm.dod
)
),
''
)
),
''
)
)
)
),
1, (ADD_MONTHS
((DECODE
(gpm.pp_type,
'0042', (DECODE
(gpm.type_code,
'0006', (DECODE
(gpm.dod,
'', fn_get_pp_family_eventdate
(gpm.pp_acct_num
),
gpm.dod
)
),
''
)
),
''
)
),
( (SELECT gspt.param_value
FROM gbm_scheme_parameter_table gspt
WHERE gspt.sch_code =
'0003'
AND gspt.is_active =
'Y'
AND gspt.param_name =
'SPOUSE_FULL_PAY_PERIOD')
* 12
)
)
),
(ADD_MONTHS
(gpm.dob,
( (SELECT gspt.param_value
FROM gbm_scheme_parameter_table gspt
WHERE gspt.sch_code =
'0003'
AND gspt.is_active =
'Y'
AND gspt.param_name =
'MAX_FULL_PP_AGE')
* 12
)
)
)
)
),
''
)
),
''
)
) enhanced_to_date,
DECODE
((SELECT component_family_value
FROM gbm_pp_component_details gpcd
WHERE gpcd.pp_acct_num = gpm.pp_acct_num
AND is_active = 'Y'
AND gpcd.component_name = 'B'
AND gpcd.verified_status = 'Y'
AND gpcd.eff_to_date =
(SELECT MAX (gpcd.eff_to_date)
FROM gbm_pp_component_details gpcd
WHERE gpcd.pp_acct_num = gpm.pp_acct_num
AND component_name = 'B'
AND is_deleted = 'N'
AND ( is_active = 'Y'
AND verified_status = 'Y'
))
AND ROWNUM = 1),
'', 0,
(SELECT NVL (SUM (component_family_value), 0)
FROM gbm_pp_component_details gpcd
WHERE gpcd.pp_acct_num = gpm.pp_acct_num
AND is_active = 'Y'
AND gpcd.component_name = 'B'
AND gpcd.verified_status = 'Y'
AND gpcd.eff_to_date =
(SELECT MAX (gpcd.eff_to_date)
FROM gbm_pp_component_details gpcd
WHERE gpcd.pp_acct_num = gpm.pp_acct_num
AND component_name = 'B'
AND is_deleted = 'N'
AND ( is_active = 'Y'
AND verified_status = 'Y'
))
AND ROWNUM = 1)
) normal_fp_value,
(DECODE (gpm.pp_type,
'0042', (DECODE (gpm.type_code,
'0006', (TO_CHAR
(TO_DATE ('31/12/2099',
'dd/mm/yyyy'
),
'YYYYMMDD'
)
),
''
)
),
''
)
) normal_to_date,
gpm.pay_scale pay_band, gpm.grade_pay grade_pay,
gpm.last_pay_drawn last_pay_drawn,
NVL
((SELECT NVL (SUM (DECODE (classification,
'E', (DECODE (comp_name,
'0531', comp_value_paid,
0
)
),
0
)
),
0
)
FROM gbm_pp_history_details gphd
WHERE gphd.pp_acct_num = gpm.pp_acct_num
AND gpm.verified_status <> 'T'
AND gphd.is_pp_comp_paid = 'Y'
AND gphd.is_valid = 'Y'
AND gphd.payment_for_month =
(SELECT MAX (gphd1.payment_for_month)
FROM gbm_pp_history_details gphd1
WHERE gphd1.pp_acct_num = gphd.pp_acct_num
AND gpm.verified_status <> 'T'
AND gphd.is_pp_comp_paid = 'Y'
AND gphd.is_valid = 'Y')),
0
) additional_allowance,
(NVL (DECODE (pay_commission (gpm.pp_acct_num),
'P006', (SELECT NVL
(DECODE
(gpm.pp_type,
'0041', component_pp_value,
'0042', component_family_value,
0
),
0
)
FROM gbm_pp_component_details gpcd
WHERE gpcd.pp_acct_num =
gpm.pp_acct_num
AND component_name = 'CAA'
AND is_deleted = 'N'
AND ( is_active = 'Y'
AND verified_status = 'Y'
)),
0
),
0
)
) constant_att_allowance,
DECODE
(pay_commission (gpm.pp_acct_num),
'P006', (DECODE (gpm.pp_type,
'0042', (DECODE
(gpm.type_code,
'0006', (DECODE
(gpm.conversion_date,
'', 'N',
'Y'
)
),
'N'
)
),
'N'
)
),
''
) death_in_harness,
NVL
((SELECT NVL (DECODE (gpm.pp_type,
'0041', component_pp_value,
'0042', component_family_value,
0
),
0
)
FROM gbm_pp_component_details gpcd
WHERE gpcd.pp_acct_num = gpm.pp_acct_num
AND component_name = 'MA'
AND is_deleted = 'N'
AND (is_active = 'Y' AND verified_status = 'Y')
AND gpcd.eff_to_date =
(SELECT MAX (gpcd.eff_to_date)
FROM gbm_pp_component_details gpcd
WHERE gpcd.pp_acct_num = gpm.pp_acct_num
AND component_name = 'B'
AND is_deleted = 'N'
AND ( is_active = 'Y'
AND verified_status = 'Y'
))
AND ROWNUM = 1),
0
) medical_allowance,
NVL
((SELECT NVL (SUM (pencomgra.amt), 0)
FROM gbm_pp_comm_gra_details pencomgra,
gbm_pp_component_master pencommast
WHERE pencomgra.pp_acct_num = gpm.pp_acct_num
AND pencommast.CATEGORY = gpm.pp_category
AND pencommast.component_id = pencomgra.TYPE
AND pencommast.classification = 'O'
AND pencomgra.TYPE = '0082'
AND pencomgra.verified_status = 'Y'
AND pencomgra.com_gra_is_paid = 'Y'),
0
) commutation_value,
gpm.ais_service_cadre ais_service_cadre,
gpm.ais_state_code ais_state_code,
gpm.ais_year_join ais_year_join,
SUBSTR (gpm.net_qualifying_service,
1,
2
) qualifying_service_year,
SUBSTR (gpm.net_qualifying_service,
3,
4
) qualifying_service_month,
SUBSTR (gpm.net_qualifying_service,
5,
6
) qualifying_service_day,
DECODE (gps.pp_status, 'I', 'Y', 'N') pp_status,
DECODE (gps.pp_status,
'I', TO_CHAR (gps.pp_status_date, 'YYYYMMDD')
) pp_status_date,
gpm.pp_acct_num, pay_commission (gpm.pp_acct_num)
FROM gbm_pp_master gpm, gbm_pp_status gps
WHERE gpm.tr_code = '0001'
AND gpm.sol_id IN (
SELECT a.child_sol_id
FROM gbm_sol_view s,
(SELECT DISTINCT child_sol_id
FROM gbm_hierarchy
WHERE module_id = '003'
AND sub_code = '0001'
AND child_level_id = '6'
START WITH sol_id = '1400'
AND level_id IN (
SELECT DISTINCT (glm.level_id)
FROM gbm_level_mst glm,
gbm_level_menu_map glmm,
gbm_level_sol gls,
gbm_hierarchy gh
WHERE glmm.menu_id =
'270'
AND glm.sub_code =
'0001'
AND gh.sol_id =
'1400'
AND gh.level_id =
glm.level_id
AND glmm.level_id =
glm.level_id
AND gls.level_id =
glm.level_id
UNION
SELECT DISTINCT (glm.level_id)
FROM gbm_level_mst glm,
gbm_level_menu_map glmm,
gbm_level_sol gls,
gbm_hierarchy gh
WHERE glmm.menu_id =
'270'
AND glm.sub_code =
'0001'
AND gh.child_sol_id =
'1400'
AND gh.child_level_id =
glm.level_id
AND glmm.level_id =
glm.level_id
AND gls.level_id =
glm.level_id)
CONNECT BY PRIOR child_sol_id = sol_id
AND PRIOR child_level_id = level_id
UNION
SELECT child_sol_id
FROM gbm_hierarchy
WHERE child_level_id = '6'
AND child_sol_id = '1400') a
WHERE a.child_sol_id = s.sol_id)
AND gpm.is_deleted = 'N'
AND gpm.verified_status = 'Y'
AND gps.verified_status = 'Y'
AND gps.pp_status NOT IN ('C')
AND gps.pp_status_date =
(SELECT MAX (pp_status_date)
FROM gbm_pp_status gps2
WHERE gps2.pp_acct_num = gpm.pp_acct_num
AND gps2.pp_acct_num = gps.pp_acct_num)
AND gpm.pp_acct_num = gps.pp_acct_num)

Report message to a moderator

Re: Query cost [message #537170 is a reply to message #537162]

Tue, 27 December 2011 01:32

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

For any peformances question, please read http://www.orafaq.com/forum/mv/msg/84315/433888/102589/#msg_433888 and post the required information.

Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

Regards
Michel

Report message to a moderator

Previous Topic:	How to Track Delay in Displaying data on application GUI
Next Topic:	Performance access direct table vs view

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 18 19:38:55 CDT 2024