Home » RDBMS Server » Performance Tuning » Index Order and Question
Index Order and Question [message #168447] Thu, 20 April 2006 08:34 Go to next message
just_a_kid
Messages: 16
Registered: December 2005
Junior Member
Hi Guys,

I am new to Oracle and Indexing Stuff.
I have questions like this

For Example i have table below
Client(client_id(pk),contact_number,name,email_addr,address,post_code )

and i have index for this table as below
IDX_client_1(client_id,name,email_addr)


I have queries as below
1. Select post_code from Client where client_id = '1'
and name = 'w' and email_addr = 'www'
2. Select Post_code from Client where Client_id = '2'
3. Select POst_code from Client where Client_id = '2'
and name = 'w'
4. Select Post_code from Client where email_addr = 'w'

Which One would be faster?
In My knowledge first three queries should speed up the performance But the 4th one would not speed up because it cant use the indexes?
Am i right?


And Is there any way to maximize the performance of indexes?
Maybe like specifiy Initial Size and Next Extent Size?


Thanks.




Re: Index Order and Question [message #168462 is a reply to message #168447] Thu, 20 April 2006 10:01 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Without understanding the data, we cannot say that.
It all depends on data distribution and your statistics.
Re: Index Order and Question [message #168470 is a reply to message #168447] Thu, 20 April 2006 11:04 Go to previous messageGo to next message
markmal
Messages: 113
Registered: April 2006
Location: Toronto, Canada
Senior Member
if data distributed evenly, that would make index selective:
Q1 will have index access on IDX_client_1.
Q2 will have index access or range index scan on PK index.
Q3 will have index range scan IDX_client_1.
Q4 may have index skip scan on IDX_client_1 (starting from 9i).
Re: Index Order and Question [message #168476 is a reply to message #168470] Thu, 20 April 2006 11:30 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Q2 will not RANGE scan on pk since PK column is given

All questions could result in FTS (e.g if there were only 2 rows in the table)
Q4 is far from sure to do a skip scan (I would even say it is not very likely, unless the data is distributed exactly right for it)

[Edit: added some remarks]

[Updated on: Thu, 20 April 2006 11:33]

Report message to a moderator

Re: Index Order and Question [message #168481 is a reply to message #168476] Thu, 20 April 2006 12:19 Go to previous messageGo to next message
markmal
Messages: 113
Registered: April 2006
Location: Toronto, Canada
Senior Member
Frank wrote on Thu, 20 April 2006 12:30

Q2 will not RANGE scan on pk since PK column is given



It can when nonunique index used for PK.

MARK_MALAKANOV>create table Client(
client_id number constraint Client_pk primary key 
deferrable initially immediate,
contact_number number, 
name varchar2(100), email_addr varchar2(100),
address varchar2(100), post_code varchar2(10) );

MARK_MALAKANOV>exec dbms_stats.gather_table_stats(user,'CLIENT', cascade=>true);

MARK_MALAKANOV>set autotrace on
MARK_MALAKANOV>Select Post_code from Client where Client_id = '2';

POST_CODE
----------
SYS

1 row selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=12)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'CLIENT' (Cost=2 Card=1 B
          ytes=12)

   2    1     INDEX (RANGE SCAN) OF 'CLIENT_PK' (NON-UNIQUE) (Cost=1 C
          ard=1)




Frank wrote on Thu, 20 April 2006 12:30


All questions could result in FTS (e.g if there were only 2 rows in the table)
Q4 is far from sure to do a skip scan (I would even say it is not very likely, unless the data is distributed exactly right for it)


agree

[Updated on: Thu, 20 April 2006 12:43]

Report message to a moderator

Re: Index Order and Question [message #168559 is a reply to message #168481] Thu, 20 April 2006 23:51 Go to previous message
Frank
Messages: 7901
Registered: March 2000
Senior Member
I stand corrected; never thought about that possibility. Thanks for pointing that out!
Previous Topic: URGENT-Query not performing
Next Topic: Data Gaurd 10g
Goto Forum:
  


Current Time: Wed May 22 17:53:57 CDT 2024