attached index
Floyd Wellershaus asked whether detaching a composite index on first and last name from 'in table' to another dbspace would speed up a slow, very frequent query. Andreas Legner doubted it would help, since frequently used index pages stay cached; only rebuilds are slower in table. John Carlson and bozon asked for the query plan, schema and row counts. No resolution.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
We have a query that runs kind of slow, which is exacerbated by the fact that it has to run millions of times. It's searching on partial firstname and lastname. It is using a composite index but I notice that this index as created with a storage parameter of "in table". Wouldn't I expect to get better performance by detaching that index to another dbspace ? Thanks, floyd ======================== -<<Floyd Wellershaus>>- Database Administrator Unix Administrator email: fwellers@yahoo.com Home: 703-430-0805 Cell: 703-477-6045 ======================== http://www.one.org/
I doubt that would make that much difference, if any at all - as long as it isn't version 10 and a detached index could possibly benefit from larger page sizes than the data partition. The index pages' content would be identical (single partition table). 'In table' the pages might be a bit more scattered across the partition, so when reading the index from disk, a detached index might have slightly better performance with regards to index read ahead, but with using this index so frequently all relevant pages will always be in cache. A different question would be if the index has to be rebuilt - and first dropped. This will take considerably longer 'in table'. HTH, Andreas
On Fri, 18 Aug 2006 09:21:36 -0700 (PDT), Floyd Wellershaus <fwellers@yahoo.com> wrote: >We have a query that runs kind of slow, which is exacerbated by the fact that it has to run millions of times. >It's searching on partial firstname and lastname. >It is using a composite index but I notice that this index as created with a storage parameter of "in table". > >Wouldn't I expect to get better performance by detaching that index to another dbspace ? > Maybe, maybe not . . . . What's the query plan like? JWC
Query and Query Plan might be more helpful along with schema of the tables and row counts of the tables. It just might be the query. Floyd Wellershaus wrote: > We have a query that runs kind of slow, which is exacerbated by the fact that it has to run millions of times. > It's searching on partial firstname and lastname. > It is using a composite index but I notice that this index as created with a storage parameter of "in table". > > Wouldn't I expect to get better performance by detaching that index to another dbspace ? > > Thanks, > floyd > > > > > > > > > > ======================== > -<<Floyd Wellershaus>>- > Database Administrator > Unix Administrator > > > > email: fwellers@yahoo.com > > > Home: 703-430-0805 > > > Cell: 703-477-6045 > ======================== > > > http://www.one.org/ > --0-243733573-1155918096=:95026 > Content-Type: text/html > X-Google-AttachSize: 1335 > > <html><head><style type="text/css"><!-- DIV {margin:0px} --></style></head><body><div style="font-family:times new roman, new york, times, serif;font-size:12pt"><DIV></DIV> > <DIV>We have a query that runs kind of slow, which is exacerbated by the fact that it has to run millions of times.</DIV> > <DIV>It's searching on partial firstname and lastname.</DIV> > <DIV>It is using a composite index but I notice that this index as created with a storage parameter of "in table".</DIV> > <DIV> </DIV> > <DIV>Wouldn't I expect to get better performance by detaching that index to another dbspace ?</DIV> > <DIV> </DIV> > <DIV>Thanks,</DIV> > <DIV>floyd<BR> </DIV> > <DIV><BR> > <DIV><BR> > <DIV><BR> > <DIV><BR> > <DIV>========================<BR>-<<Floyd Wellershaus>>-<BR>Database Administrator<BR>Unix Administrator</DIV><BR> > <DIV><BR>email: <A href="mailto:fwellers@yahoo.com">fwellers@yahoo.com</A></DIV><BR> > <DIV>Home: 703-430-0805</DIV><BR> > <DIV>Cell: 703-477-6045<BR>========================</DIV><BR> > <DIV><A href="http://www.one.org/">http://www.one.org/</A></DIV></DIV></DIV></DIV></DIV>; > <DIV></DIV></div></body></html> > --0-243733573-1155918096=:95026--