| Index Order and Question [message #168447] |
Thu, 20 April 2006 08:34  |
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 #168470 is a reply to message #168447] |
Thu, 20 April 2006 11:04  |
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 |
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 |
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 |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
