Re: What's this SQL doing?

"mcstock" <mcstockspamplug_at_spamdamenquery.com> wrote in message news:<oc6dnZ9XAaz9zTeiRVn-tw_at_comcast.com>...
> ouch! if at all possible, get control over the production indexes --
> document what indexes are required and what they should be named in order
> for your product to perform properly, then supply a configuration validation
> option. i can't see any justification for a required index to not exist in
> production, anymore than a customer or dba could justify dropping or
> renaming constraints, tables, views, etc.

The justification could be whether the customer chooses to use a particular set of tables or reports, and how. There are some things that are just plain beyond the control of a vendor, and it is usually a mistake for the vendor to try to account for every possibility. The more generalized the product, the more true this becomes. With indices especially, there is usually going to be some trade-off between insert performance and lookup performance. Add some denormalization into the mix...

And with some of the dumbass indices I've seen, as a customer I would want both choice and information. Think on this (from a real system):

  1 select count(*) from user_ind_columns   2 group by table_name, index_name
  3* having count(*) > 10
SQL> /

        11
        11
        13
        11
        14
        12
        12
        13
        13
        16
        12
        22
        15
        15
        12
        12

16 rows selected.

>
> -- mcs
> "Thomas Kellerer" <spam_eater_at_gmx.net> wrote in message
> news:bodhq5$1c6q3a$2_at_ID-13919.news.uni-berlin.de...
> > Yes and no :-) We don't always have control over the index names that get
> > installed into the production environment, but we do have control over the
> > columns used....
> >
> > mcstock schrieb:
> >
> > > wouldn't it be better (clearer, more reliable) to use a hint to force
> the
> > > index usage?
> > >
> > > -- mcs
> > >
> > > "Thomas Kellerer" <spam_eater_at_gmx.net> wrote in message
> > > news:bod7m6$1bu8rt$1_at_ID-13919.news.uni-berlin.de...
> > >
> > >>Prince Of Thieves schrieb:
> > >>
> > >>
> > >>>Select c.clridx, n.price, n.perprice
> > >>>from clr c, sprice_clrsku n where c.styleidx = 1164
> > >>>and c.styleidx = c.styleidx and c.active = 1 and c.colouridx > 0 and
> > >>>n.spidx(+) = 163
> > >>>and n.clridx(+) = c.clridx order by c.clrcode;
> > >>>
> > >>>Notice the line "and c.styleidx = c.styleidx "? Why did the author set
> > >
> > > this
> > >
> > >>>SQL like this? Is this to accomplish something I do not know about?
> > >>>
> > >>
> > >>I'm doing this pretty often as well. Mostly because you can force Oracle
> > >
> > > to use
> > >
> > >>an index on that column by including it this way. We have seen radical
> > >
> > > changes
> > >
> > >>in the execution plan by adding conditions like that.
> > >>
> > >>Thomas
> > >>
> > >
> > >
> > >
> >

jg

--
@home.com is bogus.
http://www.signonsandiego.com/news/uniontrib/wed/business/news_1b5scrushy.html