Solved

Create index on View

Posted on 2016-09-27
27
52 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
27 Comments
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 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 18

Expert Comment

by:Pawan Kumar Khowal
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
 
LVL 18

Expert Comment

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

Author Comment

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

Expert Comment

by:Pawan Kumar Khowal
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 76

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 34

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 76

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 48

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 76

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

760 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now