Left outer joins & Sequential Scans. Why? 
Author Message
 Left outer joins & Sequential Scans. Why?

All,

Disclaimer: I'm not an SQL expert. Help me...I'm melting.....

Issue:

I'm trying to determine why the following SQL, which performs several
left outer joins, is performing a sequential scan on the ENROLLMENT
table, even though the appropriate indexes exist on the table.  Of
course, this is causing the SQL to run way to long. Yes, I have run
update stats several different ways (low, medium, high, distributions
only, etc, etc, etc).

I'm willing to bet that some of you SQL experts out there could make
some recommendations to make this thing fly.  Any help will be greatly
appreciated.

Thanks,

Christopher

Here's the output from SET EXPLAIN ON.

---------------------------------------------------------------------------

QUERY:
------
create view "informix".dorm
(dorm_id,ctr_id,dorm_name,dorm_cap,dorm_type_cd,dorm_active) as select
x0.dorm_id ,x0.ctr_id ,x1.code_desc ,x0.dorm_cap ,x0.dorm_type_cd
,x0.dorm_active from "informix".dorm_info x0 ,"informix".code x1 where
((x0.dorm_loc_cd = x1.code_id ) AND (x1.code_active = 'Y' ) ) ;

Estimated Cost: 125
Estimated # of Rows Returned: 88
Maximum Threads: 3

1) informix.dorm_info: SEQUENTIAL SCAN

2) informix.code: INDEX PATH

    Filters: informix.code.code_active = 'Y'

    (1) Index Keys: code_id
        Lower Index Filter: informix.code.code_id =
informix.dorm_info.dorm_loc_cd
NESTED LOOP JOIN

QUERY:
------
SELECT  informix.student.stud_id,
         informix.student.ssn,  
         informix.student.stud_lname,  
         informix.student.stud_fname,  
         informix.student.stud_mi,  
         informix.student.sex,  
         informix.dorm.dorm_id,  
         informix.student.stud_id,  
         informix.duty_status.duty_stat_cd,  
         et1.resident,  
         et1.enrtype_id,  
         et1.ctr_id,  
         informix.student.dob,  
         (select group.group_id
                                from group,
                                                student_group
                                where group.group_id =
student_group.group_id
                                and student_group.enrtype_id =
et1.enrtype_id
                                and group.group_type_cd = 1000
                        ) as group_id,
                        informix.staff.staff_id,
        informix.staff.staff_lname,
        informix.staff.staff_fname,
        informix.staff.staff_mi,
         et1.enr_id,
                        0 as sep_type_cd,
                        date(NULL) as sep_date,
                        "Y" as active
    FROM informix.duty_status,              
         informix.enrollment,  
         informix.student,
                        informix.enrollment_type et1, /*Having
enrollment_type outside of the left outer joins helps the Informix
optimizer tremendously.*/
         (informix.enrollment_type et2  
                        left outer join informix.bed           on
et2.enrtype_id = informix.bed.enrtype_id
                        left outer join informix.dorm_room     on
informix.dorm_room.dorm_rm_id       = informix.bed.dorm_rm_id
                        left outer join informix.dorm          on
informix.dorm.dorm_id               = informix.dorm_room.dorm_id
                        left outer join informix.student_group on
et2.enrtype_id = informix.student_group.enrtype_id
                        left outer join informix.group         on
informix.student_group.group_id     = informix.group.group_id
                        left outer join informix.student_staff on
et2.enrtype_id = informix.student_staff.enrtype_id
                        left outer join informix.staff                
 on informix.student_staff.staff_id              =
informix.staff.staff_id)
   WHERE ( et1.duty_stat_id = informix.duty_status.duty_stat_id ) and
                        ( informix.student.stud_id =
informix.enrollment.stud_id ) and
         ( informix.enrollment.enr_id = et1.enr_id ) and  
                        ( et1.enrtype_id = et2.enrtype_id) AND  /*
Join the two enrollment_type tables to the same record so no
duplicates. */
                        (
nvl(informix.student_staff.stud_staff_role_cd, 1044) = 1044) AND
                   ( et1.ctr_id = 76 ) AND  
         ( et1.active = "Y" )

Estimated Cost: 2828805
Estimated # of Rows Returned: 137
Maximum Threads: 0

                1) informix.enrollment: SEQUENTIAL SCAN

                2) informix.et1: INDEX PATH

                    Filters: informix.et1.active = 'Y'

                    (1) Index Keys: enr_id ctr_id arrival_dt
                        Lower Index Filter: (informix.et1.ctr_id = 76
AND informix.et1.enr_id = informix.enrollment.enr_id )
                NESTED LOOP JOIN

            3) informix.duty_status: INDEX PATH

                (1) Index Keys: duty_stat_id
                    Lower Index Filter:
