Re: How to select 1 record from 1M records fast ?
Date: Thu, 15 Apr 2010 11:12:03 -0700 (PDT)
Message-ID: <ff5de8eb-922c-45bb-85a5-68d164814132_at_q15g2000yqj.googlegroups.com>
On Apr 15, 3:25 am, Jia Lu <roka..._at_gmail.com> wrote:
> Hi all
>
> DB:Oracle 11g R2 Standard One x64 on Linux
>
> I created a table with 1,000,000 records.
> Then I select a,b from tbl where xx=xx.
> (With the real case we need do some joins)
>
> Question is how can I select one row fast.
> (maybe in 3 seconds)
>
> With the standard edition we cannot use partition
> tables.
> Maybe we can tune it with creating indices.
>
> I need some advice on the case.
> Thanks a lot.
>
> Best
>
> Lau Lu
In the real case you will need to understand how Oracle accesses rows. You will need to understand what a plan tells you and what various things like nested loop joins and hash table probing will do for you. It isn't magic, it's just knowing how to use the tools. The basics are explained in the concepts and performance tuning guides, and intermediate level explanations are widely available.
Many things impact how to get one row fast. Oracle is particularly good where you have concurrent access cases. If you need help tuning, you need to supply an exact case, including DDL and data creation. Sometimes it is indeed better to read 1M rows, depending on what the 1 row is. For example, you may have a situation where a materialized view reads the 1M rows before someone asks for the 1 row from the view.
It all depends exactly what you are doing. 1M rows isn't a big table, unless each row has some big object.
See this: http://oracle-randolf.blogspot.com/2009/02/basic-sql-statement-performance.html
Welcome to the group, here's an introduction: http://dbaoracle.net/readme-cdos.htm
jg
-- _at_home.com is bogus. http://images.pictopia.com.edgesuite.net/perl/get_image?provider_id=801&ptp_photo_id=nctimes:8781307&size=420x300_mb&re=1&m=1265438519.0Received on Thu Apr 15 2010 - 13:12:03 CDT