Home » RDBMS Server » Performance Tuning » Index not used in outer join query
Index not used in outer join query [message #202162] Wed, 08 November 2006 07:44 Go to next message
lars3006
Messages: 9
Registered: November 2006
Junior Member
Hi all,

I am trying optimize the following query by preventing oracle from performing a full table scan (using an indices respectively).

SELECT
  /*+INDEX(person idx_person_name,idx_person_gebname)*/
  p.id ,
  p.name ,
  p.vorname ,
  p.gebdatum 
FROM  person p left outer join alias al on (p.id = al.migid)
WHERE
  UPPER(p.name) = 'ALDI'
  OR UPPER(p.gebname) = 'ALDI'
  OR UPPER(al.name) = 'A
LDI'

The person name column contains more that 100,000 distinct value so a b-tree index seem appropriate to me. Actually, there are three indices:

create index idx_person_name on person (upper(name));
create index idx_person_gebname on person (upper(gebname));
create index idx_alias_name on alias (upper(name));


The explain plan looks like this:
---------------------------------------------------------------------------------
| Id  | Operation              | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |        |   101K|  9701K|   564   (2)| 00:00:07 |
|*  1 |  FILTER                |        |       |       |            |          |
|*  2 |   HASH JOIN RIGHT OUTER|        |   101K|  9701K|   564   (2)| 00:00:07 |
|   3 |    TABLE ACCESS FULL   | ALIAS  |   242 |  2420 |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL   | PERSON |   101K|  8711K|   559   (2)| 00:00:07 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(UPPER("P"."NAME")='ALDI' OR UPPER("P"."GEBNAME")='ALDI' OR
              UPPER("AL"."NAME")='ALDI')
   2 - access("P"."ID"="AL"."MIGID"(+))


The table statistics are accurate. Even hints do not convince the optimizer to use indices.

The funny thing is: The indices are utilized after remove the criterion "UPPER(al.name) = 'ALDI'" from the query, which refers to an outer join table.

So what goes wrong here? Any help is highly appreciated!

Thanks,
Lars


[Updated on: Wed, 08 November 2006 10:54] by Moderator

Report message to a moderator

Re: Index not used in outer join query [message #202167 is a reply to message #202162] Wed, 08 November 2006 07:52 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Look into Function Based Indexes.
Search the forum.
>>The person name column contains more that 100,000 distinct value
Out of how much total records?
Re: Index not used in outer join query [message #202173 is a reply to message #202167] Wed, 08 November 2006 08:27 Go to previous messageGo to next message
lars3006
Messages: 9
Registered: November 2006
Junior Member
The total number of records in the person table is about 105,000.
And a forgot to mention that it is Oracle 10g that I use.
Re: Index not used in outer join query [message #202179 is a reply to message #202173] Wed, 08 November 2006 10:36 Go to previous messageGo to next message
lars3006
Messages: 9
Registered: November 2006
Junior Member
Could be the null values in person.gebname and alias.name responsible for the FTS ?.
select count(*), count(distinct name) from alias

returns 241 | 30.
select count(*), count(distinct gebname),count(distinct name)  from person

returns 101,368 | 3,312 | 42,339

Hence, i must correct my statement from above:

Quote:

The person name column contains more that 100,000 distinct values ...



Any ideas how to optimize the query?

Cheers,
Lars
Re: Index not used in outer join query [message #202180 is a reply to message #202179] Wed, 08 November 2006 10:43 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Edit:
I misread. Response was not to this thread.
Apologies.

[Updated on: Wed, 08 November 2006 10:47]

Report message to a moderator

Re: Index not used in outer join query [message #202216 is a reply to message #202180] Wed, 08 November 2006 15:07 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Methinks, the plan is just fine and does exactly what it is supposed to do. The smaller table ALIAS is hashed and the large table PERSON is probed.
Re: Index not used in outer join query [message #202249 is a reply to message #202216] Wed, 08 November 2006 20:12 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Whilst it is possible for Oracle to perform indexed access on OR conditions, it is only when the OR-ed columns are on the same table.

You have OR conditions over both tables. Think about it? How do you expect Oracle to use an index? It could use indexes to get the ALDI rows from PERSON, but it may then miss some of the PERSON rows that would join to ALIAS rows that satisfy the UPPER(al.name) = 'A' condition. The opposite happens if it tries to drive from the ALIAS table.

You might be able to do it as follows:
SELECT /*+ index_combine(p) */
  p.id ,
  p.name ,
  p.vorname ,
  p.gebdatum 
FROM  person p 
left outer join alias al 
on (p.id = al.migid
AND UPPER(al.name) <> 'A')
WHERE
  UPPER(p.name) = 'ALDI'
  OR UPPER(p.gebname) = 'ALDI'
UNION ALL
SELECT
  p.id ,
  p.name ,
  p.vorname ,
  p.gebdatum 
FROM  person p 
join alias al 
on (p.id = al.migid)
WHERE UPPER(al.name) = 'A'


Ross Leishman
Re: Index not used in outer join query [message #202339 is a reply to message #202162] Thu, 09 November 2006 03:53 Go to previous messageGo to next message
lars3006
Messages: 9
Registered: November 2006
Junior Member
Thanks, Ross, you made my day! The CBO costs have been reduced by 50%, which is just beautiful.
BTW, the index_combined hint did not add any benefit (obviously the CBO used this access method anyway).
The access predicate (UPPER("AL"."NAME"(+))<>'ALDI') did only add little benefit, because the alias table (accessed using predicate id 2) is small enough to perform a FTS efficiently.

---------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name               | Rows  | Bytes | Cost (%CPU)| Time  |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                    |  2019 | 66623 |   288   (2)| 00:00:04 |
|   1 |  UNION-ALL                         |                    |       |       |            |       |
|*  2 |   HASH JOIN RIGHT OUTER            |                    |  2017 | 66561 |   284   (1)| 00:00:04 |
|*  3 |    TABLE ACCESS FULL               | ALIAS              |    12 |    72 |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS BY INDEX ROWID     | PERSON             |  2017 | 54459 |   281   (1)| 00:00:04 |
|   5 |     BITMAP CONVERSION TO ROWIDS    |                    |       |       |            |       |
|   6 |      BITMAP OR                     |                    |       |       |            |       |
|   7 |       BITMAP CONVERSION FROM ROWIDS|                    |       |       |            |       |
|*  8 |        INDEX RANGE SCAN            | IDX_PERSON_NAME    |       |       |     1   (0)| 00:00:01 |
|   9 |       BITMAP CONVERSION FROM ROWIDS|                    |       |       |            |       |
|* 10 |        INDEX RANGE SCAN            | IDX_PERSON_GEBNAME |       |       |     1   (0)| 00:00:01 |
|  11 |   NESTED LOOPS                     |                    |     2 |    62 |     4   (0)| 00:00:01 |
|  12 |    TABLE ACCESS BY INDEX ROWID     | ALIAS              |     2 |    12 |     2   (0)| 00:00:01 |
|* 13 |     INDEX RANGE SCAN               | IDX_ALIAS_NAME     |     1 |       |     1   (0)| 00:00:01 |
|  14 |    TABLE ACCESS BY INDEX ROWID     | PERSON             |     1 |    25 |     1   (0)| 00:00:01 |
|* 15 |     INDEX UNIQUE SCAN              | SYS_C0063958       |     1 |       |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("P"."ID"="AL"."MIGID"(+))
   3 - filter(UPPER("AL"."NAME"(+))<>'ALDI')
   8 - access(UPPER("NAME")='ALDI')
  10 - access(UPPER("GEBNAME")='ALDI')
  13 - access(UPPER("NAME")='ALDI')
  15 - access("P"."ID"="AL"."MIGID")



I'll buy you a beer next time I'm in Melbourne.
Cheers,
Lars

Re: Index not used in outer join query [message #202495 is a reply to message #202339] Fri, 10 November 2006 01:05 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You need the <> clause to avoid duplicates, otherwise the two parts of the UNION ALL could return the same row.

Ross Leishman
Re: Index not used in outer join query [message #202542 is a reply to message #202495] Fri, 10 November 2006 05:04 Go to previous messageGo to next message
lars3006
Messages: 9
Registered: November 2006
Junior Member
rleishman wrote on Fri, 10 November 2006 08:05
You need the <> clause to avoid duplicates, otherwise the two parts of the UNION ALL could return the same row.

Ross Leishman


Of course - I overlooked that.

I am just thinking about another way to make the CBO more index friendly:

Increasing optimizer_index_caching from 0 (default) to 90.
Decreasing optimizer_index_cost_adj from 100 (default) to 10.

However, I'am not really convinced whether this is a good thought. The above query would certainly perform faster. However, there are probably a dozen other queries that rather would benefit from a FTS.

Re: Index not used in outer join query [message #202674 is a reply to message #202542] Fri, 10 November 2006 18:51 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I've never adjusted those parameters, but you are right to worry.

I certainly would not consider changing them for a single query.

OPTIMIZER_INDEX_CACHING:

Why don't you let your Production Database run for a few days, and then get the DBA to monitor how many index hits are cached. Maybe even muck about with the size of the cache for a while and see if performance improves. Then set Optimizer_Index_Caching equal to roughly the percentage of index lookups that are cached.

OPTIMIZER_INDEX_COST_ADJ:

I would only adjust this if I had a special purpose database. For example, a transactional system with no reporting might benefit from more index usage, so adjust it down. A data warehouse with no ROLAP layer might perform only bulk SQL in the ETL and building external OLAP cubes, so adjusting upwards might be a benefit.


Ross Leishman
Previous Topic: Parrallel Query Problem
Next Topic: Analyze SYS_IL0000 LOB Indexes
Goto Forum:
  


Current Time: Sun Dec 11 00:17:02 CST 2016

Total time taken to generate the page: 0.11738 seconds