Solved

Create index on View

Posted on 2016-09-27
27
68 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 28

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
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 28

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 28

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 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
 
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 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Require data to appear on a single line 2 69
Sybase and replication server 13 39
oracle date format checking 7 26
PL/SQl Expanding the WHERE statement in query 3 16
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to take different types of Oracle backups using RMAN.

840 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