Create index on View

have the following query which is very costly

SELECT
                custno
              , stockto
              , MAX(rad) "LAST_RAD"
            FROM xxx_ess_scp_ib_cap_summary
            WHERE 1=1
            GROUP BY
                custno
              , stockto

now xxx_ess_scp_ib_cap_summary is a view
can i create a clusteded index on a view
pardeshirahulAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
>>can i create a clusteded index on a view

No.  Did you try?

If you create a Materialized View, you can.

You have no where clause so even if you could create an index, it wouldn't be used.
0
 
Pawan KumarDatabase ExpertCommented:
Assuming you must be having customer master table.
Use below approach. Dont go for view.

--

SELECT
                custno
              , stockto
			  , y.LAST_RAD
FROM 
Customer c
Cross apply
(
            SELECT MAX(rad) "LAST_RAD"
            FROM xxx_ess_scp_ib_cap_summary s
            WHERE s.custno = c.custno
			and s.stockto = c.stockto
)y


--

Open in new window



Enjoy !!
1
 
pardeshirahulAuthor Commented:
This is my  select

SELECT
        b.custno
      , b.stockto
      , TO_CHAR(a.dateholder, 'DY')                                 "DAYNAME"
      , TO_CHAR(a.dateholder, 'IYYY') || TO_CHAR(a.dateholder, 'IW') "WEEKNO"
      , a.dateholder                                                "RAD"
      , 0                                                           "TDY_LOAD_CNT"
      , NVL(TO_NUMBER(c.avail), 0)                                  "TDY_CAP"
      , DECODE (TRUNC (a.dateholder),  TRUNC (SYSDATE), jms_ess_scp_ib_cap_prior (b.custno),  NULL)   "PRIOR_LOAD_CNT"
FROM (SELECT
                TRUNC(SYSDATE) - 1 + LEVEL AS dateholder
      FROM DUAL
      CONNECT BY LEVEL <= 180
      ) a
INNER JOIN (SELECT
                custno
              , stockto
              , MAX(rad) "LAST_RAD"
            FROM xxx_ess_scp_ib_cap_summary
            WHERE 1=1
            GROUP BY
                custno
              , stockto
                                      ) b ON a.dateholder <= b.last_rad
LEFT JOIN (SELECT
                cal
              , (eff / 1440 + TO_DATE('01011970', 'MMDDYYYY')) "EFFDATE"
              , avail
            FROM caldata
            ) c ON a.dateholder = c.effdate and (b.custno || '-INCAP') = c.cal
ORDER BY 5
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
Pawan KumarDatabase ExpertCommented:
Do you have customer table in your db?
0
 
pardeshirahulAuthor Commented:
its a view in Oracle
0
 
Pawan KumarDatabase ExpertCommented:
try this..

SELECT
        b.custno
      , b.stockto
      , TO_CHAR(a.dateholder, 'DY')                                 "DAYNAME"
      , TO_CHAR(a.dateholder, 'IYYY') || TO_CHAR(a.dateholder, 'IW') "WEEKNO"
      , a.dateholder                                                "RAD"
      , 0                                                           "TDY_LOAD_CNT"
      , NVL(TO_NUMBER(c.avail), 0)                                  "TDY_CAP"
      , DECODE (TRUNC (a.dateholder),  TRUNC (SYSDATE), jms_ess_scp_ib_cap_prior (b.custno),  NULL)   "PRIOR_LOAD_CNT"
FROM (SELECT
                TRUNC(SYSDATE) - 1 + LEVEL AS dateholder
      FROM DUAL
      CONNECT BY LEVEL <= 180
      ) a
INNER JOIN (
			   SELECT
                custno
              , stockto
			  , y.LAST_RAD
				FROM 
				Customer c
				Cross apply
				(
							SELECT MAX(rad) "LAST_RAD"
							FROM xxx_ess_scp_ib_cap_summary s
							WHERE s.custno = c.custno
							and s.stockto = c.stockto
				)y
			) b ON a.dateholder <= b.last_rad