informix.duty_status.duty_stat_id = informix.et1.duty_stat_id
            NESTED LOOP JOIN

        4) informix.student: INDEX PATH

            (1) Index Keys: stud_id
                Lower Index Filter: informix.student.stud_id =
informix.enrollment.stud_id
        NESTED LOOP JOIN

                                5) informix.et2: INDEX PATH

                                    (1) Index Keys: enrtype_id  
(Key-Only)
                                        Lower Index Filter:
informix.et2.enrtype_id = informix.et1.enrtype_id

                                6) informix.bed: INDEX PATH

                                    (1) Index Keys: enrtype_id
                                        Lower Index Filter:
informix.bed.enrtype_id = informix.et2.enrtype_id

                                ON-Filters:informix.et2.enrtype_id =
informix.bed.enrtype_id
                                NESTED LOOP JOIN(LEFT OUTER JOIN)

                            7) informix.dorm_room: INDEX PATH

                                (1) Index Keys: dorm_rm_id
                                    Lower Index Filter:
informix.dorm_room.dorm_rm_id = informix.bed.dorm_rm_id

                            ON-Filters:informix.dorm_room.dorm_rm_id =
informix.bed.dorm_rm_id
                            NESTED LOOP JOIN(LEFT OUTER JOIN)

                        8) (Temp Table For View): AUTOINDEX PATH

                            (1) Index Keys: dorm_id
                                Lower Index Filter: (Temp Table For
View).dorm_id = informix.dorm_room.dorm_id

                        ON-Filters:(Temp Table For View).dorm_id =
informix.dorm_room.dorm_id
                        NESTED LOOP JOIN(LEFT OUTER JOIN)

                    9) informix.student_group: INDEX PATH

                        (1) Index Keys: enrtype_id
                            Lower Index Filter:
informix.student_group.enrtype_id = informix.et2.enrtype_id

                    ON-Filters:informix.et2.enrtype_id =
informix.student_group.enrtype_id
                    NESTED LOOP JOIN(LEFT OUTER JOIN)

                10) informix.group: INDEX PATH

                    (1) Index Keys: group_id   (Key-Only)
                        Lower Index Filter: informix.group.group_id =
informix.student_group.group_id

                ON-Filters:informix.student_group.group_id =
informix.group.group_id
                NESTED LOOP JOIN(LEFT OUTER JOIN)

            11) informix.student_staff: INDEX PATH

                (1) Index Keys: enrtype_id staff_id stud_staff_role_cd
  (Key-Only)
                    Lower Index Filter:
informix.student_staff.enrtype_id = informix.et2.enrtype_id

            ON-Filters:informix.et2.enrtype_id =
informix.student_staff.enrtype_id
            NESTED LOOP JOIN(LEFT OUTER JOIN)

        12) informix.staff: INDEX PATH

            (1) Index Keys: staff_id
                Lower Index Filter: informix.staff.staff_id =
informix.student_staff.staff_id

        ON-Filters:informix.student_staff.staff_id =
informix.staff.staff_id
        NESTED LOOP JOIN(LEFT OUTER JOIN)
    NESTED LOOP JOIN

PostJoin-Filters:((((((informix.et1.duty_stat_id =
informix.duty_status.duty_stat_id AND informix.student.stud_id =
informix.enrollment.stud_id ) AND informix.enrollment.enr_id =
informix.et1.enr_id ) AND informix.et1.enrtype_id =
informix.et2.enrtype_id ) AND NVL
(informix.student_staff.stud_staff_role_cd , 1044 ) = 1044 ) AND
informix.et1.ctr_id = 76 ) AND informix.et1.active = 'Y' )



Tue, 22 Jun 2004 23:30:29 GMT
 Left outer joins & Sequential Scans. Why?


Quote:
> All,

> Disclaimer: I'm not an SQL expert. Help me...I'm melting.....

> Issue:

> I'm trying to determine why the following SQL, which performs several
> left outer joins, is performing a sequential scan on the ENROLLMENT
> table, even though the appropriate indexes exist on the table.  Of
> course, this is causing the SQL to run way to long. Yes, I have run
> update stats several different ways (low, medium, high, distributions
> only, etc, etc, etc).

> I'm willing to bet that some of you SQL experts out there could make
> some recommendations to make this thing fly.  Any help will be greatly
> appreciated.

> Thanks,

> Christopher

> Here's the output from SET EXPLAIN ON.

Only two qustions:
  1. how mach records in dorm_info?
  2. if you directly try use index (with hints) - is query work faster?


Wed, 23 Jun 2004 00:23:04 GMT
 Left outer joins & Sequential Scans. Why?

What IDS version are you using?

