Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Create index on View

Posted on 2016-09-27
27
Medium Priority
?
82 Views
Last Modified: 2016-09-30
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
0
Comment
Question by:pardeshirahul
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 4
  • 3
  • +3
27 Comments
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 41818139
>>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
 
LVL 30

Expert Comment

by:Pawan Kumar
ID: 41818164
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
 

Author Comment

by:pardeshirahul
ID: 41818172
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 free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 30

Expert Comment

by:Pawan Kumar
ID: 41818186
Do you have customer table in your db?
0
 

Author Comment

by:pardeshirahul
ID: 41818194
its a view in Oracle
0
 
LVL 30

Expert Comment

by:Pawan Kumar
ID: 41818201
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
 

Author Comment

by:pardeshirahul
ID: 41818205
Execution (19: 33): ORA-00905: missing keyword
0
 

Author Comment

by:pardeshirahul
ID: 41818206
on APPLY
0
 

Author Comment

by:pardeshirahul
ID: 41818208
what is this Customer c
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41818209
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
 

Author Comment

by:pardeshirahul
ID: 41818217
okay we have 11g database i understood sorry for the confusion
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 41818404
"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
 

Author Comment

by:pardeshirahul
ID: 41818407
yes i will close Thanks
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41818411
If you wish to continue your previous question, I can reopen it.  Let me know.
0
 

Author Comment

by:pardeshirahul
ID: 41818415
okay
0
 
LVL 8

Expert Comment

by:Ghunaima
ID: 41818512
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
 

Author Comment

by:pardeshirahul
ID: 41818616
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
 

Author Comment

by:pardeshirahul
ID: 41818617
HAVING COUNT (*) = 1

and
HAVING COUNT (*) = > 1
0
 

Author Comment

by:pardeshirahul
ID: 41818618
sorry i mean

HAVING COUNT (*) = 1

HAVING COUNT (*) > 1
0
 

Author Comment

by:pardeshirahul
ID: 41818623
okay sorry
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 41819453
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
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41824032
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question