Hello,

just small question.
I just realized that it seems that Oracle stores indexed values in the index 
itself. This mean that it is not necessary to access table when you need to 
get only indexed values.

iso table has an index for vin field. Here is an output for different queries.

SQL> explain plan for select * from iso where vin='dfgdfgdhf';
 
Explained.
 
SQL> @?/rdbms/admin/utlxpls
 
Plan Table
--------------------------------------------------------------------------------
| Operation                 |  Name    |  Rows | Bytes|  Cost  | Pstart| 
Pstop |
--------------------------------------------------------------------------------
| SELECT STATEMENT          |          |     6 |  402 |      8 |       |      
 |
|  TABLE ACCESS BY INDEX ROW|ISO       |     6 |  402 |      8 |       |      
 |
|   INDEX RANGE SCAN        |IX_ISO_VI |     6 |      |      3 |       |      
 |
--------------------------------------------------------------------------------
 
6 rows selected.
 
SQL> explain plan for select vin from iso where vin='dfgdfgdhf';
 
Explained.
 
SQL> @?/rdbms/admin/utlxpls
 
Plan Table
--------------------------------------------------------------------------------
| Operation                 |  Name    |  Rows | Bytes|  Cost  | Pstart| 
Pstop |
--------------------------------------------------------------------------------
| SELECT STATEMENT          |          |     6 |   42 |      3 |       |      
 |
|  INDEX RANGE SCAN         |IX_ISO_VI |     6 |   42 |      3 |       |      
 |
--------------------------------------------------------------------------------


I think this question already was raised here, but... Why PostgreSQL does not 
do this? What are the pros, and contros?

-- 
Sincerely Yours,
Denis Perchine

----------------------------------
E-Mail: dyp@perchine.com
HomePage: http://www.perchine.com/dyp/
FidoNet: 2:5000/120.5
----------------------------------

Tue, 23 Jan 2001 22:20:54 +0600
Denis Perchine <dyp@perchine.com>
Does Oracle store values in indices?
Denis Perchine <dyp@perchine.com> writes:
> I think this question already was raised here, but... Why PostgreSQL
> does not do this? What are the pros, and contros?

The reason you have to visit the main table is that tuple validity
status is only stored in the main table, not in each index.  See prior
discussions in the archives.

			regards, tom lane

Tue, 23 Jan 2001 12:56:19 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Does Oracle store values in indices? 
> Denis Perchine <dyp@perchine.com> writes:
> > I think this question already was raised here, but... Why PostgreSQL
> > does not do this? What are the pros, and contros?
>
> The reason you have to visit the main table is that tuple validity
> status is only stored in the main table, not in each index.  See prior
> discussions in the archives.

But how Oracle handles this?

-- 
Sincerely Yours,
Denis Perchine

----------------------------------
E-Mail: dyp@perchine.com
HomePage: http://www.perchine.com/dyp/
FidoNet: 2:5000/120.5
----------------------------------

Tue, 23 Jan 2001 23:56:25 +0600
Denis Perchine <dyp@perchine.com>
Re: Does Oracle store values in indices?