LEFT JOIN (SELECT
                cal
              , (eff / 1440 + TO_DATE('01011970', 'MMDDYYYY')) "EFFDATE"
              , avail
            FROM caldata
            ) c ON a.dateholder = c.effdate and (b.custno || '-INCAP') = c.cal

Open in new window

0
 
pardeshirahulAuthor Commented:
Execution (19: 33): ORA-00905: missing keyword
0
 
pardeshirahulAuthor Commented:
on APPLY
0
 
pardeshirahulAuthor Commented:
what is this Customer c
0
 
slightwv (䄆 Netminder) Commented:
This question was if you can create an index on a view:  That answer is no.

If you are now asking for help in tuning this query, please continue that in the question where you asked for tuning help:
https://www.experts-exchange.com/questions/28970058/how-to-tune-this-sql-query.html

>>Execution (19: 33): ORA-00905: missing keyword

Cross Apply is new in 12c.  If you aren't on 12c, it won't work.

>>what is this Customer c

They assumed you have a customer table in the database to join against.
0
 
pardeshirahulAuthor Commented:
okay we have 11g database i understood sorry for the confusion
0
 
Mark GeerlingsDatabase AdministratorCommented:
"can i create ... index on a view [in Oracle]"?
No.  Oracle supports indexes on tables, not on views.

What do you mean by "clustered index"?  That is not an Oracle term.  Oracle supports multi-column indexes (on any table with multiple columns).  Is that what you mean by "clustered index"?

This "where" clause is totally useless in production code:
 WHERE 1=1

Yes, I understand that in the development process, including this line as the first line of a multi-line "where" clause makes it easy to comment out any or all other lines in the "where" clause and still have a legal query.  But that is the *ONLY* value this line has:  just a small convenience for testing.  This line does not belong in production code.

And yes, this looks like a continuation of your earlier question:
https://www.experts-exchange.com/questions/28970058/how-to-tune-this-sql-query.html

But, now you've closed that question.
0
 
pardeshirahulAuthor Commented:
yes i will close Thanks
0
 
slightwv (䄆 Netminder) Commented:
If you wish to continue your previous question, I can reopen it.  Let me know.
0
 
pardeshirahulAuthor Commented:
okay
0
 
GhunaimaCommented:
It seems that your inner join is missing some conditions if you want to compare does of first table with max does in view against same custno & stock no add stockto and customer no in ON condition as mentioned below

INNER JOIN (SELECT
                custno
              , stockto
              , MAX(rad) "LAST_RAD"
            FROM xxx_ess_scp_ib_cap_summary
            WHERE 1=1
            GROUP BY
                custno
              , stockto
                                      ) b ON a.dateholder <= b.last_rad
and a.custno=b.custom and a.stockto=b.stockto
0
 
pardeshirahulAuthor Commented:
if i write this 2 seperate union the query is running in 24 minutes

