1 2 Previous Next 16 Replies Latest reply: Jun 10, 2013 7:08 PM by jgarry RSS

    SQL plan indicates table scan

    1000029
      Oracle Database 10g Release 10.2.0.1.0
      select  m.makeupid, j.jobnumber
      from    Makeup m, Job j, Jobcrew jc
      where   m.workdate > sysdate
      and     m.jobcrewid = jc.jobcrewid
      and     jc.jobid = j.jobid    
      shows a plan of
      -----------------------------------------------------------------------------------------------
      | Id  | Operation                     | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
      -----------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT              |               |   159 |  6519 |   125   (2)| 00:00:02 |
      |*  1 |  HASH JOIN                    |               |   159 |  6519 |   125   (2)| 00:00:02 |
      |*  2 |   HASH JOIN                   |               |   321 |  9309 |    98   (2)| 00:00:02 |
      |   3 |    TABLE ACCESS BY INDEX ROWID| MAKEUP        |   353 |  6707 |    75   (0)| 00:00:01 |
      |*  4 |     INDEX RANGE SCAN          | IDX_MAKEUP_DT |   355 |       |     4   (0)| 00:00:01 |
      |   5 |    TABLE ACCESS FULL          | JOBCREW       | 11627 |   113K|    22   (0)| 00:00:01 |
      |   6 |   TABLE ACCESS FULL           | JOB           |  5082 | 60984 |    26   (0)| 00:00:01 |
      -----------------------------------------------------------------------------------------------
      
      Predicate Information (identified by operation id):
      ---------------------------------------------------
      
         1 - access("JC"."JOBID"="J"."JOBID")
         2 - access("M"."JOBCREWID"="JC"."JOBCREWID")
         4 - access("M"."WORKDATE">SYSDATE@!)
      Table Makeup has an index on WorkDate, which is being used, but a full table scan happens on both JobCrew and Job.
      The primary key for table Job is field JobId, which being the primary key should be indexed on
      Similarly, the primary key on table JobCrew is JobCrewId which is also not being used
      ALTER TABLE JOBCREW ADD CONSTRAINT SYS_C005219  PRIMARY KEY (JOBCREWID)
      ALTER TABLE JOB ADD CONSTRAINT  SYS_C005144  PRIMARY KEY (JOBID)
      Any ideas on why these table scans happen and how these can be prevented? Thanks.

      Edited by: 997026 on Jun 7, 2013 3:54 PM
        • 1. Re: SQL plan indicates table scan
          SomeoneElse
          Any ideas on why these table scans happen and how these can be prevented? Thanks.
          Not all indexes are good, not all full table scans are bad. (Tom Kyte)

          Apparently the optimizer believes that two full table scans are better than a bunch of index accesses.
          • 2. Re: SQL plan indicates table scan
            sb92075
            post results from SQL below

            select count(*) from Makeup ;
            select count(*) from Job ;
            select count(*) from Jobcrew jc;

            select count(*) from Makeup m, Jobcrew jc where m.jobcrewid = jc.jobcrewid;

            select count(*) from Job j, Jobcrew jc where jc.jobid = j.jobid;
            • 3. Re: SQL plan indicates table scan
              rp0428
              >
              select m.makeupid, j.jobnumber
              from Makeup m, Job j, Jobcrew jc
              where m.workdate > sysdate
              and m.jobcrewid = jc.jobcrewid
              and jc.jobid = j.jobid

              Any ideas on why these table scans happen and how these can be prevented?
              >
              Sure - Oracle is using them to get the data you asked for. It has to get the data somehow doesn't it?

              For all we (and Oracle) know there are BILLIONS of rows in table Makeup dated in the future and only 10 rows in the other two tables.

              So using full table scans on the two lookup tables and HASH joins is a pretty good, and lower cost, way to get the data.

              Come to think of it - I'm not really sure about the BILLIONS of rows or the 10 rows in the lookup tables.

              Come to think of it - Oracle probably isn't sure about how many rows there are in any of the tables either.

              Prevention? Why? Do you want the query to run slower? What is the PROBLEM you are trying to solve? Doesn't the query only take 1 nanosecond now? It won't get any faster on your old PC.

              Maybe if Oracle had some current stats that showed the numbers of rows and info on the data skew of the values used in the predicates it could find a lower cost plan.
              • 4. Re: SQL plan indicates table scan
                1000029
                select count(*) from Makeup ;  --144789
                select count(*) from Job ; --5082
                select count(*) from Jobcrew jc; --11629
                select count(*) from Makeup m, Jobcrew jc where m.jobcrewid = jc.jobcrewid; --144789
                select count(*) from Job j, Jobcrew jc where jc.jobid = j.jobid; --11629
                The query to filter table by workdate should narrow down the list to 246 records in the Makeup table.
                Of these there are only 61 distinct jobid values and 143 distinct jobcrewid records
                Ideally, this should be 61 records being read in Job table and 143 records being read from the table Jobcrew.
                select count(*) from Makeup where workdate > sysdate --246
                select count(distinct(jobid)) from Makeup where workdate > sysdate -- 61
                select count(distinct(jobcrewid)) from Makeup where workdate > sysdate -- 143
                I am sure there is an explanation, i just fail to understand what it is :(
                • 5. Re: SQL plan indicates table scan
                  1000029
                  rp0428 wrote:
                  >
                  select m.makeupid, j.jobnumber
                  from Makeup m, Job j, Jobcrew jc
                  where m.workdate > sysdate
                  and m.jobcrewid = jc.jobcrewid
                  and jc.jobid = j.jobid

                  Any ideas on why these table scans happen and how these can be prevented?
                  >
                  Sure - Oracle is using them to get the data you asked for. It has to get the data somehow doesn't it?

                  For all we (and Oracle) know there are BILLIONS of rows in table Makeup dated in the future and only 10 rows in the other two tables.

                  So using full table scans on the two lookup tables and HASH joins is a pretty good, and lower cost, way to get the data.

                  Come to think of it - I'm not really sure about the BILLIONS of rows or the 10 rows in the lookup tables.

                  Come to think of it - Oracle probably isn't sure about how many rows there are in any of the tables either.

                  Prevention? Why? Do you want the query to run slower? What is the PROBLEM you are trying to solve? Doesn't the query only take 1 nanosecond now? It won't get any faster on your old PC.

                  Maybe if Oracle had some current stats that showed the numbers of rows and info on the data skew of the values used in the predicates it could find a lower cost plan.
                  I am trying to learn how Oracle optimizer works. Its been many many years since i worked in databases and back in the days a table scan almost always offered up an opportunity at optimization. May be it has all changed. May be Oracle is different. But i would like to know more and i am willing to learn. I just posted records counts in this thread.
                  • 6. Re: SQL plan indicates table scan
                    rp0428
                    >
                    I am trying to learn how Oracle optimizer works. Its been many many years since i worked in databases and back in the days a table scan almost always offered up an opportunity at optimization. May be it has all changed. May be Oracle is different. But i would like to know more and i am willing to learn. I just posted records counts in this thread.
                    >
                    That's all well and good but there was a lot of information that you did NOT post at the start. If you read the FAQ at the top of the thread list on how to post a tuning request you would have seen that the common information needed includes: table and index DDL, table and query predicate row counts, information about the stats (are they current, how were they collected).

                    You just posted this
                    select count(*) from Job ; --5082
                    select count(*) from Jobcrew jc; --11629
                    If the IDs are simple NUMBER values they only take a few bytes of storage each. ROWIDs also only take a few bytes.

                    Let's just use 22 bytes for numbers and 18 for ROWIDs - 40 bytes total

                    Then a standard 8k block might equate to 200 pairs of values. So at 5 blocks per 1000 values the entire job index data is only about 25 blocks and the Jobcrew data another 50 blocks.

                    That data in a HASH table can be accessed much quicker than reading and parsing blocks of data that are in native format. So Oracle could pre-read the entire tables, extract the data to hash tables where ONLY the data needed (id, ROWID) is stored.

                    Then it is doing a HASH join of the MAKEUP table id to the hash table of the jobcrew and then using that to probe the hash table of the job table.

                    You didn't provide any info about the stats on the tables and indexes but that info is what Oracle would use to decide on the cost of the access plans it might consider.

                    Let me use an analogy for the above description.

                    There is a line of people at the library that each want to get the ISBN number for one book of the many books on databases.

                    You are the library clerk that has to help them. There are 100 books on databases. Your choices are:

                    1. Use the card catalog to look up each book, go back to the stacks, find the book and help them.

                    2. Go back to the stacks and bring all 100 books up to the front desk on a cart and them use them to satisfy the requests.

                    At what point would you decide to just go get ALL the books that you might need rather than make trips, one at a time, back to the stacks?

                    10 people in line? 100 people? 1000 people? If you don't know how many times you might want the same book but you know there are only 100 books it is easier to just get all the books in one trip instead of making seperate trips.

                    Having the proper statistics would have told you how many people are in line and how many database books you had.
                    • 7. Re: SQL plan indicates table scan
                      sb92075
                      select count(*) from Makeup ; --144789
                      select count(*) from Job ; --5082
                      select count(*) from Jobcrew jc; --11629
                      select count(*) from Makeup m, Jobcrew jc where m.jobcrewid = jc.jobcrewid; --144789
                      filter above (where m.jobcrewid = jc.jobcrewid) returns EVERY row from MAKEUP so Index unused
                      select count(*) from Job j, Jobcrew jc where jc.jobid = j.jobid; --11629
                      filter above (where jc.jobid = j.jobid) returns EVERY row from JOBCREW so Index unused

                      Index is beneficial when only a very small subset of rows get returned (by any Filter/WHERE clause)

                      CBO correctly decided that the Indexes were not selective enough to justify using them.
                      • 8. Re: SQL plan indicates table scan
                        1000029
                        sb92075 wrote:
                        select count(*) from Makeup ; --144789
                        select count(*) from Job ; --5082
                        select count(*) from Jobcrew jc; --11629
                        select count(*) from Makeup m, Jobcrew jc where m.jobcrewid = jc.jobcrewid; --144789
                        filter above (where m.jobcrewid = jc.jobcrewid) returns EVERY row from MAKEUP so Index unused
                        select count(*) from Job j, Jobcrew jc where jc.jobid = j.jobid; --11629
                        filter above (where jc.jobid = j.jobid) returns EVERY row from JOBCREW so Index unused

                        Index is beneficial when only a very small subset of rows get returned (by any Filter/WHERE clause)

                        CBO correctly decided that the Indexes were not selective enough to justify using them.
                        The table Makeup has fields jobcrewid and jobid which are pointers to records in tables Jobcrew and Job respectively.
                        The first level of filtering using workdate reduces the list to 246 records in Makeup which has 61 unique jobid values and 143 unique Jobcrewid values . Why is the lookup to tables Job and Jobcrew not done just for these values? Why is a table span less expensive than scan through the index data for the Job table for these 61 records?
                        • 9. Re: SQL plan indicates table scan
                          sb92075
                          WHY MY INDEX IS NOT BEING USED
                          http://communities.bmc.com/communities/docs/DOC-10031

                          http://searchoracle.techtarget.com/tip/Why-isn-t-my-index-getting-used

                          http://www.orafaq.com/tuningguide/not%20using%20index.html


                          post DDL for each of the three tables
                          • 10. Re: SQL plan indicates table scan
                            rp0428
                            >
                            The table Makeup has fields jobcrewid and jobid which are pointers to records in tables Jobcrew and Job respectively.
                            >
                            Well that is NEW information. That is why I suggested you read the FAQ about how to post a tuning request and told you some of the info needed.

                            Part of that info is the table and index DDL.

                            You now say that the Makeup table has BOTH 'jobcreid' and 'jobid'.

                            So why aren't you using the 'jobid' value to link to the lookup tables?
                            select  m.makeupid, j.jobnumber
                            from    Makeup m, Job j, Jobcrew jc
                            where   m.workdate > sysdate
                            and     m.jobcrewid = jc.jobcrewid
                            and     jc.jobid = j.jobid
                            That query above, that you posted, only uses 'jobcrewid' from the 'Makeup' table
                            >
                            The first level of filtering using workdate reduces the list to 246 records in Makeup which has 61 unique jobid values and 143 unique Jobcrewid values . Why is the lookup to tables Job and Jobcrew not done just for these values? Why is a table span less expensive than scan through the index data for the Job table for these 61 records?
                            >
                            The lookup IS done just for those values. I tried to explain above why a table scan might be less expensive. Oracle only has to scan the lookup table one time: to build a hash table in memory. Then it can probe that hash table instead of doing an index or table scan every time.

                            And how is Oracle supposed to know that the list might be reduced to 246 rows or that there are 61 unique jobid values and 143 unique Jobcrewid values?

                            You STILL haven't told us:

                            1. Are there any stats for ALL tables and indexes?
                            2. Are the status current for ALL tables and indexes?
                            3. How did you collect the stats for ALL tables and indexes?

                            Oracle uses stats, or does a sample (which you don't show as part of the plan you posted) to gather information like that. No stats, missing stats, wrong stats are pretty likely to result in poor execution plans.
                            • 11. Re: SQL plan indicates table scan
                              1000029
                              Thanks for the details. I am not yet familiar with collecting statistics or looking it up. Based on a few web searches, i ran DBMS_STATS.GATHER_TABLE_STATS on all these 3 tables which didnt make a difference.

                              I read the faq at https://wikis.oracle.com/display/Forums/Forums+FAQ before posting, but didnt find any mention of how to collect statistics inforrmation.

                              I didnt quite follow the "number of people in the line" analogy. Once you reduce the number of Makeup records to 246, optimizer should know the most Jobcrews it will ever need to lookup are 246. So, why would it ever need to do a table scan on Jobcrew table with 11629 records when the Jobcrewid is a primary key? I believe i am missing a key concept here.
                              • 12. Re: SQL plan indicates table scan
                                rp0428
                                Good luck with your problem. Since you don't want to provide the information that was ask for or even respond directly to the questions that were ask then I can't help you.
                                • 13. Re: SQL plan indicates table scan
                                  1000029
                                  rp0428 wrote:
                                  Good luck with your problem. Since you don't want to provide the information that was ask for or even respond directly to the questions that were ask then I can't help you.
                                  Well, i have been hard at work collecting the information. I dont have the know-how to get all the information you request. I will read up about how i can collect statistics over the weekend. I just got a copy of Christian Antognini's book which could help me. If you feel i have been unresponsive, please feel free to not help any further. Thank you for all the valuable information you provided so far.
                                      DESCRIPTION             VARCHAR2(2048)         NULL,
                                      ESTIMATENUMBER          VARCHAR2(20)           NULL,
                                      JOBNUMBER               VARCHAR2(22)           NOT NULL,
                                      NAME                    VARCHAR2(200)          NULL,
                                      STATE                   VARCHAR2(2)            NULL,
                                      STATUS                  VARCHAR2(20)           NULL,
                                      SPECIALCONDITIONS       VARCHAR2(20)           NULL,
                                      ZIPCODE                 VARCHAR2(10)           NULL,
                                      UPDATEDDATE             DATE                   NULL,
                                      UPDATEDSOURCE           VARCHAR2(100)          NULL,
                                      MISSINGJOBNOFROMHISTORY VARCHAR2(1)            DEFAULT 'N'     NULL,
                                      GEOLOCATION             MDSYS.SDO_GEOMETRY     NULL,
                                      LAT                     NUMBER(20,8)           NULL,
                                      LNG                     NUMBER(20,8)           NULL,
                                      COMPANYID               NUMBER(10,0)           NULL,
                                      CLIENTNAME              VARCHAR2(100)          NULL,
                                      PRINTTC                 CHAR(1)                DEFAULT 'N' NOT NULL
                                        
                                  )
                                  
                                  ALTER TABLE AO.JOB CONSTRAINT SYS_C005144 KEY (JOBID) INDEX TABLESPACE USERS
                                  
                                  CREATE TABLE AO.JOBCREW
                                  (
                                      JOBCREWID           NUMBER(22,0)   NOT NULL,
                                      JOBID               NUMBER(22,0)   NOT NULL,
                                      DISPATCHERID        NUMBER(22,0)   NOT NULL,
                                      SUPERINTENDENTID    NUMBER(22,0)   NOT NULL,
                                      CREATEDBYID         VARCHAR2(100)      NULL,
                                      CREATEDDATE         TIMESTAMP(6)       NULL,
                                      ENDTIME             VARCHAR2(10)       NULL,
                                      CREWLETTER          VARCHAR2(20)       NULL,
                                      LOCATION            VARCHAR2(256)      NULL,
                                      NIGHTFLAG           VARCHAR2(1)        NULL,
                                      SPECIALINSTRUCTION  VARCHAR2(2048)     NULL,
                                      STARTTIME           VARCHAR2(10)       NULL,
                                      STATUS              VARCHAR2(1)        NULL,
                                      TYPE                VARCHAR2(10)       NULL,
                                      DRIVINGINSTRUCTIONS VARCHAR2(2048)     NULL,
                                      OILERDRIVERID       NUMBER(22,0)       NULL
                                  )
                                  ALTER TABLE AO.JOBCREW ADD CONSTRAINT SYS_C005219 PRIMARY KEY (JOBCREWID)
                                  CREATE INDEX AO.IDX_JOBCREW_JOBID ON AO.JOBCREW(JOBID) 
                                  ALTER TABLE AO.JOBCREW ADD CONSTRAINT REFJOB209 FOREIGN KEY (JOBID) REFERENCES AO.JOB (JOBID)
                                  ALTER TABLE AO.JOBCREW ADD CONSTRAINT SYS_C00149785 FOREIGN KEY (OILERDRIVERID) REFERENCES AO.EMPLOYEE (EMPLOYEEID)
                                  ALTER TABLE AO.JOBCREW ADD CONSTRAINT REFEMPLOYEE251 FOREIGN KEY (SUPERINTENDENTID) REFERENCES AO.EMPLOYEE (EMPLOYEEID)
                                  ALTER TABLE AO.JOBCREW ADD CONSTRAINT REFEMPLOYEE250 FOREIGN KEY (DISPATCHERID) REFERENCES AO.EMPLOYEE (EMPLOYEEID)
                                  
                                  CREATE TABLE AO.MAKEUP
                                  (
                                      MAKEUPID            NUMBER(22,0)       NOT NULL,
                                      JOBCREWID           NUMBER(22,0)       NOT NULL,
                                      JOBID               NUMBER(22,0)       NOT NULL,
                                      JOBMANAGEMENTID     NUMBER(22,0)       NOT NULL,
                                      CREATEDBYID         VARCHAR2(100)          NULL,
                                      CREATEDDATE         TIMESTAMP(6)           NULL,
                                      DAILYREMARKS        VARCHAR2(2028)         NULL,
                                      STARTTIME           VARCHAR2(10)           NULL,
                                      STATUS              VARCHAR2(10)           NULL,
                                      WORKDATE            DATE                   NULL,
                                      ENDTIME             VARCHAR2(10)           NULL,
                                      GEOLOCATION         MDSYS.SDO_GEOMETRY     NULL,
                                      LOCATION            VARCHAR2(500)          NULL,
                                      LASTUPDATED         TIMESTAMP(9)       DEFAULT SYSTIMESTAMP NOT NULL,
                                      DRIVINGINSTRUCTIONS VARCHAR2(2000)         NULL,
                                      CANCELFLAG          VARCHAR2(1)        DEFAULT 'N'     NULL
                                  )
                                  ALTER TABLE AO.MAKEUP ADD CONSTRAINT SYS_C005155 PRIMARY KEY (MAKEUPID) USING INDEX TABLESPACE USERS
                                  CREATE INDEX AO.IDX_MAKEUP_JCID ON AO.MAKEUP(JOBCREWID)
                                  CREATE INDEX AO.IDX_MAKEUP_JID  ON AO.MAKEUP(JOBID) 
                                  CREATE INDEX AO.IDX_MAKEUP_DT   ON AO.MAKEUP(WORKDATE) 
                                  • 14. Re: SQL plan indicates table scan
                                    jgarry

                                    Hm, I don't see the faq rp referred to, but follow the directions here: HOW TO: Post a SQL statement tuning request - template posting

                                    1 2 Previous Next