1 Reply Latest reply on Sep 21, 2015 3:31 PM by rp0428

    Full Table Scan

    2837336

      Hi Guys,


      I have a a view with the below logic:

       

      SELECT

            , T2.CODE AS CODE

            , T1.PRODUCT AS PRODUCT

                FROM STORE.SY_DATES D

                 LEFT JOIN SUBSCRIPTIONSALES_VER_2 T1

              ON D.SY_DATE  = T1.POSTING_DATE

      ---

      I am querying from view using the below query.

      SELECT *

      FROM SQLSRV.Subscription_vw_ver_2_test

      WHERE POSTING_DATE BEtween to_date('2015-09-13', 'yyyy-mm-dd') and to_date('2015-09-14', 'yyyy-mm-dd');

       

      Even though I pass a specific date, the query is doing a full scan on SUBSCRIPTIONSALES_VER_2 .

      SY_DATE has many date values.

      Am I missing anything in the view logic?

      Any help is appreciated.


      Regards,

      Pavan

        • 1. Re: Full Table Scan

          DUPLICATE THREAD! WRONG FORUM!

           

          You were already ask to post your question in the Sql Forum - and now you have posted it again in the Sql dev forum.

          Full Table Scan

           

          Please mark BOTH threads ANSWERED and repost in the Sql and Pl/Sql forum

          SQL & PL/SQL

           

          When you repost you should

           

          1. provide the CORRECT code - the code you posted is INVALID and won't compile so it can NOT be correct

          2. post the execution plan - you said a full scan is being done but you didn't post the plan so people can see it

          3. post the table and index ddl - Oracle can ONLY do a full scan if there are no indexes - since you didn't post ANY indexes or even say that an index exists no one can really help

          4. tell people WHY you think a full scan is wrong - for all we know the table has 800 BILLION rows and ALL of them are for the SAME date - the date you ask for. So a full table scan is the ONLY thing Oracle will do?

           

          BEFORE you post read the FAQ in that forum that discusses how to post a tuning request and what info you need to provide. As you can see from the above four items you haven't provided ANY of them.

          1 person found this helpful