Thread: Does Oracle store values in indices?
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 ----------------------------------
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
> 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 ----------------------------------
> > 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? Oracle doesn't have non-overwriting storage manager but uses rollback segments to maintain MVCC. Rollback segments are used to restore valid version of entire index/table page. Vadim
> > > 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? > > Oracle doesn't have non-overwriting storage manager but uses > rollback segments to maintain MVCC. Rollback segments are used > to restore valid version of entire index/table page. Are there any plans to have something like this? I mean overwriting storage manager. -- Sincerely Yours, Denis Perchine ---------------------------------- E-Mail: dyp@perchine.com HomePage: http://www.perchine.com/dyp/ FidoNet: 2:5000/120.5 ----------------------------------
[ Charset KOI8-R unsupported, converting... ] > > > > 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? > > > > Oracle doesn't have non-overwriting storage manager but uses > > rollback segments to maintain MVCC. Rollback segments are used > > to restore valid version of entire index/table page. > > Are there any plans to have something like this? I mean overwriting storage > manager. We hope to have it some day, hopefully soon. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
* Bruce Momjian <pgman@candle.pha.pa.us> [010123 11:17] wrote: > [ Charset KOI8-R unsupported, converting... ] > > > > > 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? > > > > > > Oracle doesn't have non-overwriting storage manager but uses > > > rollback segments to maintain MVCC. Rollback segments are used > > > to restore valid version of entire index/table page. > > > > Are there any plans to have something like this? I mean overwriting storage > > manager. > > We hope to have it some day, hopefully soon. Vadim says that he hopes it to be done by 7.2, so if things go well it shouldn't be that far off... -- -Alfred Perlstein - [bright@wintelcom.net|alfred@freebsd.org] "I have the heart of a child; I keep it in a jar on my desk."
> > > But how Oracle handles this? > > > > Oracle doesn't have non-overwriting storage manager but uses > > rollback segments to maintain MVCC. Rollback segments are used > > to restore valid version of entire index/table page. > > Are there any plans to have something like this? I mean > overwriting storage manager. Well, I have plans to reimplement storage manager to allow space re-use without vacuum but without switching to overwriting, at least in near future - achievements/drawbacks are still questionable. We could add transaction data to index tuples but this would increase their size by ~ 16bytes. To estimate how this would affect performance for mostly statical tables one can run tests with schema below: create table i1 (i int, k int, l char(16)); create index i_i1 on i1 (i); create table i2 (i int, k int, l char(16)); create index i_i2 on i2 (i, k, l); Now fill tables with same data and run queries using only "I" in where clause. Vadim
"Mikheev, Vadim" <vmikheev@SECTORBASE.COM> writes:
> We could add transaction data to index tuples but this would increase
> their size by ~ 16bytes.
The increased space is the least of the drawbacks. Consider also the
time needed to maintain N copies of a tuple's commit status instead of
one. Even finding the N copies would cost a lot more than the single
disk transfer involved now ...
regards, tom lane