A question about Indexing. 
Author Message
 A question about Indexing.


Quote:

>In my work environment we are using Informix Dynamic Server AD and XP
>Options 8.2.

>Here they don't use indexes at all. (Not even primary key indexes)

>1. Is that true if you access more than 15% rows of a table you don't need
>any indexes? (What about Group by / Order by?)

This is always open to debate, but the reasoning for not having indexes is
largely as follows:

* If you read more than a certain percentage of rows from a table, indexes
aren't used anyway (unless there's a sort).
* In general, indexes are beneficial for sorting, but you need to weigh up
the performance benefit against the storage cost and the maintenance cost
(loads will be substantially slower). It may cost you too much load time to
justify the sort benefit.
* By the time the data gets into the warehouse, it should be scrubbed and
validated, hence the lack of keys for RI. (This is the theory, anyway! :-)

Quote:
>2. Or,  is there some thing particular about indexing in this version of
>Informix?

Not really, the same principles would apply for IDS.

Quote:
>3. What is Informix XPS? Is it different from the above version?

XPS is the old name for AD and XP.

HTH.

______________________________________________________
Get Your Private, Free Email at http://www.***.com/



Wed, 18 Jun 1902 08:00:00 GMT
 A question about Indexing.


Quote:


> > In my work environment we are using Informix Dynamic Server AD and XP
> > Options 8.2.

>Seems that nobody else has taken a crack at this, so I'll have a go (and
>try to leave my prejudices at the door :-)).

And a fine job you did too! (Of replying *and* of leaving your prejudices at
the door! :-)

Quote:
> > 3. What is Informix XPS? Is it different from the above version?

><Flame Suit> No </Flame Suit>. <Troll> Others may disagree.. </Troll>

No, it's no different from XPS. Just like Oracle8 and Oracle8i. :-p

______________________________________________________
Get Your Private, Free Email at http://www.hotmail.com



Wed, 18 Jun 1902 08:00:00 GMT
 A question about Indexing.

Quote:


> >In my work environment we are using Informix Dynamic Server AD and XP
> >Options 8.2.

> >Here they don't use indexes at all. (Not even primary key indexes)

> >1. Is that true if you access more than 15% rows of a table you don't need
> >any indexes? (What about Group by / Order by?)

> This is always open to debate, but the reasoning for not having indexes is
> largely as follows:

> * If you read more than a certain percentage of rows from a table, indexes
> aren't used anyway (unless there's a sort).
> * In general, indexes are beneficial for sorting, but you need to weigh up
> the performance benefit against the storage cost and the maintenance cost
> (loads will be substantially slower). It may cost you too much load time to
> justify the sort benefit.
> * By the time the data gets into the warehouse, it should be scrubbed and
> validated, hence the lack of keys for RI. (This is the theory, anyway! :-)

Unless you speak to ex-RedBrick (now Informix) consultants Dude. They insist a Data Warehouse needs RI! But that might be because they have a star index, and ADO does not.

AB
----------------------------------------------------------------
Get your free email from AltaVista at http://altavista.iname.com



Wed, 18 Jun 1902 08:00:00 GMT
 A question about Indexing.


Quote:



> > >In my work environment we are using Informix Dynamic Server AD and XP
> > >Options 8.2.

> > >Here they don't use indexes at all. (Not even primary key indexes)

> > >1. Is that true if you access more than 15% rows of a table you don't
>need
> > >any indexes? (What about Group by / Order by?)

> > This is always open to debate, but the reasoning for not having indexes
>is
> > largely as follows:

> > * If you read more than a certain percentage of rows from a table,
>indexes
> > aren't used anyway (unless there's a sort).
> > * In general, indexes are beneficial for sorting, but you need to weigh
>up
> > the performance benefit against the storage cost and the maintenance
>cost
> > (loads will be substantially slower). It may cost you too much load time
>to
> > justify the sort benefit.
> > * By the time the data gets into the warehouse, it should be scrubbed
>and
> > validated, hence the lack of keys for RI. (This is the theory, anyway!
>:-)

>Unless you speak to ex-RedBrick (now Informix) consultants Dude. They
>insist a Data Warehouse needs RI! But that might be because they have a
>star index, and ADO does not.

Last time I looked, we were discussing the Database Server For Real Men With
Hairy Chests (TM), not that wiffly other thing. :-)

