Home » RDBMS Server » Performance Tuning » Not able to reduce the optimizer cost after creating index
Not able to reduce the optimizer cost after creating index [message #383074] Tue, 27 January 2009 03:24 Go to next message
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 #383076 is a reply to message #383074] Tue, 27 January 2009 03:28 Go to previous messageGo to next message
dhanamukesh
Messages: 51
Registered: January 2009
Member
hi,
Please find the explain plan as attached here for the query sent in my previous mail.



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 Go to previous message
flyboy
Messages: 1832
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.
Previous Topic: Going for full table scan
Next Topic: Multiple schemas against single schema - ERP
Goto Forum:
  


Current Time: Thu Dec 08 20:10:45 CST 2016

Total time taken to generate the page: 0.21502 seconds