Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2008
    Location
    Midwest
    Posts
    4

    Unanswered: Oracle optimizer question

    I haven't been able to find the answer to this question... perhaps i'm just not searching correctly.

    If I have a join and I know that the join is happening from table A to table B (I can see it in the execution plan) , and table A has a field in the index being used, lets call it company, but the literal value is actually being qualified on table B, is the optimizer smart enough to know that to use that value on the index to table A?

    Here's a simple example without any explicit join

    Assume that the index on table A is being used and is as follows:

    Ayear
    Amonth
    Acompany
    Apart

    select * from table-a A, table-b B
    where Ayear = 2008 and
    Amonth = 03 and
    Apart = 1234 and
    Acompany = Bcompany
    and Bcompany = '5678'

    Will the optimizer know that since acompany = bcompany then it can use the literal value for Bcompany in the index on table-a

    Of course this isn't how i'd write it, but I have a real world problem and I'm trying to figure out whether this is causing the problem

    Regards

    tngxl

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    How would it know what is in table-b until it looks at table-b? If there is an index on company for table-b, it will use the index. If there is no index on company, it will full table scan table-b to find the matching rows.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Feb 2008
    Location
    Midwest
    Posts
    4
    It doesn't have to know what's in table b. It's a literal in the query, only it has been qualified against bcompany, not acompany. However since acompany = bcompany and bcompany = '5678', is oracle smart enough to know that acompany = '5678' when it tries to read on the index on table a.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I'm trying to figure out whether this is causing the problem
    Problem? What problem?
    I don't see any problem.

    If you don't tell us what really is the problem to be solved,
    it might lengthen the time before a real solution is found.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Feb 2008
    Location
    Midwest
    Posts
    4
    I'm not asking for anyone to solve my problem, only for insight on the specific question that I asked.

  6. #6
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    This is pretty much easy to see in 10g+ with explain plan.
    Code:
    SQL> create table t1 ( x varchar2(4), y varchar2(4) );
    
    Table created.
    
    SQL> create table t2 ( x varchar2(4), y varchar2(4) );
    
    Table created.
    
    SQL> set autotrace traceonly explain
    SQL> select *
      2    from t1, t2
      3   where t1.x = t2.x
      4     and t2.x = '5678'
      5  /
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 1819147781
    
    ---------------------------------------------------------------------------
    | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------
    |   0 | SELECT STATEMENT   |      |     1 |    16 |     5  (20)| 00:00:01 |
    |*  1 |  HASH JOIN         |      |     1 |    16 |     5  (20)| 00:00:01 |
    |*  2 |   TABLE ACCESS FULL| T1   |     1 |     8 |     2   (0)| 00:00:01 |
    |*  3 |   TABLE ACCESS FULL| T2   |     1 |     8 |     2   (0)| 00:00:01 |
    ---------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - access("T1"."X"="T2"."X")
       2 - filter("T1"."X"='5678')
       3 - filter("T2"."X"='5678')
    
    Note
    -----
       - dynamic sampling used for this statement
    
    SQL>

  7. #7
    Join Date
    Feb 2008
    Location
    Midwest
    Posts
    4
    Wonderful, that's exactly what I needed to know. And thanks for showing me a bit about explain plans at a prompt. The tool I'm using doesn't give me that information.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •