1. Registered User
Join Date
Feb 2008
Location
Midwest
Posts
4

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. Registered User
Join Date
Jun 2004
Location
Liverpool, NY USA
Posts
2,520
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.

3. Registered User
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. Registered User
Join Date
Aug 2003
Location
Where the Surf Meets the Turf @Del Mar, CA
Posts
7,776
>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.

5. Registered User
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. Registered User
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. Registered User
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
•