Forum Stats

  • 3,768,302 Users
  • 2,252,772 Discussions
  • 7,874,521 Comments

Discussions

SQL plan indicates table scan

1000029
1000029 Member Posts: 16
edited Jun 10, 2013 8:08PM in General Database Discussions
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">[email protected]!)
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
Tagged:
«1

Answers

  • SomeoneElse
    SomeoneElse Member Posts: 14,866 Silver Crown
    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.
  • sb92075
    sb92075 Member Posts: 42,196 Blue Diamond
    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;
  • >
    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.
  • 1000029
    1000029 Member Posts: 16
    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 :(
  • 1000029
    1000029 Member Posts: 16
    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.
  • >
    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.
  • sb92075
    sb92075 Member Posts: 42,196 Blue Diamond
    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.
  • 1000029
    1000029 Member Posts: 16
    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?
  • sb92075
    sb92075 Member Posts: 42,196 Blue Diamond
    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
  • >
    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.
This discussion has been closed.