7 Replies Latest reply on Mar 20, 2017 4:52 PM by Stew Ashton

    Query:is any other way approach to better way

    959406

      Hi ALL

      i need clarification on the below mentioned query that takes very long time to execute even provide the index colum in where clause

       

       

      can you suggest me, is any other way approach

       

       

      query

       

       

      create or replace view hp_view

      select col_1, col_2,col_3,col_4,col_5,col_6,col_7 from inhp_view1

      union all

      select coA_1, coA_2,coA_3,coA_4,coA_5,coA_6,coA_7 from inhp_view2

       

       

       

       

      CREATE OR REPLACE VIEW inhp_view1 AS

      SELECT DISTINCT

               coB_1, coB_2,coB_3,coB_4,coB_5,coB_6,coB_7

           FROM EMPLOYEE E

                INNER JOIN E_EMP SE

                   ON     E.ID = SE.USERID--.Both have index

                      AND SE.ROLE = 'INFO'

                      AND se.flg = 'Y'

                INNER JOIN LONS S

                   ON     S.ID = SE.S_ID --Both have index

                      AND S.TYPE = 'GO'

                      AND S.SRC = 'CO'

                INNER JOIN F_CODE RC

                   ON rc.code = SUBSTR (s.SRC_NM, 4, 2) AND RC.type = 'CD'

                INNER JOIN FEED HR ON E.ID = HR.UIID

                INNER JOIN EMP_SET ES1

                   ON     ES1.userid = E.ID--Both have index

                      AND ES1.type = 'OK'

                      AND ES1.TYPE = 'Y'

                INNER JOIN EMP_SET ES2

                   ON     ES2.emp_userid = E.id --Both have index

                      AND ES2.type = 'RC'

                      AND ES2.TYPE = S.ID

                LEFT OUTER JOIN vacation_grp_emp vge ON vge.emp_id = e.id

                       LEFT OUTER JOIN KAL.HOME EP --->OTHER SCHEMA

                   ON     E.ID = EP.ID

                      AND EP.TYPE_NM = 'CALL'

          WHERE E.ActION = 'Y' AND E.Flg = 'Y'

       

       

       

      CREATE OR REPLACE VIEW inhp_view2 AS

        SELECT DISTINCT

                coB_1, coB_2,coB_3,coB_4,coB_5,coB_6,coB_7

           FROM EMPLOYEE E

                INNER JOIN E_EMP SE

                   ON     E.ID = SE.USERID--.Both have index

                      AND SE.ROLE = 'INFO'

                      AND se.flg = 'Y'

                INNER JOIN LONS S

                   ON     S.ID = SE.S_ID --Both have index

                      AND S.TYPE = 'GO'

                      AND S.SRC = 'CO-PP'

                IINNER JOIN F_CODE RC

                   ON rc.code = SUBSTR (s.SRC_NM, 4, 2) AND RC.type = 'CD'

                INNER JOIN FEED HR ON E.ID = HR.UIID

                INNER JOIN EMP_SET ES1

                   ON     ES1.userid = E.ID--Both have index

                      AND ES1.type = 'OK'

                      AND ES1.TYPE = 'Y'

                INNER JOIN EMP_SET ES2

                   ON     ES2.userid = E.id --Both have index

                      AND ES2.type = 'RC'

                      AND ES2.TYPE = S.ID

                LEFT OUTER JOIN vacation_grp_emp vge ON vge.emp_id = e.id

                       LEFT OUTER JOIN KAL.HOME EP --->OTHER SCHEMA TABLE

                   ON     E.ID = EP.ID

                      AND EP.TYPE_NM = 'CALL'

          WHERE E.ActION = 'Y' AND E.Flg = 'Y'

         UNION ALL

         SELECT coB_1, coB_2,coB_3,coB_4,coB_5,coB_6,coB_7

           FROM TMH TH

          WHERE NOT EXISTS

                       (SELECT 'x'

                          FROM emp e, EMP_SET es

                         WHERE     e.ID = es.userid

                               AND es.TYPE = 'COES_ENABLED'

                               AND ES.TYPE = 'OK'

                               AND E.VID = LOWER (th.VID))

       

       

       

      Thanks in Advance

        • 1. Re: Query:is any other way approach to better way
          Chris Hunt

          Is there a "better" way to write a query to achieve an unknown objective from tables we know nothing about? How do you expect us to know?

          • 2. Re: Query:is any other way approach to better way
            GregV

            Hi,

             

            I personally don't like when dev start creating nested views. A view that references a view itself referencing another view and so on. Since the views hide the underlying queries, the trap you fall into is querying the same tables several times through the views, while it could have been done once. That's your case it seems here. Your query is a UNION ALL of 2 views that select from the same tables. To top it all, each with a DISTINCT operator.

            Rewrite your query so that you strive to query the tables only once.

            • 3. Re: Query:is any other way approach to better way
              Chris Hunt

              That said, it looks like the query behind inhp_view1 and the first bit of inhp_view2 are very similar and could/should be combined into a single query. Then run that query, and the one of TMH, separately to see which one is taking the time, the concentrate on improving that query's performance.

              • 4. Re: Query:is any other way approach to better way
                959406

                this type of query structure i have ,

                 

                what i expect is how to do the best way of this approach based on this query structure

                 

                query structure:

                 

                main view view_ALL

                 

                in_view1

                union all

                in_view 2

                 

                in_view1 (select cl,c2,c3 from emp  inner join dept on emp.id=dept.id)

                 

                 

                in_view2(select cl,c2,c3 from emp1  inner join dept1 on emp1.id=dept1.id)

                • 5. Re: Query:is any other way approach to better way
                  959406

                  Thanks lot ...

                   

                  that way i think how to do

                  • 6. Re: Query:is any other way approach to better way
                    Andrew Sayer

                    I don't see any actual query?

                     

                    That said, you'd probably find that those distincts and union alls will prevent predicates from being pushed into your views - causing them to need to be calculated in full whenever you query them.

                    • 7. Re: Query:is any other way approach to better way
                      Stew Ashton

                      Imagine you went on vacation and I replaced you.

                       

                      I would start by finding which tables actually contain the columns of interest. Are they all in one table, or two tables? The other tables don't have to be JOINed to.

                       

                      I would then try to find out where the duplicates are coming from that require a DISTINCT. I would do that by adding tables one at a time to see which JOIN is causing the duplicates.

                       

                      If you can reduce the number of JOINs, you reduce the chances of generating duplicates. Here is an example using the DEPT and EMP tables from the sample SCOTT schema.

                       

                      select d.dname 
                      from dept d 
                      join emp e on d.deptno = e.deptno;
                      

                       

                      This returns 14 rows with lots of duplicates.

                       

                      select distinct d.dname 
                      from dept d 
                      join emp e on d.deptno = e.deptno;
                      

                       

                      This returns 3 rows, but does at least as much work as the first query.

                       

                      select dname from dept d
                      where exists(
                        select null from emp where deptno = d.deptno
                      );
                      

                       

                      This returns 3 rows, without requiring a DISTINCT, and does less work!

                       

                      So, if you are JOINing to a table but not using any of its columns, and that table causes duplicates, you can change the code to WHERE EXISTS and the duplicates will disappear.

                       

                      Best regards, Stew Ashton