There have been numerous Informix software defects involving ANSI
left outer joins.  They appear to be mostly resolved with IDS 7.31.UD3.
If you are using an older version of IDS 7.31, try upgrading.
There is probably an IDS 9.21x version which also incorporates
similar fixes.

Good luck,
Rick

Quote:
-----Original Message-----

Sent: Friday, January 04, 2002 07:30

Subject: Left outer joins & Sequential Scans. Why?

All,

Disclaimer: I'm not an SQL expert. Help me...I'm melting.....

Issue:

I'm trying to determine why the following SQL, which performs several
left outer joins, is performing a sequential scan on the ENROLLMENT
table, even though the appropriate indexes exist on the table.  Of
course, this is causing the SQL to run way to long. Yes, I have run
update stats several different ways (low, medium, high, distributions
only, etc, etc, etc).

I'm willing to bet that some of you SQL experts out there could make
some recommendations to make this thing fly.  Any help will be greatly
appreciated.

Thanks,

Christopher

Here's the output from SET EXPLAIN ON.

---------------------------------------------------------------------------

QUERY:
------
create view "informix".dorm
(dorm_id,ctr_id,dorm_name,dorm_cap,dorm_type_cd,dorm_active) as select
x0.dorm_id ,x0.ctr_id ,x1.code_desc ,x0.dorm_cap ,x0.dorm_type_cd
,x0.dorm_active from "informix".dorm_info x0 ,"informix".code x1 where
((x0.dorm_loc_cd = x1.code_id ) AND (x1.code_active = 'Y' ) ) ;

Estimated Cost: 125
Estimated # of Rows Returned: 88
Maximum Threads: 3

1) informix.dorm_info: SEQUENTIAL SCAN

2) informix.code: INDEX PATH

    Filters: informix.code.code_active = 'Y'

    (1) Index Keys: code_id
        Lower Index Filter: informix.code.code_id =
informix.dorm_info.dorm_loc_cd
NESTED LOOP JOIN

QUERY:
------
SELECT  informix.student.stud_id,
         informix.student.ssn,  
         informix.student.stud_lname,  
         informix.student.stud_fname,  
         informix.student.stud_mi,  
         informix.student.sex,  
         informix.dorm.dorm_id,  
         informix.student.stud_id,  
         informix.duty_status.duty_stat_cd,  
         et1.resident,  
         et1.enrtype_id,  
         et1.ctr_id,  
         informix.student.dob,  
         (select group.group_id
                                from group,
                                                student_group
                                where group.group_id =
student_group.group_id
                                and student_group.enrtype_id =
et1.enrtype_id
                                and group.group_type_cd = 1000
                        ) as group_id,
                        informix.staff.staff_id,
        informix.staff.staff_lname,
        informix.staff.staff_fname,
        informix.staff.staff_mi,
         et1.enr_id,
                        0 as sep_type_cd,
                        date(NULL) as sep_date,
                        "Y" as active
    FROM informix.duty_status,              
         informix.enrollment,  
         informix.student,
                        informix.enrollment_type et1, /*Having
enrollment_type outside of the left outer joins helps the Informix
optimizer tremendously.*/
         (informix.enrollment_type et2  
                        left outer join informix.bed           on
et2.enrtype_id = informix.bed.enrtype_id
                        left outer join informix.dorm_room     on
informix.dorm_room.dorm_rm_id       = informix.bed.dorm_rm_id
                        left outer join informix.dorm          on
informix.dorm.dorm_id               = informix.dorm_room.dorm_id
                        left outer join informix.student_group on
et2.enrtype_id = informix.student_group.enrtype_id
                        left outer join informix.group         on
informix.student_group.group_id     = informix.group.group_id
                        left outer join informix.student_staff on
et2.enrtype_id = informix.student_staff.enrtype_id
                        left outer join informix.staff                
 on informix.student_staff.staff_id              =
informix.staff.staff_id)
   WHERE ( et1.duty_stat_id = informix.duty_status.duty_stat_id ) and
                        ( informix.student.stud_id =
informix.enrollment.stud_id ) and
         ( informix.enrollment.enr_id = et1.enr_id ) and  
                        ( et1.enrtype_id = et2.enrtype_id) AND  /*
Join the two enrollment_type tables to the same record so no
duplicates. */
                        (
nvl(informix.student_staff.stud_staff_role_cd, 1044) = 1044) AND
                   ( et1.ctr_id = 76 ) AND  
         ( et1.active = "Y" )

Estimated Cost: 2828805
Estimated # of Rows Returned: 137
Maximum Threads: 0

                1) informix.enrollment: SEQUENTIAL SCAN

                2) informix.et1: INDEX PATH

                    Filters: informix.et1.active = 'Y'

                    (1) Index Keys: enr_id ctr_id arrival_dt
                        Lower Index Filter: (informix.et1.ctr_id = 76
AND informix.et1.enr_id = informix.enrollment.enr_id )
                NESTED LOOP JOIN

            3) informix.duty_status: INDEX PATH

                (1) Index Keys: duty_stat_id
                    Lower Index Filter:
