How can I improve performance of queries with window-functions, that seem to ignore indexes?  

Or, are there additional indexes I need to create?  

Or, can I eliminate self-joins from my final query?

I have a query with window functions, and I need to get it running smoothly.  

I could remove the window functions, and switch to `GROUP BY` - but I think that would be slower?

This query is used inside views, for external reports that are pulled frequently.  

In other words, this query is executed frequently by end-users, in some cases numerous times for large reports, and the end-user is directly impacted by the time this query takes to execute.  

Currently, the query executes pretty quickly as-is - but later on, I have to self-join this query to itself, and it slows to a crawl.

The underlying table has an index defined on every single column referenced.  

But when I `EXPLAIN` this query, it isn't using any of the indexes, and performs a full table scan instead.

The table currently has 28,000 rows, but that will increase over time (about 10,000 rows per day).

The `EDGE_VP`, `EDGE_RM`, and `EDGE_ASM` columns contain an e-mail address - string functions remove the domain.

```
SELECT DISTINCT SS_TIMESTAMP,
                CASE WHEN INSTR(EDGE_VP,'@oracle.com')=0 THEN EDGE_VP ELSE SUBSTR(EDGE_VP,1,INSTR(EDGE_VP,'@oracle.com')-1) END AS EDGE_VP,
                CASE WHEN INSTR(EDGE_RM,'@oracle.com')=0 THEN EDGE_RM ELSE SUBSTR(EDGE_RM,1,INSTR(EDGE_RM,'@oracle.com')-1) END AS EDGE_RM,
                CASE WHEN INSTR(EDGE_ASM,'@oracle.com')=0 THEN EDGE_ASM ELSE SUBSTR(EDGE_ASM,1,INSTR(EDGE_ASM,'@oracle.com')-1) END AS EDGE_ASM,
                NVL(SUM(CASE WHEN OPPTY_STATUS = 'Open' THEN ARR_PIPELINE END) OVER (PARTITION BY SS_TIMESTAMP, EDGE_VP, EDGE_RM, EDGE_ASM),0) AS PIPELINE,
                NVL(SUM(CASE WHEN OPPTY_STATUS = 'Open' THEN ARR_BEST END) OVER (PARTITION BY SS_TIMESTAMP, EDGE_VP, EDGE_RM, EDGE_ASM),0) AS BEST,
                NVL(SUM(CASE WHEN OPPTY_STATUS = 'Open' THEN ARR_FORECAST END) OVER (PARTITION BY SS_TIMESTAMP, EDGE_VP, EDGE_RM, EDGE_ASM),0) AS FORECAST,
                NVL(SUM(CASE WHEN OPPTY_STATUS = 'Won' THEN ARR END) OVER (PARTITION BY SS_TIMESTAMP, EDGE_VP, EDGE_RM, EDGE_ASM),0) AS CLOSED,
                COUNT(*) OVER (PARTITION BY SS_TIMESTAMP, EDGE_VP, EDGE_RM, EDGE_ASM) AS ROW_COUNT
FROM SS_EDGE_FORECAST
WHERE EDGE_ASM NOT IN('Email_Address1', 'Email_Address2')

```

**CLICK IMAGES TO ENLARGE:**

