Topic: SQL*Plus and PL/SQL >> Clarification required in non-unique index
|
Disclaimer: The purpose of all dbapool forums including OCP and Other Oracle Certification forums is to help each other with specific issues but not to share dump and copyrighted exam content, materials or intellectual property.
You may review the entire Oracle Certification Program Candidate Agreement online Here.
|
|
|
|
| Title: Clarification required in non-unique index |
| muzu |
Posted: Aug 22, 2007 04:23:34 AM |
Total Post: 37
Joined: Aug, 2007
|
If I have a table t1 with a column a as follows
A
1
1
1
1
1
1
1
1
1
1
2
2
2
2
2
2
2
2
2
2
if i create a non unique index on column A of table t1
and access a row by giving the following select statement
select a from t1 where a > 1;
where we get all the values that are '2' returning 10 rows.
Here the non-unique index goes with index range scan.
1) Is it that it also takes the rowid into account while accessing the row values of 2 ?
2) Suppose if i want a particular row value from a particular position say for example 12th position in the column A. How can non-unique index will be useful and how it actually accesses and what will be the query?
3)When and where generally non-unique indexes are used ?
|
|
|
 Mitesh |
| Posted: Aug 28, 2007 05:53:49 AM |  |
|
Total Post: 6
Joined: Aug, 2007
|
Hi,
Here the non-unique index goes with index range scan.
1) Is it that it also takes the rowid into account while accessing the row values of 2 ?
Ans : Yes
2) Suppose if i want a particular row value from a particular position say for example 12th position in the column A. How can non-unique index will be useful and how it actually accesses and what will be the query?
Ans: for accessing 12th record
step 1 ) First create view with rownum
e.g.
create or replace view v_t1_num
as
select t1.*,rownum as rec_no from t1
Step2 ) Now each rowid is stored in rec_no of v_t1_num.
so you can fire query
like
select * from v_t1_num t
where t.rec_no=12
so you will get 12th record.
3)When and where generally non-unique indexes are used
Ans : It depends upon the user requirement , i mean to say suppose i m creating user table there may be user name may be repeated. But if i want to unique user name as per requirement, then for that i have to create unique index.
|
|
|
|
|
| Time Zone: EDT |
Send this thread to your friend |
|
|
|
|
Forum Rules & Description
Who Can Read The Forum? Any registered user or guest
Who Can Post New Topics? Any registered user
Who Can Post Replies? Any registered user
|
| |
Get FREE Magazines
|
Top 10 Forum User
|
| Murtuja Khokhar | 857 |
| Mohammed Taj | 746 |
| positive fanatic | 483 |
| Jayanta Sur | 479 |
| Vigyan Kaushik | 386 |
| Vinoth Kumar | 357 |
| Gopu Gopi | 340 |
| Gitesh Trivedi | 322 |
| neeraj sharma | 258 |
| Ramesh Jois | 246 |
|
|