informix.duty_status.duty_stat_id = informix.et1.duty_stat_id
            NESTED LOOP JOIN

        4) informix.student: INDEX PATH

            (1) Index Keys: stud_id
                Lower Index Filter: informix.student.stud_id =
informix.enrollment.stud_id
        NESTED LOOP JOIN

                                5) informix.et2: INDEX PATH

                                    (1) Index Keys: enrtype_id  
(Key-Only)
                                        Lower Index Filter:
informix.et2.enrtype_id = informix.et1.enrtype_id

                                6) informix.bed: INDEX PATH

                                    (1) Index Keys: enrtype_id
                                        Lower Index Filter:
informix.bed.enrtype_id = informix.et2.enrtype_id

                                ON-Filters:informix.et2.enrtype_id =
informix.bed.enrtype_id
                                NESTED LOOP JOIN(LEFT OUTER JOIN)

                            7) informix.dorm_room: INDEX PATH

                                (1) Index Keys: dorm_rm_id
                                    Lower Index Filter:
informix.dorm_room.dorm_rm_id = informix.bed.dorm_rm_id

                            ON-Filters:informix.dorm_room.dorm_rm_id =
informix.bed.dorm_rm_id
                            NESTED LOOP JOIN(LEFT OUTER JOIN)

                        8) (Temp Table For View): AUTOINDEX PATH

                            (1) Index Keys: dorm_id
                                Lower Index Filter: (Temp Table For
View).dorm_id = informix.dorm_room.dorm_id

                        ON-Filters:(Temp Table For View).dorm_id =
informix.dorm_room.dorm_id
                        NESTED LOOP JOIN(LEFT OUTER JOIN)

                    9) informix.student_group: INDEX PATH

                        (1) Index Keys: enrtype_id
                            Lower Index Filter:
informix.student_group.enrtype_id = informix.et2.enrtype_id

                    ON-Filters:informix.et2.enrtype_id =
informix.student_group.enrtype_id
                    NESTED LOOP JOIN(LEFT OUTER JOIN)

                10) informix.group: INDEX PATH

                    (1) Index Keys: group_id   (Key-Only)
                        Lower Index Filter: informix.group.group_id =
informix.student_group.group_id

                ON-Filters:informix.student_group.group_id =
informix.group.group_id
                NESTED LOOP JOIN(LEFT OUTER JOIN)

            11) informix.student_staff: INDEX PATH

                (1) Index Keys: enrtype_id staff_id stud_staff_role_cd
  (Key-Only)
                    Lower Index Filter:
informix.student_staff.enrtype_id = informix.et2.enrtype_id

            ON-Filters:informix.et2.enrtype_id =
informix.student_staff.enrtype_id
            NESTED LOOP JOIN(LEFT OUTER JOIN)

        12) informix.staff: INDEX PATH

            (1) Index Keys: staff_id
                Lower Index Filter: informix.staff.staff_id =
informix.student_staff.staff_id

        ON-Filters:informix.student_staff.staff_id =
informix.staff.staff_id
        NESTED LOOP JOIN(LEFT OUTER JOIN)
    NESTED LOOP JOIN

PostJoin-Filters:((((((informix.et1.duty_stat_id =
informix.duty_status.duty_stat_id AND informix.student.stud_id =
informix.enrollment.stud_id ) AND informix.enrollment.enr_id =
informix.et1.enr_id ) AND informix.et1.enrtype_id =
informix.et2.enrtype_id ) AND NVL
(informix.student_staff.stud_staff_role_cd , 1044 ) = 1044 ) AND
informix.et1.ctr_id = 76 ) AND informix.et1.active = 'Y' )



Wed, 23 Jun 2004 01:27:53 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. LEFT OUTER LOOP JOIN and LEFT OUTER JOIN

2. LEFT JOIN and LEFT OUTER JOIN

3. LEFT JOIN and LEFT OUTER JOIN

4. Left-Outer join and Right-Outer join

5. Left outer join works, Right outer join just goes on for ever, processor at 100%

6. LEFT OUTER JOIN cause TABLE LOCK why ??

7. LEFT OUTER JOIN vs RIGHT OUTER JION

8. LEFT OUTER JOIN & inheritance

9. LEFT OUTER JOIN faster than INNER JOIN?

10. Left Outer join on Inner join

11. left outer join on Inner join

12. Here is a challenge: 2 inner joins and 1 left outer join


 
Powered by phpBB® Forum Software