Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> How to reduce time in fetching data from table, How to reduce time in fetching data from table with millions of record
URNetMate
post Feb 10 2007, 03:57 AM
Post #1


Newbie
*

Group: Members
Posts: 5
Joined: 6-January 07
Member No.: 6,397



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.
Go to the top of the page
 
+Quote Post
Ben Prusinski Or...
post Feb 10 2007, 04:05 AM
Post #2


Advanced Member
***

Group: Members
Posts: 495
Joined: 12-June 06
From: San Diego, California
Member No.: 4,359



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.


--------------------
Regards,
Ben Prusinski, Database Professional
http://www.benprusinski.com
http://www.oracle-magician.blogspot.com
Go to the top of the page
 
+Quote Post
URNetMate
post Feb 10 2007, 04:30 AM
Post #3


Newbie
*

Group: Members
Posts: 5
Joined: 6-January 07
Member No.: 6,397



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..
Go to the top of the page
 
+Quote Post
burleson
post Feb 10 2007, 06:31 AM
Post #4


Advanced Member
***

Group: Members
Posts: 11,314
Joined: 26-January 04
Member No.: 13



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


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
Tim...
post Feb 10 2007, 05:25 PM
Post #5


Newbie
*

Group: Members
Posts: 6
Joined: 8-January 07
Member No.: 6,417



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...
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 23rd July 2014 - 02:22 AM