Why is this query doing a full table scan?

Question

The query:

SELECT tbl1.*
   FROM tbl1 
JOIN tbl2
     ON (tbl1.t1_pk  = tbl2.t2_fk_t1_pk
AND tbl2.t2_strt_dt <= sysdate
AND tbl2.t2_end_dt  >= sysdate)
JOIN tbl3 on (tbl3.t3_pk = tbl2.t2_fk_t3_pk
AND tbl3.t3_lkup_1 = 2577304
AND tbl3.t3_lkup_2 = 1220833)
where tbl2.t2_lkup_1   = 1020000002981587;

Facts:

• Oracle XE
• tbl1.t1_pk is a primary key.
• tbl2.t2_fk_t1_pk is a foreign key on that t1_pk column.
• tbl2.t2_lkup_1 is indexed.
• tbl3.t3_pk is a primary key.
• tbl2.t2_fk_t3_pk is a foreign key on that t3_pk column.

Explain plan on a database with 11,000 rows in tbl1 and 3500 rows in tbl2 shows that it's doing a full table scan on tbl1. Seems to me that it should be faster if it could do a index query on tbl1.

Explain plan on a database with 11,000 rows in tbl1 and 3500 rows in tbl2 shows that it's doing a full table scan on tbl1. Seems to me that it should be faster if it could do a index query on tbl1.

Update: I tried the hint a few of you suggested, and the explain cost got much worse! Now I'm really confused.

Further Update: I finally got access to a copy of the production database, and "explain plan" showed it using indexes and with a much lower cost query. I guess having more data (over 100,000 rows in tbl1 and 50,000 rows in tbl2) were what it took to make it decide that indexes were worth it. Thanks to everybody who helped. I still think Oracle performance tuning is a black art, but I'm glad some of you understand it.

Further update: I've updated the question at the request of my former employer. They don't like their table names showing up in google queries. I should have known better.

Answer 1

The easy answer: Because the optimizer expects more rows to find then it actually does find.

Check the statistics, are they up to date? Check the expected cardinality in the explain plan do they match the actual results? If not fix the statistics relevant for that step.

Histogramms for the joined columns might help. Oracle will use those to estimate the cardinality resulting from a join.

Of course you can always force index usage with a hint