[![enter image description here](https://i.stack.imgur.com/bc7TC.png)](https://i.stack.imgur.com/bc7TC.png)

[![enter image description here](https://i.stack.imgur.com/QeRLI.png)](https://i.stack.imgur.com/QeRLI.png)

Here's the expanded query I'm using later on.  

**Notice the query at the top, in the `WITH` clause.**  

At the end of this query, I am currently using self-joins of the `WITH` query - maybe I should use window-functions here as well?

I can't tell you how long this takes to execute, because it hangs when I try to run it, and I don't have patience to wait for hours etc.

```
CREATE OR REPLACE FORCE VIEW "EDGE_FORECAST_OVER_TIME" AS
WITH basequery AS (SELECT DISTINCT SS_TIMESTAMP,
                                   CASE WHEN INSTR(EDGE_VP,'@oracle.com')=0 THEN EDGE_VP ELSE SUBSTR(EDGE_VP,1,INSTR(EDGE_VP,'@oracle.com')-1) END AS EDGE_VP,
                                   CASE WHEN INSTR(EDGE_RM,'@oracle.com')=0 THEN EDGE_RM ELSE SUBSTR(EDGE_RM,1,INSTR(EDGE_RM,'@oracle.com')-1) END AS EDGE_RM,
                                   CASE WHEN INSTR(EDGE_ASM,'@oracle.com')=0 THEN EDGE_ASM ELSE SUBSTR(EDGE_ASM,1,INSTR(EDGE_ASM,'@oracle.com')-1) END AS EDGE_ASM,
                                   NVL(SUM(CASE WHEN OPPTY_STATUS = 'Open' THEN ARR_PIPELINE END) OVER (PARTITION BY SS_TIMESTAMP, EDGE_VP, EDGE_RM, EDGE_ASM),0) AS PIPELINE,
                                   NVL(SUM(CASE WHEN OPPTY_STATUS = 'Open' THEN ARR_BEST END) OVER (PARTITION BY SS_TIMESTAMP, EDGE_VP, EDGE_RM, EDGE_ASM),0) AS BEST,
                                   NVL(SUM(CASE WHEN OPPTY_STATUS = 'Open' THEN ARR_FORECAST END) OVER (PARTITION BY SS_TIMESTAMP, EDGE_VP, EDGE_RM, EDGE_ASM),0) AS FORECAST,
                                   NVL(SUM(CASE WHEN OPPTY_STATUS = 'Won' THEN ARR END) OVER (PARTITION BY SS_TIMESTAMP, EDGE_VP, EDGE_RM, EDGE_ASM),0) AS CLOSED,
                                   COUNT(*) OVER (PARTITION BY SS_TIMESTAMP, EDGE_VP, EDGE_RM, EDGE_ASM) AS ROW_COUNT
                   FROM SS_EDGE_FORECAST
                   WHERE EDGE_ASM NOT IN('Email_Address1', 'Email_Address2'))
SELECT ss.TIMESTAMP,
       ss.TIMESTAMP_DATE,
       ss.FREQUENCY,
       ss.PREV_TIMESTAMP,
       ss.PREV_F_TIMESTAMP,
       ss.PREV_H_TIMESTAMP,
       ss.PREV_D_TIMESTAMP,
       ss.PREV_W_TIMESTAMP,
       ss.PREV_M_TIMESTAMP,
       ss.PREV_Q_TIMESTAMP,
       ss.PREV_Y_TIMESTAMP,
       ss.PREV_TIMESTAMP_DATE,
       ss.PREV_F_TIMESTAMP_DATE,
       ss.PREV_H_TIMESTAMP_DATE,
       ss.PREV_D_TIMESTAMP_DATE,
       ss.PREV_W_TIMESTAMP_DATE,
       ss.PREV_M_TIMESTAMP_DATE,
       ss.PREV_Q_TIMESTAMP_DATE,
       ss.PREV_Y_TIMESTAMP_DATE,
       ss.DAYS_SINCE_PREV_TIMESTAMP,
       ss.DAYS_SINCE_PREV_F_TIMESTAMP,
       ss.DAYS_SINCE_PREV_H_TIMESTAMP,
       ss.DAYS_SINCE_PREV_D_TIMESTAMP,
       ss.DAYS_SINCE_PREV_W_TIMESTAMP,
       ss.DAYS_SINCE_PREV_M_TIMESTAMP,
       ss.DAYS_SINCE_PREV_Q_TIMESTAMP,
       ss.DAYS_SINCE_PREV_Y_TIMESTAMP,
       ss.DAYS_SINCE_PREV_TS_DATE,
       ss.DAYS_SINCE_PREV_F_TS_DATE,
       ss.DAYS_SINCE_PREV_H_TS_DATE,
       ss.DAYS_SINCE_PREV_D_TS_DATE,
       ss.DAYS_SINCE_PREV_W_TS_DATE,
       ss.DAYS_SINCE_PREV_M_TS_DATE,
       ss.DAYS_SINCE_PREV_Q_TS_DATE,
       ss.DAYS_SINCE_PREV_Y_TS_DATE,

       bq.EDGE_VP,
       bq.EDGE_RM,
       bq.EDGE_ASM,

       bq.PIPELINE,
       bq.BEST,
       bq.FORECAST,
       bq.CLOSED,
       bq.PIPELINE + bq.BEST AS PIPE_BEST,
       bq.CLOSED + bq.FORECAST AS CLOSED_FORECAST,

       bqp.PIPELINE AS PREV_PIPELINE,
       bqp.BEST AS PREV_BEST,
       bqp.FORECAST AS PREV_FORECAST,
       bqp.CLOSED AS PREV_CLOSED,
       bqp.PIPELINE + bqp.BEST AS PREV_PIPE_BEST,
       bqp.CLOSED + bqp.FORECAST AS PREV_CLOSED_FORECAST,
       bq.PIPELINE - bqp.PIPELINE AS PIPELINE_DIFF,
       bq.BEST - bqp.BEST AS BEST_DIFF,
       bq.FORECAST - bqp.FORECAST AS FORECAST_DIFF,
       bq.CLOSED - bqp.CLOSED AS CLOSED_DIFF,
       (bq.PIPELINE + bq.BEST) - (bqp.PIPELINE + bqp.BEST) AS PIPE_BEST_DIFF,
       (bq.CLOSED + bq.FORECAST) - (bqp.CLOSED + bqp.FORECAST) AS CLOSED_FORECAST_DIFF,

       bqpf.PIPELINE AS PREV_F_PIPELINE,
       bqpf.BEST AS PREV_F_BEST,
       bqpf.FORECAST AS PREV_F_FORECAST,
       bqpf.CLOSED AS PREV_F_CLOSED,
       bqpf.PIPELINE + bqpf.BEST AS PREV_F_PIPE_BEST,
       bqpf.CLOSED + bqpf.FORECAST AS PREV_F_CLOSED_FORECAST,
       bq.PIPELINE - bqpf.PIPELINE AS F_PIPELINE_DIFF,
       bq.BEST - bqpf.BEST AS F_BEST_DIFF,
       bq.FORECAST - bqpf.FORECAST AS F_FORECAST_DIFF,
       bq.CLOSED - bqpf.CLOSED AS F_CLOSED_DIFF,
       (bq.PIPELINE + bq.BEST) - (bqpf.PIPELINE + bqpf.BEST) AS F_PIPE_BEST_DIFF,
       (bq.CLOSED + bq.FORECAST) - (bqpf.CLOSED + bqpf.FORECAST) AS F_CLOSED_FORECAST_DIFF,

       bqph.PIPELINE AS PREV_H_PIPELINE,
       bqph.BEST AS PREV_H_BEST,
       bqph.FORECAST AS PREV_H_FORECAST,
       bqph.CLOSED AS PREV_H_CLOSED,
       bqph.PIPELINE + bqph.BEST AS PREV_H_PIPE_BEST,
       bqph.CLOSED + bqph.FORECAST AS PREV_H_CLOSED_FORECAST,
       bq.PIPELINE - bqph.PIPELINE AS H_PIPELINE_DIFF,
       bq.BEST - bqph.BEST AS H_BEST_DIFF,
       bq.FORECAST - bqph.FORECAST AS H_FORECAST_DIFF,
       bq.CLOSED - bqph.CLOSED AS H_CLOSED_DIFF,
       (bq.PIPELINE + bq.BEST) - (bqph.PIPELINE + bqph.BEST) AS H_PIPE_BEST_DIFF,
       (bq.CLOSED + bq.FORECAST) - (bqph.CLOSED + bqph.FORECAST) AS H_CLOSED_FORECAST_DIFF,

       bqpd.PIPELINE AS PREV_D_PIPELINE,
       bqpd.BEST AS PREV_D_BEST,
       bqpd.FORECAST AS PREV_D_FORECAST,
       bqpd.CLOSED AS PREV_D_CLOSED,
       bqpd.PIPELINE + bqpd.BEST AS PREV_D_PIPE_BEST,
       bqpd.CLOSED + bqpd.FORECAST AS PREV_D_CLOSED_FORECAST,
       bq.PIPELINE - bqpd.PIPELINE AS D_PIPELINE_DIFF,
       bq.BEST - bqpd.BEST AS D_BEST_DIFF,
       bq.FORECAST - bqpd.FORECAST AS D_FORECAST_DIFF,
       bq.CLOSED - bqpd.CLOSED AS D_CLOSED_DIFF,
       (bq.PIPELINE + bq.BEST) - (bqpd.PIPELINE + bqpd.BEST) AS D_PIPE_BEST_DIFF,
       (bq.CLOSED + bq.FORECAST) - (bqpd.CLOSED + bqpd.FORECAST) AS D_CLOSED_FORECAST_DIFF,

       bqpw.PIPELINE AS PREV_W_PIPELINE,
       bqpw.BEST AS PREV_W_BEST,
       bqpw.FORECAST AS PREV_W_FORECAST,
       bqpw.CLOSED AS PREV_W_CLOSED,
       bqpw.PIPELINE + bqpw.BEST AS PREV_W_PIPE_BEST,
       bqpw.CLOSED + bqpw.FORECAST AS PREV_W_CLOSED_FORECAST,
       bq.PIPELINE - bqpw.PIPELINE AS W_PIPELINE_DIFF,
       bq.BEST - bqpw.BEST AS W_BEST_DIFF,
       bq.FORECAST - bqpw.FORECAST AS W_FORECAST_DIFF,
       bq.CLOSED - bqpw.CLOSED AS W_CLOSED_DIFF,
       (bq.PIPELINE + bq.BEST) - (bqpw.PIPELINE + bqpw.BEST) AS W_PIPE_BEST_DIFF,
       (bq.CLOSED + bq.FORECAST) - (bqpw.CLOSED + bqpw.FORECAST) AS W_CLOSED_FORECAST_DIFF,

       bqpm.PIPELINE AS PREV_M_PIPELINE,
       bqpm.BEST AS PREV_M_BEST,
       bqpm.FORECAST AS PREV_M_FORECAST,
       bqpm.CLOSED AS PREV_M_CLOSED,
       bqpm.PIPELINE + bqpm.BEST AS PREV_M_PIPE_BEST,
       bqpm.CLOSED + bqpm.FORECAST AS PREV_M_CLOSED_FORECAST,
       bq.PIPELINE - bqpm.PIPELINE AS M_PIPELINE_DIFF,
       bq.BEST - bqpm.BEST AS M_BEST_DIFF,
       bq.FORECAST - bqpm.FORECAST AS M_FORECAST_DIFF,
       bq.CLOSED - bqpm.CLOSED AS M_CLOSED_DIFF,
       (bq.PIPELINE + bq.BEST) - (bqpm.PIPELINE + bqpm.BEST) AS M_PIPE_BEST_DIFF,
       (bq.CLOSED + bq.FORECAST) - (bqpm.CLOSED + bqpm.FORECAST) AS M_CLOSED_FORECAST_DIFF,

       bqpq.PIPELINE AS PREV_Q_PIPELINE,
       bqpq.BEST AS PREV_Q_BEST,
       bqpq.FORECAST AS PREV_Q_FORECAST,
       bqpq.CLOSED AS PREV_Q_CLOSED,
       bqpq.PIPELINE + bqpq.BEST AS PREV_Q_PIPE_BEST,
       bqpq.CLOSED + bqpq.FORECAST AS PREV_Q_CLOSED_FORECAST,
       bq.PIPELINE - bqpq.PIPELINE AS Q_PIPELINE_DIFF,
       bq.BEST - bqpq.BEST AS Q_BEST_DIFF,
       bq.FORECAST - bqpq.FORECAST AS Q_FORECAST_DIFF,
       bq.CLOSED - bqpq.CLOSED AS Q_CLOSED_DIFF,
       (bq.PIPELINE + bq.BEST) - (bqpq.PIPELINE + bqpq.BEST) AS Q_PIPE_BEST_DIFF,
       (bq.CLOSED + bq.FORECAST) - (bqpq.CLOSED + bqpq.FORECAST) AS Q_CLOSED_FORECAST_DIFF,

       bqpy.PIPELINE AS PREV_Y_PIPELINE,
       bqpy.BEST AS PREV_Y_BEST,
       bqpy.FORECAST AS PREV_Y_FORECAST,
       bqpy.CLOSED AS PREV_Y_CLOSED,
       bqpy.PIPELINE + bqpy.BEST AS PREV_Y_PIPE_BEST,
       bqpy.CLOSED + bqpy.FORECAST AS PREV_Y_CLOSED_FORECAST,
       bq.PIPELINE - bqpy.PIPELINE AS Y_PIPELINE_DIFF,
       bq.BEST - bqpy.BEST AS Y_BEST_DIFF,
       bq.FORECAST - bqpy.FORECAST AS Y_FORECAST_DIFF,
       bq.CLOSED - bqpy.CLOSED AS Y_CLOSED_DIFF,
       (bq.PIPELINE + bq.BEST) - (bqpy.PIPELINE + bqpy.BEST) AS Y_PIPE_BEST_DIFF,
       (bq.CLOSED + bq.FORECAST) - (bqpy.CLOSED + bqpy.FORECAST) AS Y_CLOSED_FORECAST_DIFF,

       bq.ROW_COUNT,
       bqp.ROW_COUNT AS PREV_ROW_COUNT,
       bq.ROW_COUNT - bqp.ROW_COUNT AS NET_ROWS_ADDED

FROM basequery bq
LEFT JOIN SNAPSHOTS ss ON ss.TIMESTAMP = bq.SS_TIMESTAMP AND ss.TABLE_NAME = 'EDGE_FORECAST'

LEFT JOIN basequery bqp ON bqp.SS_TIMESTAMP = ss.PREV_TIMESTAMP
                       AND bqp.EDGE_VP = bq.EDGE_VP
                       AND bqp.EDGE_RM = bq.EDGE_RM
                       AND bqp.EDGE_ASM = bq.EDGE_ASM

LEFT JOIN basequery bqpf ON bqp.SS_TIMESTAMP = ss.PREV_F_TIMESTAMP
                        AND bqp.EDGE_VP = bq.EDGE_VP
                        AND bqp.EDGE_RM = bq.EDGE_RM
                        AND bqp.EDGE_ASM = bq.EDGE_ASM

LEFT JOIN basequery bqph ON bqp.SS_TIMESTAMP = ss.PREV_H_TIMESTAMP
                        AND bqp.EDGE_VP = bq.EDGE_VP
                        AND bqp.EDGE_RM = bq.EDGE_RM
                        AND bqp.EDGE_ASM = bq.EDGE_ASM

LEFT JOIN basequery bqpd ON bqp.SS_TIMESTAMP = ss.PREV_D_TIMESTAMP
                        AND bqp.EDGE_VP = bq.EDGE_VP
                        AND bqp.EDGE_RM = bq.EDGE_RM
                        AND bqp.EDGE_ASM = bq.EDGE_ASM

LEFT JOIN basequery bqpw ON bqp.SS_TIMESTAMP = ss.PREV_W_TIMESTAMP
                        AND bqp.EDGE_VP = bq.EDGE_VP
                        AND bqp.EDGE_RM = bq.EDGE_RM
                        AND bqp.EDGE_ASM = bq.EDGE_ASM

LEFT JOIN basequery bqpm ON bqp.SS_TIMESTAMP = ss.PREV_M_TIMESTAMP
                        AND bqp.EDGE_VP = bq.EDGE_VP
                        AND bqp.EDGE_RM = bq.EDGE_RM
                        AND bqp.EDGE_ASM = bq.EDGE_ASM

LEFT JOIN basequery bqpq ON bqp.SS_TIMESTAMP = ss.PREV_Q_TIMESTAMP
                        AND bqp.EDGE_VP = bq.EDGE_VP
                        AND bqp.EDGE_RM = bq.EDGE_RM
                        AND bqp.EDGE_ASM = bq.EDGE_ASM

LEFT JOIN basequery bqpy ON bqp.SS_TIMESTAMP = ss.PREV_Y_TIMESTAMP
                        AND bqp.EDGE_VP = bq.EDGE_VP
                        AND bqp.EDGE_RM = bq.EDGE_RM
                        AND bqp.EDGE_ASM = bq.EDGE_ASM

ORDER BY ss.TIMESTAMP DESC,
         bq.EDGE_VP ASC,
         bq.EDGE_RM ASC,
         bq.EDGE_ASM ASC

```