But (horror of horrors!) that chap Townsend from the O-word Corporation was
basically right. When you're in AD/XP territory, the normal rules of
database design don't really apply. I mean, ask any ex-RedBrick consultant
how they would cope with putting RI onto a database where the _raw_data_
exceeded 2TB... :-)

______________________________________________________
Get Your Private, Free Email at http://www.hotmail.com



Wed, 18 Jun 1902 08:00:00 GMT
 A question about Indexing.

Quote:


> >Unless you speak to ex-RedBrick (now Informix) consultants Dude. They
> >insist a Data Warehouse needs RI! But that might be because they have a
> >star index, and ADO does not.

> Last time I looked, we were discussing the Database Server For Real Men With
> Hairy Chests (TM), not that wiffly other thing. :-)

Sure Dude, but I was just pointing out that there are two schools of thought when it comes to DW DB design, and Informix subscribe to both of them.

Quote:
> But (horror of horrors!) that chap Townsend from the O-word Corporation was
> basically right. When you're in AD/XP territory, the normal rules of
> database design don't really apply. I mean, ask any ex-RedBrick consultant
> how they would cope with putting RI onto a database where the _raw_data_
> exceeded 2TB... :-)

Sure thing, but Informix still support & sell it for some reason. Though sometimes I think they just bought it to remove an irritation from the market. Perhaps they could rename it to IDS-RHO - IDS Red Herring Option, or maybe IDS-LBO - Lead Brick(Baloon) Option.

AB
----------------------------------------------------------------
Get your free email from AltaVista at http://altavista.iname.com



Wed, 18 Jun 1902 08:00:00 GMT
 A question about Indexing.


Quote:



> > >Unless you speak to ex-RedBrick (now Informix) consultants Dude. They
> > >insist a Data Warehouse needs RI! But that might be because they have a
> > >star index, and ADO does not.

> > Last time I looked, we were discussing the Database Server For Real Men
>With
> > Hairy Chests (TM), not that wiffly other thing. :-)

>Sure Dude, but I was just pointing out that there are two schools of
>thought when it comes to DW DB design, and Informix subscribe to both of
>them.

Ah, but you use IDS AD/XP for "Data Warehouses" and operational data stores,
and the Wiffly Other Thing for data marts.

Quote:
> > But (horror of horrors!) that chap Townsend from the O-word Corporation
>was
> > basically right. When you're in AD/XP territory, the normal rules of
> > database design don't really apply. I mean, ask any ex-RedBrick
>consultant
> > how they would cope with putting RI onto a database where the _raw_data_
> > exceeded 2TB... :-)

>Sure thing, but Informix still support & sell it for some reason. Though
>sometimes I think they just bought it to remove an irritation from the
>market. Perhaps they could rename it to IDS-RHO - IDS Red Herring Option,
>or maybe IDS-LBO - Lead Brick(Baloon) Option.

:-)

______________________________________________________
Get Your Private, Free Email at http://www.hotmail.com



Wed, 18 Jun 1902 08:00:00 GMT
 A question about Indexing.

Quote:

>Sure thing, but Informix still support & sell it for some reason. Though sometimes I think they >just bought it to remove an irritation from the market. Perhaps they could rename it to IDS->RHO - IDS Red Herring Option, or maybe IDS-LBO - Lead Brick(Baloon) Option.

Knowing Informix's Marketing Dept it would be the only product they'd try to sell

Paul Watson
WF Software Ltd
Tel: +44 1436 674729
Fax: +44 1436 678729
www.wfsoftware.com/informix
# If you broke it, hide the evidence

**********************************************************************
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they  
are addressed. If you have received this email in error please notify
the system manager.

This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.

www.mimesweeper.com
**********************************************************************



Wed, 18 Jun 1902 08:00:00 GMT
 A question about Indexing.

Quote:




> > > >Unless you speak to ex-RedBrick (now Informix) consultants Dude. They
> > > >insist a Data Warehouse needs RI! But that might be because they have a
> > > >star index, and ADO does not.

> > > Last time I looked, we were discussing the Database Server For Real Men
> >With
> > > Hairy Chests (TM), not that wiffly other thing. :-)

> >Sure Dude, but I was just pointing out that there are two schools of
> >thought when it comes to DW DB design, and Informix subscribe to both of
> >them.

