hello all,

Please help me out to fetch records from table having millions of records.
Do i need to use any Indexing technique or any special kind of Search algorithms?

Thanks in advance.

Have you ever considered index organized tables ? Here is a good discussion on index organized tables:

http://www.dba-oracle.com/oracle_news/2005...ized_tables.htm


Depends on your application. If you have a large data warehouse, then bitmap indexes may help you out instead of index organized tables. If I were you, I would setup some baseline tests using autotrace and explain plan to find out what works best for your environment.

thanks ...

I have a master table of postcodes and appropriate street,county,country details for each postcode.
Now on selection of postcode all the relative details have to be fetched.

Now searching from millions of records it takes considerable time...and i just want to reduce that..

Hi,

>> Please help me out to fetch records from table having millions of records.

By "fetch", do you mean a SQL "fetch" command? If so, check-out the "bulk collect" method, way faster than fetch. Dr. Hall has great descriptions of this:

http://www.rampant-books.com/book_2006_1_plsql_tune.htm

The fastest way to access a row is by ROWID. You can do this with sorted hash clusters:

http://www.dba-oracle.com/t_sorted_hash_clusters.htm

Otherwise, an index provides fast access, just match the index to the columns desired:

http://www.rampant-books.com/shad_oracle_cbo_book.htm

I don't know how postcode work in your country, but in the UK a single postcode is associated with about 30 addresses. If you index the postcode column on the table I would expect you to get a really quick response, even with millions of records in the table.

Cheers

Tim...