File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes full table scans Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCA/OCP Java SE 7 Programmer I & II Study Guide this week in the OCPJP forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "full table scans" Watch "full table scans" New topic
Author

full table scans

Mallika Kumar
Ranch Hand

Joined: Feb 15, 2001
Posts: 61
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.
Daniel Dunleavy
Ranch Hand

Joined: Mar 13, 2001
Posts: 276
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
Mallika Kumar
Ranch Hand

Joined: Feb 15, 2001
Posts: 61
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.
Daniel Dunleavy
Ranch Hand

Joined: Mar 13, 2001
Posts: 276
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
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
 
subject: full table scans