> Ah, but you use IDS AD/XP for "Data Warehouses" and operational data stores,
> and the Wiffly Other Thing for data marts.

Sure Dude, that's what Informix marketing came up with in order to sell the products side by side. The Red Herring people were not too happy about that concept though. Just as the ADO people were not too happy about their product not being sutiable for data marts. Why do you think ADO for NT was dropped, hey? A direct competitor for Lead Balloon.

I think they should stop pandering to the Red Balloon people, and drop it in favour of ADO on all platforms! It's a cool product! The problem they have is they don't have a marketing dept who can sell that concept to the public.

AB
----------------------------------------------------------------
Get your free email from AltaVista at http://altavista.iname.com



Wed, 18 Jun 1902 08:00:00 GMT
 A question about Indexing.


Quote:





> > > > >Unless you speak to ex-RedBrick (now Informix) consultants Dude.
>They
> > > > >insist a Data Warehouse needs RI! But that might be because they
>have a
> > > > >star index, and ADO does not.

> > > > Last time I looked, we were discussing the Database Server For Real
>Men
> > >With
> > > > Hairy Chests (TM), not that wiffly other thing. :-)

> > >Sure Dude, but I was just pointing out that there are two schools of
> > >thought when it comes to DW DB design, and Informix subscribe to both
>of
> > >them.

> > Ah, but you use IDS AD/XP for "Data Warehouses" and operational data
>stores,
> > and the Wiffly Other Thing for data marts.

>Sure Dude, that's what Informix marketing came up with in order to sell the
>products side by side. The Red Herring people were not too happy about that
>concept though. Just as the ADO people were not too happy about their
>product not being sutiable for data marts. Why do you think ADO for NT was
>dropped, hey? A direct competitor for Lead Balloon.

I think the number one reason AD/XP isn't suitable for data marts is the
$150 000 installation and configuration price tag. :-)

Quote:
>I think they should stop pandering to the Red Balloon people, and drop it
>in favour of ADO on all platforms! It's a cool product! The problem they
>have is they don't have a marketing dept who can sell that concept to the
>public.

Wrong. You should have said: "The problem they have is they don't have a
marketing dept." Full stop.

Anyway, I don't know that they should drop either one if they can sell them.
They each address different things, and *I* don't believe that the song and
dance of setting up AD/XP is justified in a lot of smaller sites. Also,
LeadBrik is very fast in specific cases, and that might make it attractive
to certain people. Informix is still a long way from a "one size fits all"
database, IMHO.

______________________________________________________
Get Your Private, Free Email at http://www.hotmail.com



Wed, 18 Jun 1902 08:00:00 GMT
 A question about Indexing.

Quote:
> > * By the time the data gets into the warehouse, it should be scrubbed and
> > validated, hence the lack of keys for RI. (This is the theory, anyway! :-)

> Unless you speak to ex-RedBrick (now Informix) consultants Dude. They insist a Data Warehouse needs RI! But that might be because they have a star index, and ADO does not.

It IS possible to have the RI without the indexes in some of the more
advanced databases :-). If the DW has been designed well, you will only
need to validate new data, and a straight 'new' partition comparison
against the existing data will allow you to check PK's and FK's without
the overhead of building an index (although in does increase load time).
This then provides support for star indexes etc, and is also useful for
end-user tools that use the RI information as meta data.

--
Regards,

Mark Townsend

  mtownsen.vcf
< 1K Download


Wed, 18 Jun 1902 08:00:00 GMT
 A question about Indexing.


Quote:

>It IS possible to have the RI without the indexes in some of the more
>advanced databases :-)

I didn't realise you're were such a big fan of Informix servers, Mark! :-)

______________________________________________________
Get Your Private, Free Email at http://www.hotmail.com



Wed, 18 Jun 1902 08:00:00 GMT
 
 [ 11 post ] 

 Relevant Pages 

1. Index question (best index structures for a table)

2. DTS newbie question regarding indexes

3. Question on Index

4. Question on indexing column(s) within a table

5. Question regarding Indexing

6. Question on Indexes

7. question on indexes

8. Question about indexes and performance.

9. question about indexes

10. SQL Namespace Question on Indexes

11. Basic question of indexes


 
Powered by phpBB® Forum Software