View previous topic :: View next topic
|
Author |
Message |
anjoos20
New User
Joined: 09 Apr 2008 Posts: 14
|
|
|
|
Can any one explain me whether we can query an index or not? |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hello,
Your question may have multiple answers depending on what you are looking for.
Please provide an example and it will help us answer. |
|
Back to top |
|
|
dharmendra_kp
New User
Joined: 10 Mar 2005 Posts: 33 Location: Lewiston
|
|
|
|
Is it an interview question? |
|
Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008 Posts: 1020 Location: India
|
|
|
|
Anjoos,
I don't think you can query an index. That would be my answer, FIRST.
But, by the way D.sch replied
Quote: |
Your question may have multiple answers depending on what you are looking for. |
I am thinking like, is it possible ?
Sushanth |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hi Sushanth,
This will depend on the actual question. . .
One thought is that the question is really about satisfying a query using only the index. Another is to how ask db2 for info about some index(es). Another might be how to make sure a query uses an index (or how to prevent a full table scan).
Once some clarification has been provided, we should be better able to reply. |
|
Back to top |
|
|
anjoos20
New User
Joined: 09 Apr 2008 Posts: 14
|
|
|
|
Hi Dick,
My question is can we use select statement on a table index.
Thanks
Anju
dick scherrer wrote: |
Hi Sushanth,
This will depend on the actual question. . .
One thought is that the question is really about satisfying a query using only the index. Another is to how ask db2 for info about some index(es). Another might be how to make sure a query uses an index (or how to prevent a full table scan).
Once some clarification has been provided, we should be better able to reply. |
|
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hello,
Quote: |
My question is can we use select statement on a table index. |
That is just a re-post of the original question. It is not any more clear. Keep in mind that while your question is completely clear to you, it may not be to others.
From what you have posted, the only safe answer is "no". . .
What you want to do is likely possible, but until you better define what you want to do, we can't offer any better suggestions. |
|
Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008 Posts: 1020 Location: India
|
|
|
|
D.sch,
Quote: |
the actual question. . . |
Question is "Is it possible to query an index".
Query can be like,
select * from myindex
or
EXTRACTing the data inside an index by a method.
This is "THE HOW PART ?".
Quote: |
One thought is that the question is really about satisfying a query using only the index. |
No, its not about satisfying an query, but indexes are to be designed in such a way, a query will use an index and retreive data efficiently in good amount of time. In the end, query has to be satisfied in the best possible way.
Quote: |
Another is to how ask db2 for info about some index(es). |
Information about the structure of an index can be got from Catalog Tables.
Only the structure.
Quote: |
Another might be how to make sure a query uses an index (or how to prevent a full table scan). |
Use indexed columns in the predicates.
Sushanth. |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hi Sushanth,
Those "bullets" i posted were meant to generate ideas about how to clarify the original question.
Quote: |
Query can be like,
select * from myindex |
Has this been tested? What is myindex?
It will help if Anjoos posts some sample table/columns, the index definitions for the table and some sample data that would be quesied against. |
|
Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008 Posts: 1020 Location: India
|
|
|
|
D.sch,
Quote: |
Those "bullets" i posted were meant to generate ideas |
It did. And it is still continuing............
select * from myindex
Is an dummy example INDEX. Without testing i can say 'It wont' work'. I tested on a existing index.
I got an error
Code: |
SQLCODE = -204, ERROR: SHANDB.DIXEMP1 IS AN UNDEFINED NAME |
Reason : It is an index, not an table.
what i know is, its not possible by a SELECT.
What iam really interested in knowing is, if the safe answer is no, what would be the unsafe answer ?
For the Learning,
Sushanth |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hi Sushanth,
Quote: |
what would be the unsafe answer |
Ah, maybe yes would be unsafe
As you demonstrated, the select needs column names from tables.
I believe that if Anjoos posts a better example/description of what is desired, there may well be a way to do it. . . |
|
Back to top |
|
|
anjoos20
New User
Joined: 09 Apr 2008 Posts: 14
|
|
|
|
Quote: |
I believe that if Anjoos posts a better example/description of what is desired, there may well be a way to do it. . . |
What i meant is : Is it possible to see the pointer for a particular value of my key. |
|
Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008 Posts: 1020 Location: India
|
|
|
|
Anju,
I think an index will contain indexed columns of a table & ROWID.
Is ROWID or POINTER you want to see.
Sushanth |
|
Back to top |
|
|
ashimer
Active Member
Joined: 13 Feb 2004 Posts: 551 Location: Bangalore
|
|
|
|
Quote: |
I think an index will contain indexed columns of a table & ROWID.
|
Indexes has pointers to the data rows of a table .... the index is structured as a b-tree with the leaf pages containing the pointers ... this pointer is called a RID or record id... you cannot view this pointer ... |
|
Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008 Posts: 1020 Location: India
|
|
|
|
Thank You ASHIMER,
Till now i was thinking as ROWID.
Sushanth |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Thanks Ashimer
When i thought it "might be possible", i had a different requirement in mind
d |
|
Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008 Posts: 1020 Location: India
|
|
|
|
Guyz,
Quote: |
When i thought it "might be possible", i had a different requirement in mind |
Again!. It is possible to get data which is inside the index. RIGHT.
Sushanth |
|
Back to top |
|
|
|