StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POfunction based index
text
Body
copied!<pre><code>select <here I have functions like to_char, nvl, rtrim, ltrim, sum, decode> from table1 table2 where joining conditions 1 joining conditions 2 group by <here I have functions like to_char, nvl, rtrim, ltrim, sum, decode> </code></pre> <p>I got this query from production and looking at it need to provide few solutions to tune, I m thinking of using function based inbex for group by columns. I think select columns need not be index. I will get enviornment in couple of days but before that I need to come up with different apporaches. What all things I need to check if function by index is useful? Also, apart from explain plan which other documents I need to ask from DBAs?</p> <p>I m adding actual sql here, I have asked for explain plan, which I will get in sometime :- </p> <pre><code>SELECT D_E_TRADE.DATE_VALUE, to_char(D_E_TRADE.DATE_VALUE,'Mon-yyyy'), NVL(P_DIM.P_NAME,' '), rtrim(ltrim(P_DIM.C_CTRY)), D_E_TRADE.YEAR, L_E_DIM.L_CODE, NVL(D_DIM.DESCR,' '), ( decode(D_DIM.DEPT_ID,'-1',' ',D_DIM.DEPT_ID) ), sum(A_CGE.TOTAL_CALC_NET_FEES), L_E_DIM.L_NAME, decode(A_CGE.E_M_CENTER,-9,0,A_CGE.E_M_CENTER), NVL(F_DIM.S_DESC,'-1'), sum(A_CGE.C_TOTAL_SHARES) FROM DATE_D D_E_TRADE, P_DIM, L_E_DIM, D_DIM, A_CGE, F_DIM WHERE ( D_E_TRADE.DATE_KEY=A_CGE.T_KEY ) AND ( P_DIM.PARTY_KEY=A_CGE.E_P_KEY ) AND ( F_DIM.F_T_KEY=A_CGE.F_T_KEY ) AND ( L_E_DIM.L_E_KEY=A_CGE.L_E_KEY ) AND ( D_DIM.DEPT_KEY=A_CGE.DEPT_KEY ) AND ( rtrim(ltrim(P_DIM.C_CTRY)) = 'AC' AND ( A_CGE.T_KEY >= (SELECT DATE_D_PROMPTS.DATE_KEY FROM DATE_D DATE_D_PROMPTS WHERE ( DATE_D_PROMPTS.DATE_VALUE = '01-01-2012 00:00:00' ) ) AND A_CGE.T_KEY <= (SELECT DATE_D_PROMPTS.DATE_KEY FROM DATE_D DATE_D_PROMPTS WHERE ( DATE_D_PROMPTS.DATE_VALUE = '31-08-2012 00:00:00' )) AND A_CGE.TRANS_REGION_KEY IN (SELECT REGION_KEY FROM REGION_DIM WHERE REGION_DIM.REGION_NAME IN ('Americas') ) ) AND ( A_CGE.T_KEY >= (SELECT DATE_D_PROMPTS.DATE_KEY FROM DATE_D DATE_D_PROMPTS WHERE ( DATE_D_PROMPTS.DATE_VALUE = '01-01-2012 00:00:00' ) ) AND A_CGE.T_KEY <= (SELECT DATE_D_PROMPTS.DATE_KEY FROM DATE_D DATE_D_PROMPTS WHERE ( DATE_D_PROMPTS.DATE_VALUE = '31-08-2012 00:00:00' )) AND A_CGE.TRANS_REGION_KEY IN (SELECT REGION_KEY FROM REGION_DIM WHERE REGION_DIM.REGION_NAME IN ('Americas') ) ) AND ( 'All Fees' IN ('2 - E','3 - P','4 - F','5 - C,') OR A_CGE.F_T_KEY IN (SELECT F_T_KEY FROM F_DIM WHERE (F_DIM.s_id ) || ' - ' || ( F_DIM.CHARGE_LVL1_NAME ) IN ('2 - E','3 - P','4 - F','5 - C')) ) ) GROUP BY D_E_TRADE.DATE_VALUE, to_char(D_E_TRADE.DATE_VALUE,'Mon-yyyy'), NVL(P_DIM.P_NAME,' '), rtrim(ltrim(P_DIM.C_CTRY)), D_E_TRADE.YEAR, L_E_DIM.L_CODE, NVL(D_DIM.DESCR,' '), ( decode(D_DIM.DEPT_ID,'-1',' ',D_DIM.DEPT_ID) ), L_E_DIM.L_NAME, decode(A_CGE.E_M_CENTER,-9,0,A_CGE.E_M_CENTER), NVL(F_DIM.S_DESC,'-1') </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload