[HACKERS] 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: [EMAIL PROTECTED] HomePage: http://www.perchine.com/dyp/ FidoNet: 2:5000/120.5 --
Re: [HACKERS] Does Oracle store values in indices?
Denis Perchine [EMAIL PROTECTED] 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
Re: [HACKERS] Does Oracle store values in indices?
Denis Perchine [EMAIL PROTECTED] 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: [EMAIL PROTECTED] HomePage: http://www.perchine.com/dyp/ FidoNet: 2:5000/120.5 --
RE: [HACKERS] Does Oracle store values in indices?
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
Re: [HACKERS] Does Oracle store values in indices?
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: [EMAIL PROTECTED] HomePage: http://www.perchine.com/dyp/ FidoNet: 2:5000/120.5 --
Re: [HACKERS] Does Oracle store values in indices?
[ 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 [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [HACKERS] Does Oracle store values in indices?
* Bruce Momjian [EMAIL PROTECTED] [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 - [[EMAIL PROTECTED]|[EMAIL PROTECTED]] "I have the heart of a child; I keep it in a jar on my desk."
RE: [HACKERS] Does Oracle store values in indices?
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
Re: [HACKERS] Does Oracle store values in indices?
"Mikheev, Vadim" [EMAIL PROTECTED] 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
