Table scans with large table joined to small table

Table scans with large table joined to small table

Post by rachyut » Sat, 06 Jul 1996 04:00:00




> We recently upgraded from Sybase 4.9 to Sybase 11.  We are now having a
> problem with table scans, something which did not occur in 4.9.  Our large
> table contains a column with NULLS not allowed.  Our small table contains
> a column with NULLS allowed.  With Sybase 4.9, the optimizer would join
> these two tables using indexes, but with Sybase 11 the optimizer table scans
> the large table.  Our resolution has been to bcp out the small table, change
> the column to not allow NULLS and bcp the table back in. Does anyone know of
> a better fix than this, or if this is a bug?

> Much thanks.

I suspect this is the bug reported in the ebf 6303 (the bug id
is 91387), Does the query do an outer join  on the two tables
(large and small)?
        Sybase seems to roll out the fix for this problem in
11.02.

cheers,
Ramesh

 
 
 

1. Table scans with large table joined to small table

I too have noticed this situation when upgrading from 4.9 to 10.x.  The
optimizer would not choose the obvious clustered index in the child table.
I ended up forcing the index.

: We recently upgraded from Sybase 4.9 to Sybase 11.  We are now having a
: problem with table scans, something which did not occur in 4.9.  Our large
: table contains a column with NULLS not allowed.  Our small table contains
: a column with NULLS allowed.  With Sybase 4.9, the optimizer would join
: these two tables using indexes, but with Sybase 11 the optimizer table scans
: the large table.  Our resolution has been to bcp out the small table, change
: the column to not allow NULLS and bcp the table back in. Does anyone know of
: a better fix than this, or if this is a bug?

: Much thanks.

--
+==============================+=============================================+
| David W. Pledger             | S T R A T E G I C   D A T A   S Y S T E M S |

| Custom Database Applications |        Phone: (800)253-5624 ext 2940        |
+==============================+=============================================+

2. SQL anyhwere 5.5 vs SQL7 on Win95

3. Join of small table with large table

4. Copy all field values

5. Table scan, Table scan, Table scan

6. 16 bit SQL*Net for 7.3.4

7. Transactional Replication from a large table to a smaller table using criteria in a related table

8. Table Scan on very small table...

9. Query Optimizer ?-small tables/table scan

10. Many smaller tables or one large table?

11. One large dimention table Vs two smaller dimention tables

12. One large table vs many small tables