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枚der
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 Messenger:
http://messenger.msn.com/cn
>
>
> --
> MaxDB Discussion Mailing List
> For list archives: http://lists.mysql.com/maxdb
> To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
>
>
_________________________________________________________________
享用世界上最大的电子邮件系统― MSN Hotmail。 http://www.hotmail.com
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
