File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
Win a copy of Clojure in Action this week in the Clojure forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

full table scans

 
Mallika Kumar
Ranch Hand
Posts: 61
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 276
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 61
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 276
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
I agree. Here's the link: http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic