Re: FW: Sequential scan
We had a developer's creating thier own tables and designs at
one stage, and no offence to those of you who know what you are doing
but ex-cobol proggies just don't do it...
We found one case of a table with 12 indexes on it duplicating the
data twice. The optimiser loved this table, even with Art's most
excellent dostats app.
Dropped a few of these indexes, changed some of them to composite and
poof, performance increase. The write you know about, but watch which
indexes the optimiser wants to use. We've seen the wierdest behaviour
under 7.31
brett
Dirk Moolman wrote:
>
> Is an index on paymonth necessary ? We had technical support out here at
> our site, and they requested that I drop all indexes on my system where the
> column of a stand-alone index is already the leading column in another
> composite index (for the same table of course).
>
> They said that we had alot of duplication where indexes were concerned and
> by duplicating the optimiser had more work to do, which could affect our
> performance.
>
> Any comments ?
>
> Dirk
> Reach Technologies
>
> -----Original Message-----
> From: owner-informix-list@iiug.iiug.org
> [mailto:owner-informix-list@iiug.iiug.org]On Behalf Of Mark Griebling
> Sent: Monday, January 29, 2001 5:41 PM
> To: shahid.mehmood; informix-list@iiug. org (E-mail)
> Subject: Re:
>
> Hi Shadid,
>
> You need an index on payprmbcalc.paymonth.
>
> Later,
>
> Mark
>
> At 05:58 PM 1/29/01 +0500, shahid.mehmood wrote:
>
> OS: SCO UNIX 5.0, IX: OWS 7.20.UC2
>
> I have a table named payprmbcalc which also has index on its three fields.
> This
> table contains 28000+ records. when I run a query on this table with the db
> engine searches the table using sequential scan, where as I am expecting
> search
> using an index. this query is taking too long to complete. can you guys
> suggest
> any thing how to improve this query ...
>
> The table schema and 'set explain on' output is attached here with.
>
> The Table Schema ...
>
> { TABLE "paydba".payprmbcalc row size = 58 number of columns = 9 index size
> =
> 36 }
> create table "paydba".payprmbcalc
> (
> paymonth integer not null constraint "paydba".n196_305,
> internalno char(6) not null constraint "paydba".n196_306,
> currency char(10) not null constraint "paydba".n196_307,
> balance decimal(10,2),
> product decimal(10,2),
> taxprofit decimal(10,2),
> ntaxprofit decimal(10,2),
> lastedit date,
> lastuser char(10)
> );
>
> create unique index "paydba".pp001idx on "paydba".payprmbcalc
> (paymonth,internalno, currency);
>
> the 'set explain output' ...
>
> QUERY:
> ------
> select employee.employeeno , payprmbcalc.internalno,
> employee.fname , employee.mname , employee.lname ,
> payprmbcalc.currency , payprmbcalc.balance ,
> employee.appointment , employee.termination ,
> payprmbcalc.product , payprmbcalc.taxprofit ,
> payprmbcalc.ntaxprofit
> from payprmbcalc, employee
> where payprmbcalc.paymonth = 2000130000
> and employee.internalno = payprmbcalc.internalno
> order by 1>
> Estimated Cost: 7
> Estimated # of Rows Returned: 5
> Temporary Files Required For: Order By
>
> 1) paydba.payprmbcalc: SEQUENTIAL SCAN { why ? i need index scan }
>
> Filters: paydba.payprmbcalc.paymonth = 2000130000
>
> 2) paydba.employee: INDEX PATH
>
> (1) Index Keys: internalno
> Lower Index Filter: paydba.employee.internalno =
> paydba.payprmbcalc.internalno
>
> an early reply will be highly appreciated.
>
> thanks.
>
> Shahid Mehmood
> Software Designer & IX DBA
> Information Systems Department
> The Aga Khan University,
> Karachi
>
> Official: Yes
--
-----------------------------------------------------------------
Brett's 12th law of UNIX administration...
People tend not to react well when they lose control over their
computers. Typically, it brings out the worst in them ...
-----------------------------------------------------------------
Brett Geer - UNIX Admin/Analyst/Programmer - Intratex Holdings.
Tel. +27 31 717 4000 Direct. +27 31 717 4146
Fax. +27 31 717 4001
-----------------------------------------------------------------
The little voices are talking to me again, telling me to reach
for a keyboard and type rm -rf /*
last week they had me rm -rf `echo $MANPATH | sed 's/:/ /g'`
now I fear I have no answers
-----------------------------------------------------------------