different cost values.
So take care when comparing explain output for different values.
Regards
Alexander Schr枚der
SAP DB, SAP Labs Berlin
> -----Original Message-----
> From: ��?��?[mailto:[EMAIL PROTECTED]
> Sent: Thursday, June 16, 2005 5:01 AM
> To: [EMAIL PROTECTED]; Schroeder, Alexander;
> [email protected]
> Subject: RE: optimize a sql statement
>
> I understand the produce of the phenomennon.It because
> 'COOKING'in the
> table "ITEM" column "I_SUBJECT".
> Now,I change my sql statment.
> Follow is the 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 >515603 AND ITEM.I_SUBJECT = 'COOKING'
> GROUP BY I_ID, I_TITLE, A_FNAME, A_LNAME
> --------------------------------------------------------------
> --------------------------
>
> The condition "ORDER_LINE.OL_O_ID >10000" turn into
> "ORDER_LINE.OL_O_ID
> >515603".
> Follow is the explain output:
> --------------------------------------------------------------
> --------------------------
>
> DBA ORDER_LINE ORDER_LINE_OL_O_ID RANGE CONDITION FOR
> INDEX
> 20532
> DBA ITEM ITEC_BOTH1 JOIN VIA RANGE OF
> MULTIPLE INDEXED
> COL. 810
> I_ID (USED INDEX COLUMN)
>
>
> DBA AUTHOR A_ID JOIN VIA KEY COLUMN
>
> 125
> TABLE HASHED
>
>
> RESULT IS COPIED
> , COSTVALUE IS
> 2162
> --------------------------------------------------------------
> --------------------------
>
> The costvalue is going down.
> But the costvalue too high to apply.
> For this explain output,how do i optimize the sql statement?
>
>
> >From: ��?��?<[EMAIL PROTECTED]>
> >To: [EMAIL PROTECTED], [email protected]
> >Subject: RE: optimize a sql statement
> >Date: Thu, 16 Jun 2005 10:44:39 +0800
> >
> >hello schroeder,
> >
> >I notice a phenomenon.
> >When the condition "ITEM.I_SUBJECT = 'COOKING'" be changed into
> >others,the costvalue 5443 turn to 19!!
> >For example, "ITEM.I_SUBJECT = 'qwerqwer'" or "ITEM.I_SUBJECT =
> >'COOK'" or "ITEM.I_SUBJECT = 'COOKINGG'".
> >
> >why??
> >
> >
> >>From: "Schroeder, Alexander" <[EMAIL PROTECTED]>
> >>To: ��?��?<[EMAIL PROTECTED]>, <[email protected]>
> >>Subject: RE: optimize a sql statement
> >>Date: Wed, 15 Jun 2005 08:18:34 +0200
> >>
> >>Hello Liu,
> >>
> >>I only meant that - aside from simple cases - any suggestion must
> >>take the
> >output of the EXPLAIN
> >>command into account (the complete output), so you should post it
> >>along
> >with your query. If you only
> >>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!
> >> > > >
> >> > > >
> >>_________________________________________________________________
> >> > > >
>
>>濞���虫唉娴���ュ嫉���瑙���遍�哄�����瀵����娼诲��婊���藉ù婧垮��缁�锕��
