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

On 15 Apr., 12:25, Jia Lu <roka...@gmail.com> wrote:
> Hi all
>
> DB:Oracle 11g R2 Standard One x64 on Linux
>
> =A0I created a table with 1,000,000 records.
> Then I select a,b from tbl where xx=3Dxx.
> (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
> =A0tables.
> Maybe we can tune it with creating indices.
>
> I need some advice on the case.
> Thanks a lot.
>
> Best
>
> Lau Lu

Suppose you have a book with 1.000.000 pages, and you want to find a
certain chapter. What will be your preferred method ?

a) Thumb through the book starting from page 1
b) Look into the index and go to the page cited there


Hint: If your answer is a), leave everything  as it is

On 4=E6=9C=8815=E6=97=A5, =E5=8D=88=E5=BE=8C9:02, magicwand <magicw...@gmx.=
at> wrote:
>
> a) Thumb through the book starting from page 1
> b) Look into the index and go to the page cited there
Thanks for your answer.
But if it still slow? is there any advanced method in 11gR2?

Jia Lu, 15.04.2010 14:14:
> On 4月15日, 午後9:02, magicwand<magicw...@gmx.at>  wrote:
>>
>> a) Thumb through the book starting from page 1
>> b) Look into the index and go to the page cited there
> Thanks for your answer.
> But if it still slow? is there any advanced method in 11gR2?

If the row can uniquely (!) be identified through an index, reading a single row from a table with 100 rows takes the same time as reading a single row from a table with 1,000,000 or 1,000,000,000 rows

On 15 Apr., 14:14, Jia Lu <roka...@gmail.com> wrote:
> On 4=E6=9C=8815=E6=97=A5, =E5=8D=88=E5=BE=8C9:02, magicwand <magicw...@gm=
x.at> wrote:
>
> > a) Thumb through the book starting from page 1
> > b) Look into the index and go to the page cited there
>
> Thanks for your answer.
> But if it still slow? is there any advanced method in 11gR2?

Still too slow ?
First you were asking fo < 3 seconds, now 5-8 milliseconds (worst
case) is too slow ??
In this case you should talk to your Oracle seller about an In Memory
Database, there you will be in the nano seconds range ...

On Apr 15, 3:25=A0am, Jia Lu <roka...@gmail.com> wrote:
> Hi all
>
> DB:Oracle 11g R2 Standard One x64 on Linux
>
> =A0I created a table with 1,000,000 records.
> Then I select a,b from tbl where xx=3Dxx.
> (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
> =A0tables.
> 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-pe=
rformance.html

Welcome to the group, here's an introduction: http://dbaoracle.net/readme-c=
dos.htm

jg
--
@home.com is bogus.
http://images.pictopia.com.edgesuite.net/perl/get_image?provider_id=3D801&p=
tp_photo_id=3Dnctimes:8781307&size=3D420x300_mb&re=3D1&m=3D1265438519.0