Oracle : Indexes not being used

I have a query which is not using my indexes. Can someone say why?

explain plan set statement_id = 'bad8' for
select
  g1.g1id,a.a1id from atable a, 
(
 select
   phone,address,g1id from gtab开发者_如何学编程le g
 where
   g.active = 0 and
   (g.name is not null) AND 
   (SYSDATE - g.CTIME <= 2*365)
) g1
where
(
  (a.phone.ph1 = g1.phone.ph1 and
   a.phone.ph2 = g1.phone.ph2 and
   a.phone.ph3 = g1.phone.ph3
  )
  OR
  (a.address.ad1 = g1.address.ad1 and a.address.ad2 = g1.address.ad2)
) 

In both the tables : atable,gtable I have these indexes :

1. On phone.ph1,phone.ph2,phone.ph3

2. On address.ad1,address.ad2

phone,address are of custom data types.

Using Oracle 11g.

Here is the explain plan query and output :

 SELECT cardinality "Rows",
      lpad(' ',level-1)||operation||' '||
      options||' '||object_name "Plan"
 FROM PLAN_TABLE
 CONNECT BY prior id = parent_id
       AND prior statement_id = statement_id
 START WITH id = 0
       AND statement_id = 'bad8'
 ORDER BY id;

Result:

>   Rows           Plan    
     490191190  SELECT STATEMENT  
>       null          CONCATENATION  
>     490190502   HASH JOIN  
>     511841       TABLE ACCESS FULL gtable
>     41332965     PARTITION LIST ALL 
>     41332965      TABLE ACCESS FULL atable
>     688               HASH JOIN  
>     376893       TABLE ACCESS FULL gtable
>     41332965     PARTITION LIST ALL 
>     41332965      TABLE ACCESS FULL atable

Both atable,gtable have more than 10 million rows each.

Most values in columns phone and address don't repeat.

What indices Oracle chosen depends on many factor including things you haven't mentioned in your question such as the number of rows in the table, frequency of values within a column and whether you have separate or combined indices when more than one column is indexed.

Having said that, I suppose that the main reason your indices aren't used are:

• You don't join directly with GTABLE / GLOBAL. Instead you join with a view that has three additional WHERE clauses that aren't part of the index and thus make it less effective in this constellation.

• The JOIN condition includes an OR, which makes it difficult to use indices.

Update:

If Oracle used your indices to do the join - which is already very difficult due to the OR condition - it would end up with a huge number of ROWIDs. For each ROWID, it then had to fetch the full row. Since a full table scan can easily be up to 50 times faster than a fetch by ROWID (I don't know what value Oracle uses), it will only use the indices if it reliably knows that the join will reduce the number of rows to fetch by a factor of 50.

In your case, there are the remaining WHERE conditions (g.active = 0, g.name is not null, SYSDATE - g.CTIME <= 2*365), which aren't represented in the indices. So they have to applied after the join and after the GTABLE rows have been fetched. This makes it even more difficult to reach a 50 times smaller result set than a full table scan.

So I'm pretty sure the Oracle cost estimate is correct, i.e. using the indices would result in a more expensive query and even longer execution time.

We can say "your query does not use your indexes because does not need them". A hash join is better. To use your indexes, oracle need to full scan them(4 indexes), make two joins, make a rowid or, and after that read from tables probably many blocks. If he belives that the result has many rows, the CBO coose the full scans, because is faster.

There are no conditions that reduce the number of rows taken from tables. There is no range scan. It must do full scans.