Or not.

I just encountered the term "covering index" for the first time today.
It was being put forth as an amazing and important DBMS feature that
"everyone expects".

I was obviously worried because I've never seen Ingres claim to
implement covering indices or even express any aspiration to support
them in future (whatever they are). I was even more worried 'cos I
didn't know what they are and I suddenly felt left behind by technology.

So I did a quick Google and found out Ingres has *always* supported
them. It's just an expensive-sounding name for a secondary index with
redundant data columns that can be used to satisfy a query without
reference to the base table!

Talk about calling a spade a pedolith agitator...

--
Roy

UK Ingres User Association Conference 2011 will be on Tuesday June 7 2011.
Register at http://www.regonline.co.uk/ukiua2011

Jargon is a beautiful thing. Where did you come across this gem?

Marty

-----Original Message-----
From: Roy Hann [mailto:specially (AT) processed (DOT) almost.meat]
Sent: 23 May 2011 11:26
To: info-ingres (AT) kettleriverconsulting (DOT) com
Subject: [Info-Ingres] From "You learn something new everyday" file

Or not.

I just encountered the term "covering index" for the first time today.
It was being put forth as an amazing and important DBMS feature that
"everyone expects".

I was obviously worried because I've never seen Ingres claim to
implement covering indices or even express any aspiration to support
them in future (whatever they are). I was even more worried 'cos I
didn't know what they are and I suddenly felt left behind by technology.

So I did a quick Google and found out Ingres has *always* supported
them. It's just an expensive-sounding name for a secondary index with
redundant data columns that can be used to satisfy a query without
reference to the base table!

Talk about calling a spade a pedolith agitator...

--
Roy

UK Ingres User Association Conference 2011 will be on Tuesday June 7 2011.
Register at http://www.regonline.co.uk/ukiua2011


_______________________________________________
Info-Ingres mailing list
Info-Ingres (AT) kettleriverconsulting (DOT) com
http://ext-cando.kettleriverconsulti...fo/info-ingres

Reminds me of when Oracle introduced this wonderful new feature "Index
Organized Tables". When reading some blurb about it took me a moment to
realise all they'd done was implement BTree tables.

Reminds me of when I needed recovery some relationship key data from a table in Oracle (nearly 2002). We lost the tablespace where the base tables data were, but we had the tablespace with indices intact. Backup was gone too (corrupted data / disk). And we needed recovery only the relationship data, at least, to recreate a main base table.

In Ingres we simply do a "select table_a_col_key, table_b_col_key from ab_fk_constraint_secondary_index" and "voilà!", we can get data without the need to have the base table (this I used to do since version 6.4 as I remember ).

But in Oracle (7.2) I got stumbled. After weeks "googling" and asking to oracle specialists with negative responses, I finally found a way to do that using Oracle "hints" to retrieve data from the index without use the corrupted data from the base table.

it's like rules...others call it 'triggers' while Ingres calls it
'rules'. or MVCC called 'snapshot isolation' by SQL server etc. but
there is no Ingres term for 'defered constraints' ...while at it,
are we going to get deferred constraints same day in the future?

nikosv wrote:

Oh where do I begin?!! :-)

Let's begin at the end.

I think that to implement deferred constraints *of some kind* might be
easier than I once thought, and I've had several beer-fueled
conversations with people who would know better than me that seem to
support that. The technical problems can probably be fixed or
"finessed".

The show-stopper is that even products that support deferred constraints
find relatively few customers use them.

I think that's an education issue, but in a world with limited
development resources those resources are going to be deployed where
they'll pay back quickly. Implementing a feature that could (I firmly
believe) lead to a new approach to application development in three,
four, maybe ten years isn't going to get started when there's
quicker wins to chase.

I have to admit that I've come around to thinking that MySQL might have
been right about something that used to annoy me. I always hated the
idea that MySQL would allow you to declare constraints it had no
intention of enforcing. I've come to realize that a lot of the things
I'd like to do with constraints don't depend on them being enforced.
Just having *syntactically correct* constraint definitions available
to query in the system catalogues would be very useful. So even if
Ingres weren't to support things like deferred constraints or general
constraints (assertions) any time soon, I'd really like to be able to
declare them anyway.

Having said all that, if you are looking for an ally to push for
deferred constraints, I'm in. :-) Constraints are liberating.

--
Roy

UK Ingres User Association Conference 2011 will be on Tuesday June 7 2011.
Register at http://www.regonline.co.uk/ukiua2011

On May 23, 2011, at 2:10 PM, Roy Hann wrote:

Really?

I use them all the time in Oracle with complex object hierarchies combined with Hibernate
object-relational mapping.

ORMs are famous for inserting rows out of order in complex transactions.

I have seen teams purposely avoid putting constraints on their tables to avoid the problem.
That just makes me sad.

Cheers,

Mikey

Monday, May 23, 2011, 9:05:32 PM, you wrote:

I said "relatively few", and although I admit I was repeating
something I was told by someone else, the person who told me was
in a position to know.

Ugh. But I see why you'd want to use deferred constraints there.

I have no problem with that. In fact I don't think there is an order
to be "out of", whether you're using an ORM or anything else.
Transactions are supposed to behave as if they move the database
instantly from one consistent state to a new consistent state, and
any other behaviour is a bug. IMO.

Maybe that's what they say is the reason. More likely they don't care
or it's not a stated deliverable or they're speed freaks or something else
discreditable.

Roy

--
UK Ingres User Association Conference 2011 will be on Tuesday June 7 2011.
Register at http://www.regonline.co.uk/ukiua2011