SELECT a.custno,
            a.stockto,
            a.dayname,
            TO_CHAR (a.rad, 'IYYY') || TO_CHAR (a.rad, 'IW') "WEEKNO",
            a.rad,
            --            COUNT (NVL (a.load_number_last, a.cono)) "TDY_LOAD_CNT",
            SUM (a.load_count) "TDY_LOAD_CNT",
            NVL (TO_NUMBER (b.avail), 0) "TDY_CAP",
            DECODE (TRUNC (a.rad),
                    TRUNC (SYSDATE), jms_ess_scp_ib_cap_prior (a.custno),
                    NULL)
               "PRIOR_LOAD_CNT"
       FROM xxx_ess_scp_ib_cap_summary a,
            (SELECT cal,
                    (eff / 1440 + TO_DATE ('01011970', 'MMDDYYYY')) "EFFDATE",
                    avail
               FROM caldata) b
      WHERE     a.rad >= SYSDATE
            AND a.rad = b.effdate(+)
            AND (a.custno || '-INCAP') = b.cal(+)
   GROUP BY a.custno,
            a.stockto,
            a.rad,
            a.weekno,
            a.dayname,
            b.avail
   UNION ALL
   -- Dummy Data
   SELECT b.custno,
          b.stockto,
          TO_CHAR (a.dateholder, 'DY') "DAYNAME",
          TO_CHAR (a.dateholder, 'IYYY') || TO_CHAR (a.dateholder, 'IW')
             "WEEKNO",
          a.dateholder "RAD",
          0 "TDY_LOAD_CNT",
          NVL (TO_NUMBER (c.avail), 0) "TDY_CAP",
          DECODE (TRUNC (a.dateholder),
                  TRUNC (SYSDATE), jms_ess_scp_ib_cap_prior (b.custno),
                  NULL)
             "PRIOR_LOAD_CNT"
     FROM (    SELECT TRUNC (SYSDATE) - 1 + LEVEL AS dateholder
                 FROM DUAL
           CONNECT BY LEVEL <= 180) a
          INNER JOIN (  SELECT custno, stockto, MAX (rad) "LAST_RAD"
                          FROM xxx_ess_scp_ib_cap_summary
                         WHERE 1 = 1
                      GROUP BY custno, stockto
                        HAVING COUNT (*) >1
                        ) b
             ON a.dateholder <= b.last_rad
          LEFT JOIN
          (SELECT cal,
                  (eff / 1440 + TO_DATE ('01011970', 'MMDDYYYY')) "EFFDATE",
                  avail
             FROM caldata) c
             ON a.dateholder = c.effdate AND (b.custno || '-INCAP') = c.cal
   UNION ALL
   -- Dummy Data
   SELECT b.custno,
          b.stockto,
          TO_CHAR (a.dateholder, 'DY') "DAYNAME",
          TO_CHAR (a.dateholder, 'IYYY') || TO_CHAR (a.dateholder, 'IW')
             "WEEKNO",
          a.dateholder "RAD",
          0 "TDY_LOAD_CNT",
          NVL (TO_NUMBER (c.avail), 0) "TDY_CAP",
          DECODE (TRUNC (a.dateholder),
                  TRUNC (SYSDATE), jms_ess_scp_ib_cap_prior (b.custno),
                  NULL)
             "PRIOR_LOAD_CNT"
     FROM (    SELECT TRUNC (SYSDATE) - 1 + LEVEL AS dateholder
                 FROM DUAL
           CONNECT BY LEVEL <= 180) a
          INNER JOIN (  SELECT custno, stockto, MAX (rad) "LAST_RAD"
                          FROM xxx_ess_scp_ib_cap_summary
                         WHERE 1 = 1
                      GROUP BY custno, stockto
                        HAVING COUNT (*) = 1) b
             ON a.dateholder <= b.last_rad
          LEFT JOIN
          (SELECT cal,
                  (eff / 1440 + TO_DATE ('01011970', 'MMDDYYYY')) "EFFDATE",
                  avail
             FROM caldata) c
             ON a.dateholder = c.effdate AND (b.custno || '-INCAP') = c.cal
   ORDER BY 5;
0
 
pardeshirahulAuthor Commented:
HAVING COUNT (*) = 1

and
HAVING COUNT (*) = > 1
0
 
pardeshirahulAuthor Commented:
sorry i mean

HAVING COUNT (*) = 1

HAVING COUNT (*) > 1
0
 
pardeshirahulAuthor Commented:
okay sorry
0
 
PortletPaulfreelancerCommented:
The view should be avoided therefore the index on that view is not needed.

Use only the one or two SOURCE TABLES needed for your query.
0
 
slightwv (䄆 Netminder) Commented:
The other question was query tuning.

This question was:
can i create a clusteded index on a view

To which the answer was given in the first post #a41818139 :  No.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.