What Indexes should be created for improve performance of the sql query
766877Apr 15 2010 — edited Apr 15 2010Hello Admins
One of my user is facing slow performance issue while running the below query. Can someone, please guide me for the same. I want to know, what indexes should be created to improve the performance of this query. Also what else can be done to achieve the same.
SQL Query:-
--------------------------------------------------------------------------------
SELECT UPPER(LTRIM(RTRIM(CGSBI_SHIP_DIST_S_EXTRACT.PO_NUMBER))),
CGSBI_SHIP_DIST_S_EXTRACT.PO_LINE_NUMBER,
CGSBI_SHIP_DIST_S_EXTRACT.PO_SHIPMENT_NUMBER,
CGSBI_SHIP_DIST_S_EXTRACT.PO_LINE_SHIP_DIST_NUMBER,
CGSBI_SHIP_DIST_S_EXTRACT.DISTRIBUTION_DATE,
UPPER(LTRIM(RTRIM(CGSBI_SHIP_DIST_S_EXTRACT.PO_LINE_SHIP_DIST_LINE_ID))),
UPPER(LTRIM(RTRIM(CGSBI_SHIP_DIST_S_EXTRACT.PROJECT_ID))),
UPPER(LTRIM(RTRIM(CGSBI_SHIP_DIST_S_EXTRACT.ACCOUNT_DISTRIBUTION_CODE))),
UPPER(LTRIM(RTRIM(CGSBI_SHIP_DIST_S_EXTRACT.ORACLE_ACCOUNT_NUMBER))),
UPPER(LTRIM(RTRIM(CGSBI_SHIP_DIST_S_EXTRACT.COMPONENT_CODE))),
UPPER(LTRIM(RTRIM(CGSBI_SHIP_DIST_S_EXTRACT.TRANSACTION_CURRENCY_CODE))),
CGSBI_SHIP_DIST_S_EXTRACT.ORDER_QUANTITY, UPPER(LTRIM(RTRIM(CGSBI_SHIP_DIST_S_EXTRACT.ORDER_UOM))),
CGSBI_SHIP_DIST_S_EXTRACT.UNIT_PRICE_TRX_CURRENCY,
UPPER(LTRIM(RTRIM(CGSBI_SHIP_DIST_S_EXTRACT.EXPENSE_TYPE_INDICATOR))),
CGSBI_SHIP_DIST_S_EXTRACT.SOR_ID,
UPPER(LTRIM(RTRIM(CGSBI_SHIP_DIST_S_EXTRACT.PO_LINE_ITEM_CODE))),
UPPER(LTRIM(RTRIM(CGSBI_SHIP_DIST_S_EXTRACT.PO_LINE_ITEM_DESC))),
CGSBI_SHIP_DIST_S_EXTRACT.PO_LINE_ITEM_LEAD_TIME,
UPPER(LTRIM(RTRIM(CGSBI_SHIP_DIST_S_EXTRACT.UNSPSC_CODE))),
UPPER(LTRIM(RTRIM(CGSBI_SHIP_DIST_S_EXTRACT.BUYER_ID))),
UPPER(LTRIM(RTRIM(CGSBI_SHIP_DIST_S_EXTRACT.REQUESTOR_ID))),
UPPER(LTRIM(RTRIM(CGSBI_SHIP_DIST_S_EXTRACT.APPROVER_ID))),
UPPER(LTRIM(RTRIM(CGSBI_SHIP_DIST_S_EXTRACT.SUPPLIER_SITE_ID))),
UPPER(LTRIM(RTRIM(CGSBI_SHIP_DIST_S_EXTRACT.SUPPLIER_GSL_NUMBER))),
UPPER(LTRIM(RTRIM(CGSBI_SHIP_DIST_S_EXTRACT.SHIP_TO_LOCATION_CODE))),
UPPER(LTRIM(RTRIM(CGSBI_SHIP_DIST_S_EXTRACT.TASK_ID))),
(LTRIM(RTRIM(CGSBI_SHIP_DIST_S_EXTRACT.PO_RELEASE_ID)))
FROM
CGSBI_SHIP_DIST_S_EXTRACT
WHERE PO_NUMBER IS NOT NULL;
----------------------------------------------------------------------------------------------
I generated the explain plan for this query and found the following:-
Explain Plan:-
---------------------------------------------------------------------
SQL> explain plan for SELECT UPPER(LTRIM(RTRIM(CGSBI_SHIP_DIST_S_EXTRACT.PO_NUMBER))),
2 CGSBI_SHIP_DIST_S_EXTRACT.PO_LINE_NUMBER,
3 CGSBI_SHIP_DIST_S_EXTRACT.PO_SHIPMENT_NUMBER,
4 CGSBI_SHIP_DIST_S_EXTRACT.PO_LINE_SHIP_DIST_NUMBER,
5 CGSBI_SHIP_DIST_S_EXTRACT.DISTRIBUTION_DATE,
6 UPPER(LTRIM(RTRIM(CGSBI_SHIP_DIST_S_EXTRACT.PO_LINE_SHIP_DIST_LINE_ID))),
7 UPPER(LTRIM(RTRIM(CGSBI_SHIP_DIST_S_EXTRACT.PROJECT_ID))),
8 UPPER(LTRIM(RTRIM(CGSBI_SHIP_DIST_S_EXTRACT.ACCOUNT_DISTRIBUTION_CODE))),
9 UPPER(LTRIM(RTRIM(CGSBI_SHIP_DIST_S_EXTRACT.ORACLE_ACCOUNT_NUMBER))),
10 UPPER(LTRIM(RTRIM(CGSBI_SHIP_DIST_S_EXTRACT.COMPONENT_CODE))),
11 UPPER(LTRIM(RTRIM(CGSBI_SHIP_DIST_S_EXTRACT.TRANSACTION_CURRENCY_CODE))),
12 CGSBI_SHIP_DIST_S_EXTRACT.ORDER_QUANTITY, UPPER(LTRIM(RTRIM(CGSBI_SHIP_DIST_S_EXTRACT.ORDER_UOM))),
13 CGSBI_SHIP_DIST_S_EXTRACT.UNIT_PRICE_TRX_CURRENCY,
14 UPPER(LTRIM(RTRIM(CGSBI_SHIP_DIST_S_EXTRACT.EXPENSE_TYPE_INDICATOR))),
15 CGSBI_SHIP_DIST_S_EXTRACT.SOR_ID,
16 UPPER(LTRIM(RTRIM(CGSBI_SHIP_DIST_S_EXTRACT.PO_LINE_ITEM_CODE))),
17 UPPER(LTRIM(RTRIM(CGSBI_SHIP_DIST_S_EXTRACT.PO_LINE_ITEM_DESC))),
18 CGSBI_SHIP_DIST_S_EXTRACT.PO_LINE_ITEM_LEAD_TIME,
19 UPPER(LTRIM(RTRIM(CGSBI_SHIP_DIST_S_EXTRACT.UNSPSC_CODE))),
20 UPPER(LTRIM(RTRIM(CGSBI_SHIP_DIST_S_EXTRACT.BUYER_ID))),
21 UPPER(LTRIM(RTRIM(CGSBI_SHIP_DIST_S_EXTRACT.REQUESTOR_ID))),
22 UPPER(LTRIM(RTRIM(CGSBI_SHIP_DIST_S_EXTRACT.APPROVER_ID))),
23 UPPER(LTRIM(RTRIM(CGSBI_SHIP_DIST_S_EXTRACT.SUPPLIER_SITE_ID))),
24 UPPER(LTRIM(RTRIM(CGSBI_SHIP_DIST_S_EXTRACT.SUPPLIER_GSL_NUMBER))),
25 UPPER(LTRIM(RTRIM(CGSBI_SHIP_DIST_S_EXTRACT.SHIP_TO_LOCATION_CODE))),
26 UPPER(LTRIM(RTRIM(CGSBI_SHIP_DIST_S_EXTRACT.TASK_ID))),
27 (LTRIM(RTRIM(CGSBI_SHIP_DIST_S_EXTRACT.PO_RELEASE_ID)))
28 FROM
29 CGSBI_SHIP_DIST_S_EXTRACT
30 WHERE PO_NUMBER IS NOT NULL;
Explained.
SQL>
SQL>
SQL> SELECT * FROM TABLE(dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------
Plan hash value: 3891180274
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 77647 | 39M| 2006 (1)| 00:00:25 |
|* 1 | TABLE ACCESS FULL| CGSBI_SHIP_DIST_S_EXTRACT | 77647 | 39M| 2006 (1)| 00:00:25 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("PO_NUMBER" IS NOT NULL)
13 rows selected.
SQL>
SQL>
-----------------------------------------------------------------------------------------
Kindly suggest on this...
Thanks & Regards
-Naveen Gangil
Oracle DBA