RE: Index over DB2 table question

Ok, this is a realm where you need to understand the conventions of relational database concepts...

Table (same as Physical File in OS400) - Stores the low level row-oriented records used by the database.

View (somewhat like Logical File that defines a custom record format) - Look at (transform) the TABLE data in some special way..

Index (implemented as keyed access paths on PF and LF objects in OS400) - Define the keys used to select and/or order the rows in the TABLE.

In practice, the index is not directly used by the developer, only the query engine uses it. It may be used for a variety of purposes, such as implementing a join, or perhaps to speed up row selection logic in your WHERE clause. The query optimizer is responsible for knowing when to implement the index, so that the query performance is acceptable.

Hth,
-Eric DeLong

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Ashish Kulkarni
Sent: Friday, May 20, 2011 2:56 PM
To: Midrange Systems Technical Discussion
Subject: Re: Index over DB2 table question

Hi
Thanks, i have created this view and is working fine,

I would like to know when do we use Index, what is the use of Index

Ashish

On Fri, May 20, 2011 at 3:10 PM, David Gibbs <david@xxxxxxxxxxxx> wrote:

Ashish Kulkarni wrote:

I have a question about Index, i have table with date field in it, i

would

like to create a index where this date is current date, is it possible to

do

so?

Index or view?

Indexes are just access paths to a table.

View's provide alternate ways of viewing the data in one or more table ...
including selection criteria.

Try something like this: CREATE VIEW myview AS SELECT FIELD1,FIELD2,FIELD3
where DATEFIELD = CURRENT DATE;

david

--
IBM i on Power Systems - For when you can't afford to be out of business
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

--
Ashish
www.ayurwellness.com
www.mysoftwareneeds.com
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.