Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Create index on View

Posted on 2016-09-27
27
Medium Priority
?
85 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
  • 12
  • 4
  • 3
  • +3
22 Comments
 
LVL 78

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 35

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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 35

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 35

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 78

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 78

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 78

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
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.
Suggested Courses

877 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