Hi,
if I got it right the reason some aggregates (such as COUNT) using only index
columns are slow on postgresql is that it uses MVCC, so it has to read the
data as well as the index. It makes sense to me, but I don't understand is how
other databases (such as Oracle) do it.
Can someone
On Tue, Nov 18, 2008 at 04:49:35PM +, Scara Maccai wrote:
if I got it right the reason some aggregates (such as COUNT) using
only index columns are slow on postgresql is that it uses MVCC, so
it has to read the data as well as the index.
Every aggregate (of which COUNT is just one example)
Sam Mason [EMAIL PROTECTED] writes:
On Tue, Nov 18, 2008 at 04:49:35PM +, Scara Maccai wrote:
It makes sense to me,
but I don't understand is how other databases (such as Oracle) do it.
I believe Oracle maintains a separate log (not sure how it's structured)
that contains this
FWIW, I believe that count(*) is pretty slow in Oracle too.
Well COUNT was only an example. I think (but I'm not sure AT ALL) that
SELECT A FROM myTAB where A 1
only uses the index (if there's an index defined for A) in Oracle.
But mine was just curiosity... which I think you and Sam
On Tue, Nov 18, 2008 at 12:48 PM, Sam Mason [EMAIL PROTECTED] wrote:
It makes sense to me,
but I don't understand is how other databases (such as Oracle) do it.
There are tradeoffs in both directions; [...] but Oracle's way is more
optimized
response type=snarky
For the most part, that's
On Tue, Nov 18, 2008 at 2:02 PM, Scara Maccai [EMAIL PROTECTED] wrote:
SELECT A FROM myTAB where A 1
only uses the index (if there's an index defined for A) in Oracle.
Well, not exactly. That's called a covered index because the query
could be satisfied directly from the index (the
Jonah H. Harris wrote on 18.11.2008 20:15:
On Tue, Nov 18, 2008 at 2:02 PM, Scara Maccai [EMAIL PROTECTED] wrote:
SELECT A FROM myTAB where A 1
only uses the index (if there's an index defined for A) in Oracle.
Well, not exactly. That's called a covered index because the query
could be
On Tue, Nov 18, 2008 at 12:33 PM, Thomas Kellerer [EMAIL PROTECTED] wrote:
If all the columns from the select list are available in the index, then
Oracle will always prefer the index scan over a table scan (at least I have
never seen something else). Even for a SELECT that returns all rows of
On Tue, Nov 18, 2008 at 2:33 PM, Thomas Kellerer [EMAIL PROTECTED] wrote:
If all the columns from the select list are available in the index, then
Oracle will always prefer the index scan over a table scan (at least I have
never seen something else). Even for a SELECT that returns all rows of
On Tue, Nov 18, 2008 at 1:03 PM, Jonah H. Harris [EMAIL PROTECTED] wrote:
On Tue, Nov 18, 2008 at 2:57 PM, Scott Marlowe [EMAIL PROTECTED] wrote:
Sounds like they're borrowing the code from innodb that does much the
same thing. In Innodb, if a field is indexed, it lives only as an
index, not
On Tue, Nov 18, 2008 at 2:57 PM, Scott Marlowe [EMAIL PROTECTED] wrote:
Sounds like they're borrowing the code from innodb that does much the
same thing. In Innodb, if a field is indexed, it lives only as an
index, not in the table and an index at the same time.
They aren't borrowing
On Tue, Nov 18, 2008 at 1:07 PM, Scott Marlowe [EMAIL PROTECTED] wrote:
On Tue, Nov 18, 2008 at 1:03 PM, Jonah H. Harris [EMAIL PROTECTED] wrote:
On Tue, Nov 18, 2008 at 2:57 PM, Scott Marlowe [EMAIL PROTECTED] wrote:
Sounds like they're borrowing the code from innodb that does much the
same
On Tue, Nov 18, 2008 at 3:07 PM, Scott Marlowe [EMAIL PROTECTED] wrote:
They aren't borrowing anything, Oracle has had this functionality
since at least Oracle 8i (1999).
Whoa, calm down Francis.
My name's not Francis :)
I'm not suggesting they stole it or something. Just that they're
On Tue, Nov 18, 2008 at 3:09 PM, Scott Marlowe [EMAIL PROTECTED] wrote:
Oh, and citation needed. I don't remember seeing anything about
oracle using indexes as sole storage units back in 8i
Your memory-foo is weak. See ORGANIZATION INDEX:
On Tue, 2008-11-18 at 15:28 -0500, Jonah H. Harris wrote:
On Tue, Nov 18, 2008 at 3:09 PM, Scott Marlowe [EMAIL PROTECTED] wrote:
Oh, and citation needed. I don't remember seeing anything about
oracle using indexes as sole storage units back in 8i
Your memory-foo is weak. See
On Tue, Nov 18, 2008 at 3:45 PM, Joshua D. Drake [EMAIL PROTECTED] wrote:
Off topic much?
Hey, all I did was make a joke; other people wanted to get all
*correct* about it :)
Anyway, as this has been discussed at least twenty times before, this
is a waste of a thread.
--
Jonah H. Harris,
Scott Marlowe wrote:
They aren't borrowing anything, Oracle has had this functionality
since at least Oracle 8i (1999).
Whoa, calm down Francis. I'm not suggesting they stole it or
something. Just that they're using the same basic concepts.
Oh, and citation needed. I don't
Jonah H. Harris wrote on 18.11.2008 20:58:
On Tue, Nov 18, 2008 at 2:33 PM, Thomas Kellerer [EMAIL PROTECTED] wrote:
If all the columns from the select list are available in the index, then
Oracle will always prefer the index scan over a table scan (at least I have
never seen something else).
On Tue, Nov 18, 2008 at 3:54 PM, Thomas Kellerer [EMAIL PROTECTED] wrote:
Hmm. I was not talking about an index _fast full_ scan, I was talking about
index scans in general. Personally I have never seen Oracle using a table
scan (whatever kind) if all columns in the select are present in the
19 matches
Mail list logo
