2 Replies Latest reply on Sep 20, 2015 7:52 PM by xyran

    Limiting source in union view query

    xyran
    xyran Sep 16, 2015 12:58 PM

      Hi,

       

      I have a view in Red hat virtualization which is a union of  multiple sources. I want to know how can I limit the source in a single select while querying the view.

       

      Example: Following is the transformation for the view EmployeeView.

       

      Select firstname,lastname,employeeid from Source1

      UNION ALL

      Select firstname,lastname,employeeid from Source2

      UNION ALL

      Select firstname,lastname,employeeid from Source2


      My select is usually

      Select firstname from EmployeeView where employeeid = '12345'. This I believe will do a scan on tables from all the sources.


      Now I know a certain employee exists in Source1 so I still want to query EmployeeView but want to specify some extra information in the select that would limit the query to only Source1.


      Could this be possible?


      Does Red Hat virtualization support a similar concept to partitioning in Oracle.


      Thanks in advance.

        • 1. Re: Limiting source in union view query
          shawkins
          shawkins Sep 16, 2015 5:16 PM (in response to xyran)

          > Does Red Hat virtualization support a similar concept to partitioning in Oracle.

           

          DV understands value based partitioning in the select or where clause, such that if you have a view defined like:

           

          select 'source1' as source ... from source1

          union all

          select 'source2' as source ... from source2

           

          it will automatically recognize the partitioning on the source column.

           

          > Could this be possible?

           

          If there is something about the id such that a predicate can express the partitioning, that would suffice:

           

          select ... from source1 where id > 100 and < 1000

          union all

          select ... from source2 where id >= 1000


          otherwise you can add a partitioning column like in the first example and add another predicate to your query - where employeeid = 12345 and source = 'Source1'

            • Actions
          • 2. Re: Limiting source in union view query
            xyran
            xyran Sep 20, 2015 7:52 PM (in response to shawkins)

            Thank you.

             

            I have read about Partition Union after this post

             

            I have corrented by query as

             

            Select firstname,lastname,employeeid, 'Source1' as Source from Source1

            UNION ALL

            Select firstname,lastname,employeeid,'Source2' as Source from Source2

            UNION ALL

            Select firstname,lastname,employeeid,'Source3' as Sourc efrom Source2

             

            And I do my query like you suggested to do the job.

             

            Select firstname from EmployeeView where employeeid = '12345' and source = 'Source1'


              • Actions