say 'the cost is 5443 and it's too high' there can no suggestion be
given.
Regards
Alexander Schr枚der
SAP DB, SAP Labs Berlin
> -----Original Message-----
> From: ????[mailto:[EMAIL PROTECTED]
> Sent: Tuesday, June 14, 2005 6:28 PM
> To: Schroeder, Alexander; [email protected]
> Subject: RE: optimize a sql statement
>
> I know the knowledge about you mean.
> But can i optimize this operation at the status?
>
> >From: "Schroeder, Alexander" <[EMAIL PROTECTED]>
> >To: ????<[EMAIL PROTECTED]>,
<[email protected]>
> >Subject: RE: optimize a sql statement
> >Date: Tue, 14 Jun 2005 16:16:06 +0200
> >
> >Hello Liu,
> >
> >it would possibly help to identify whether there are any
> possibilities to
> optimize the statement if you post
> >the EXPLAIN output, as the strategy chosen often depends on
> the table
> statistics, i.e. depends on the data
> >contained in the tables.
> >
> >Regards
> >Alexander Schr??er
> >SAP DB, SAP Labs Berlin
> >
> > > -----Original Message-----
> > > From: ????[mailto:[EMAIL PROTECTED]
> > > Sent: Tuesday, June 14, 2005 3:56 PM
> > > To: [email protected]
> > > Subject: optimize a sql statement
> > >
> > > I have a sql statement been created basing three table.
> > > This is the table's creating statement:
> > >
--------------------------------------------------------------
> > > -----------------------
> > >
> > > CREATE TABLE "DBA"."ITEM"
> > > (
> > > "I_ID" Integer NOT NULL,
> > > "I_TITLE" Varchar (60) ASCII,
> > > "I_A_ID" Integer,
> > > "I_PUB_DATE" Date,
> > > "I_PUBLISHER" Varchar (60) ASCII,
> > > "I_SUBJECT" Varchar (60) ASCII,
> > > "I_DESC" Varchar (500) ASCII,
> > > "I_RELATED1" Integer,
> > > "I_RELATED2" Integer,
> > > "I_RELATED3" Integer,
> > > "I_RELATED4" Integer,
> > > "I_RELATED5" Integer,
> > > "I_THUMBNAIL" Varchar (40) ASCII,
> > > "I_IMAGE" Varchar (40) ASCII,
> > > "I_SRP" Fixed (17,2),
> > > "I_COST" Fixed (17,2),
> > > "I_AVAIL" Date,
> > > "I_STOCK" Integer,
> > > "I_ISBN" Char (13) ASCII,
> > > "I_PAGE" Integer,
> > > "I_BACKING" Varchar (15) ASCII,
> > > "I_DIMENSIONS" Varchar (25) ASCII,
> > > PRIMARY KEY ("I_ID")
> > > )
> > >
> > > CREATE TABLE "DBA"."AUTHOR"
> > > (
> > > "A_ID" Integer NOT NULL,
> > > "A_FNAME" Varchar (20) ASCII,
> > > "A_LNAME" Varchar (20) ASCII,
> > > "A_MNAME" Varchar (20) ASCII,
> > > "A_DOB" Date,
> > > "A_BIO" Varchar (500) ASCII,
> > > PRIMARY KEY ("A_ID")
> > > )
> > >
> > > CREATE TABLE "DBA"."ORDER_LINE"
> > > (
> > > "OL_ID" Integer NOT NULL,
> > > "OL_O_ID" Integer NOT NULL,
> > > "OL_I_ID" Integer,
> > > "OL_QTY" Integer,
> > > "OL_DISCOUNT" Fixed (5,2),
> > > "OL_COMMENTS" Varchar (100) ASCII,
> > > PRIMARY KEY ("OL_ID", "OL_O_ID")
> > > )
> > > CREATE INDEX "ORDER_LINE_OL_I_ID" ON
> "DBA"."ORDER_LINE"("OL_I_ID" ASC)
> > > CREATE INDEX "ITEM_I_A_ID" ON "DBA"."ITEM"("I_A_ID" ASC)
> > > CREATE INDEX "ORDER_LINE_OL_O_ID" ON
> "DBA"."ORDER_LINE"("OL_O_ID" ASC)
> > > CREATE INDEX "ITEM_I_SUBJECT" ON "DBA"."ITEM"("I_SUBJECT"
ASC)
> > >
--------------------------------------------------------------
> > > ---------------
> > >
> > > Follow is the sql statement:
> > >
--------------------------------------------------------------
> > > ---------------
> > >
> > > SELECT SUM(OL_QTY) SUMOLQTY,I_ID, I_TITLE, A_FNAME, A_LNAME
> > > FROM DBA.ITEM,
> > > DBA.AUTHOR, DBA.ORDER_LINE
> > > WHERE ITEM.I_ID = ORDER_LINE.OL_I_ID AND ITEM.I_A_ID =
> > > AUTHOR.A_ID AND
> > > ORDER_LINE.OL_O_ID >1 AND ITEM.I_SUBJECT = 'COOKING'
> > > GROUP BY I_ID, I_TITLE, A_FNAME, A_LNAME
> > >
--------------------------------------------------------------
> > > ---------------
> > >
> > > I using explain statement.Then i find the cost is 5443
though
> > > the statement
> > > wasn't using table scan search strategy.The cost is too
high.
> > >
> > > Please help me optimize this sql statement. Thanks!
> > >
> > >
_________________________________________________________________
> > >
娑?氦浠???规????寮告?娑??娴??绁??宀??娴ｈ法??MSN
