Hi, I have a query which joins 5 tables. All of these tables are really huge. They all have primary key indexes and foreign key indexes. I'm trying to optimize this query for best performance time. I'm using SQL+ Explain Plan to understand how the optimizer executes the query. My sample query is: SELECT TABLE1.COL1,TABLE2.COL2,TABLE3.COL3,TABLE4.COL4,TABLE5.COL5 FROM TABLE1,TABLE2,TABLE3,TABLE4,TABLE5 WHERE TABLE1.COL1 LIKE ? AND TABLE2.COL2 LIKE ? AND TABLE1.PKEY = TABLE2.FKEY AND TABLE2.PKEY = TABLE3.FKEY AND TABLE3.PKEY = TABLE4.FKEY AND TABLE4.PKEY = TABLE5.FKEY ORDER BY TABLE1.COL1 The LAST table in the FROM clause usually is getting a Full Table Scan(FTS).So, I've placed the smallest table(in terms of number of rows) LAST in the FROM clause. But when I do this, the second LAST table in the FROM clause is getting a FTS. If I place a bigger table LAST in the FROM clause, it gets the FTS. I'm puzzled. Why does this happen ? All tables have atleast more than a few hundred thousand rows in them, but for the smallest one, which has less than 50 rows. I don't mind a FTS on this smallest table, but a FTS on any other table is BAD. Can somebody explain to me whats happening here ? Thanks a lot.
Two things 1. using like without specifying the first character will not use index 2. A foreign key requires related primary key to be index but not necessarily on the table with foreign key. Make sure they are all indexed. If you are using Oracle, you can use hints to force the use of indexes on a table Dan
Joined: Feb 15, 2001
Thanks a lot Daniel. Using HINTs did help. I have another question though... Using the explain plan, I'm getting an INDEX FULL SCAN on some primary keys on tables which have few hundred thousand rows. How efficient is an INDEX FULL SCAN on Primary Keys ? Thanks for the response.
Joined: Mar 13, 2001
Full Index scans are quicker than a table scan because only the index data is looked at. Obviously the more you can narrow down the data you looking at the better. Dan