Help - Search - Members - Calendar
Full Version: How to reduce time in fetching data from table
Oracle DBA Forums > Oracle > Oracle Forum
URNetMate
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.
Ben Prusinski Oracle DBA
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.
URNetMate
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..
burleson
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
Tim...
QUOTE (URNetMate @ Feb 10 2007, 09:31 AM) *
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..


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...
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.
Invision Power Board © 2001-2014 Invision Power Services, Inc.