Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Use index to avoid full table scan

Status
Not open for further replies.

cmmrfrds

Programmer
Feb 13, 2000
4,690
US
Oracle 11.2 G

I am joining a table where I only need two fields the join key and one other field. If I created a concatenated index that contained the two fields would oracle determine that it can get all the needed information from the index and avoid a full table scan. The table has 71 million rows with an avg length of 307 bytes and takes 500K blocks. So, I would like to avoid the I/O necessary to read this table.

Thank you.
 

Go for it! [thumbsup2]



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb
 
Oracle may utilize its INDEX FAST FULL SCAN method and read all fields directly from index not accessing table at all. But only in case your query uses only those 2 fields in index.

Regards, Dima
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top