当前在线人数12368
首页 - 分类讨论区 - 电脑网络 - 数据库版 -阅读文章
未名交友
[更多]
[更多]
文章阅读:Re: 紧急请教SQL tuning高手一个问题
[同主题阅读] [版面: 数据库] [作者:wyr] , 2007年07月30日23:05:21
wyr
进入未名形象秀
我的博客
[上篇] [下篇] [同主题上篇] [同主题下篇]

发信人: wyr (遗忘小资), 信区: Database
标  题: Re: 紧急请教SQL tuning高手一个问题
发信站: BBS 未名空间站 (Mon Jul 30 23:05:21 2007)

build a tree diagram of your query you will find that you have several
tables scanned multiple times

i re-write your query by eliminating the IN() construct. Usually the IN
construct is not well optimized in most of RDBMS system. In addition to this
, I believe you can use OLAP MAX() PARTITION BY() function to eliminate the
cs_of_date subquery.

SELECT
    DECODE (LENGTH (e.e_last_name),0, u.user_id, e.e_last_name || ', ' || e.
e_fi
    rst_name) salesperson_name,
    t.ca_care_id AS account_number, t.t_mobile_number AS mobile_number,
    c.cs_id AS transaction_number,
    TO_DATE (cs_date_of_creation, 'DD-MON-YYYY') AS date_time_sold,
    ca.ca_last_name || ',' || ca.ca_first_name AS customer_name,
    si.si_sku AS item_number, si.si_original_price AS default_price,
    si.si_unit_price AS net_sale, si.si_serial_number AS serial_number
FROM     USERS u
    INNER JOIN
    EMPLOYEE e
    ON(
    u.e_number = e.e_number
    )
    INNER JOIN
    TRANSACTIONS t
    ON(
    t.user_id = u.user_id
    )
    INNER JOIN
    SALE_ITEMS si
    ON(
    si.cs_id = t.cs_id    AND
    si.t_id = t.t_id
    )
    INNER JOIN
    CUSTOMER_ACCOUNT ca
    ON(
    t.ca_care_id = ca.ca_care_id
    )
    INNER JOIN
    CUSTOMER_SESSION c
    ON(
        c.cs_id = t.cs_id
    )
    INNER JOIN (
    SELECT       c1.cs_id
    FROM        BOB_PHONE_SALE_INFO b
           INNER JOIN
           TRANSACTIONS t1
           ON(
            b.cs_id = t1.cs_id    AND
            b.t_id = t1.t_id
           )
           INNER JOIN
           CUSTOMER_SESSION c1
           ON
           (
            c1.cs_id = t1.cs_id
           )
    WHERE       b.deposit_posting_date IS NULL
           AND c.cs_date_of_creation >= TO_DATE ('07-JUL-2007', 'DD-MON-YYYY
')
           AND c.cs_date_of_creation < TO_DATE ('09-JUL-2007', 'DD-MON-YYYY')
    GROUP BY   c1.cs_id
    ) bob
    ON(
    c.cs_id = bob.cs_id
    )
WHERE    ca_as_of_date = (
            SELECT    MAX(ca_as_of_date)
            FROM     CUSTOMER_ACCOUNT
            WHERE     ca_care_id = t.ca_care_id AND
                ca_as_of_date <= c.cs_date_of_creation
            )

--

※ 来源:·WWW 未名空间站 海外: mitbbs.com 中国: mitbbs.cn·[FROM: 68.53.]

[上篇] [下篇] [同主题上篇] [同主题下篇]
[转寄] [转贴] [回信给作者] [修改文章] [删除文章] [同主题阅读] [从此处展开] [返回版面] [快速返回] [收藏] [举报]
 
回复文章
标题:
内 容:

未名交友
将您的链接放在这儿

友情链接


 

Site Map - Contact Us - Terms and Conditions - Privacy Policy

版权所有,未名空间(mitbbs.com),since 1996