i have two tables in Oracle 11g server one is partitioned and another is having a 3GB of data. I've added a composite index on both the tables based on the where clause, in the order it appears. The output of explain plan after gather stats shows that the sql is accessing both the tables in full scan mode.Sample query below.

select /*+ PARALLEL(a 8) */ b.col1, a.* from tablea a, tableb b partition(part_00)
        a.col2 is null
        b.col3 <= a.col3

The indexes where composite. I've listed them below. the order in which it is specified in the create index syntax is given below.

create index idx_composite_tableb(col4, col3, col1) local;
create index idx_composite_tablea(col4, col2);

Thanks in Advance.

  • THe order in an index matters tablea( col1,col2,col3,col4).This will be used.And tableb(col1,col4). – Mihai Jul 5 '14 at 10:46
  • What server are you using? Provide a DESC for tables A and B. Provide details about the index declarations. – Vérace Jul 5 '14 at 10:49
  • 1
    Please supply the execution plan and the full definition of the tables and indexes. Also which DBMS are you using? – a_horse_with_no_name Jul 5 '14 at 10:59
  • Suggest you show the full query, not just the where clause, and list all indexes on those tables, and give the complete query plan too. – Michael Green Jul 5 '14 at 11:03

I am assuming that you want the Oracle optimizer to pick tableb as the driver table and for each row in tableb, have it run an index scan for tablea (Since tablea has 3GB of data).

Your indexes start with col4, so I am guessing you are hoping that one of them will be used by the optimizer.

One thing to point out is that in your WHERE clause you have both:


The last conditional (b.col4=a.col4) should give the optimizer a hint to use an index scan against tablea. However the last one (b.col4>a.col4) would likely negate that option. The optimizer at that point might realize there could be a very large amount of index scanning multiplied by the number of rows in tableb. As others pointed out, an index scan (for tablea) followed by a table row retrieval (for tablea) can quickly become more expensive than a straightforward table scan on tablea.

One last note: I am also assuming that your WHERE clause is all ANDed. If so having both


Should never be TRUE. Basically you are looking of A>B AND A=B, which should always be false.


This may be about the selectivity of your predicates. If a query uses an index it suffers the overhead of reading from disk the pages which constitute the index itself. If instead it performs a table scan it has the overhead of retrieving data it will ignore. The relative cost of these two options will depend on the selectivity of your index, how the data's clustered, and if the index is covering. The optimiser will choose whichever option actually does the least work. Having up-to-date statistics helps it do this well.

It may be that some other part of your query that you don't show -- a order by or group by for example -- would incur more cost when using the indexes than when using the table scan. For instance, if the query had an order by which is the same as the clustering sequence for the tables a costly sort can be eliminated by processing the rows in the sequence them come off disk.

  • my query is not having an order or group functions. just columns in the select list, no functions, no analitical or whatsoever functions for that matter. the where clause like listed. – Reuben Emmanuel Joshua Jul 5 '14 at 12:39
  • @ReubenEmmanuelJoshua, I can see that. What fraction of a.col2 has a NULL value? Similarly with col3? – Michael Green Jul 10 '14 at 9:45

From the documentation:

Full table scans are cheaper than index range scans when accessing a large fraction of the blocks in a table. Full table scans can use larger I/O calls, and making fewer large I/O calls is cheaper than making many smaller calls

I suggest reading that entire page and then asking a more specific question if the optimizer is not behaving as you think it ought.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.