| Index not used in outer join query [message #202162] |
Wed, 08 November 2006 07:44  |
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
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 #202179 is a reply to message #202173] |
Wed, 08 November 2006 10:36  |
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 #202249 is a reply to message #202216] |
Wed, 08 November 2006 20:12 |
rleishman
Messages: 3728
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 |
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 #202542 is a reply to message #202495] |
Fri, 10 November 2006 05:04 |
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 |
rleishman
Messages: 3728
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
