| Not able to reduce the optimizer cost after creating index [message #383074] |
Tue, 27 January 2009 03:24  |
dhanamukesh
Messages: 51
Registered: January 2009
|
Member |
|
|
I have tried to reduce the optimizer cost of the particular query,in which i have created index in where clause columns.But the query is not going for index scan.Cost also very high.Please suggest me the tuning steps to tune this query.
For your reference ,i have attached sql,explain plan with this.
Regards,
Dhanalakshmi.P
|
|
|
|
|
|
| Re: Not able to reduce the optimizer cost after creating index [message #383086 is a reply to message #383074] |
Tue, 27 January 2009 04:08  |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
The query is SELECT a.name,
c.encryp_item_val,
pkg_pci_cmmn_decrypt.fcn_get_decrypt_val_aes('BAT',
e.reference_nbr,
e.order_date,
'RA') as card_nbr,
e.*
FROM express_pay_order e, transaction_encryp_item c, agent a
WHERE e.reference_nbr = c.tran_ref_id
and e.rcv_agent_id = a.agent_id
The explain plain shows using HASH JOIN with these source table cardinalities:
HASH JOIN
transaction_encryp_item : 8,391,418 rows
HASH JOIN
agent : 271,232 rows
express_pay_order : 8,420,620 rows
Just for your information, no index will enhance performance of this query as it does not filter any row.
Please, define "slowness". Are the number of rows in those tables (approximately) correct?
I see the biggest problem in calling the function PKG_PCI_CMMN_DECRYPT.FCN_GET_DECRYPT_VAL_AES. Although it may be fast not for a few rows, when repeated 8 million times, it may cause the query being slow.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
