Home » RDBMS Server » Performance Tuning » Query tuning
Query tuning [message #148824] Mon, 28 November 2005 07:26 Go to next message
yram
Messages: 75
Registered: February 2001
Member
I have a table with data around 5000 records... the column are c1,c2,c3,c4,c5... c10
The column c1,c2,c3,c4 are having compsite index on these columns. the column c3,c4 are of date datatype, c1,c2 are of number datatype...

I have a query with c1,c2,c3,c4 in the where clause, inspite of these i am having a full table scan ... can u pls help me out... to overcome the full table scan...

Regards
Yram
Re: Query tuning [message #148825 is a reply to message #148824] Mon, 28 November 2005 07:33 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
To mis-quote / paraphrase Tom Kyte:
Quote:

Full table scans are not bad
Index lookups are not good
Full table scans are not bad
Index lookups are not good
Full table scans are not bad
Index lookups are not good
repeat ad infinitum until understood


You may easily find that the index structure will require more IO to extract the data than a multiblock read FTS.

Read the P&T documentation available @ http://tahiti.oracle.com
(go for the 10g version)

HTH
Jim
Re: Query tuning [message #149256 is a reply to message #148824] Wed, 30 November 2005 20:26 Go to previous messageGo to next message
nmacdannald
Messages: 460
Registered: July 2005
Location: Stockton, California - US...
Senior Member
If, for some reason, you truely want to avoid FTS then index properly and add hints if necessary.
Re: Query tuning [message #149543 is a reply to message #148824] Fri, 02 December 2005 03:32 Go to previous message
yogeshse
Messages: 11
Registered: December 2005
Location: Chennai
Junior Member
Hello,

i think , if the query is going to retrive more than 20% of the records in table, then optimizer will go for full table scan.

Thanks,
Yogesh
Previous Topic: Help - ToTune This Query....
Next Topic: Performance tuning without accessing real data
Goto Forum:
  


Current Time: Tue Dec 06 22:25:44 CST 2022