DB2关联查询慢，帮忙分析一下执行计划

DB2的两张表做了笛卡尔积关联，并卡了相应的条件。在SQLSERVER中执行6秒钟就执行完了，但是在DB2里执行了50分钟。下面是执行计划，我看不懂，请高手帮忙分析下，怎么改就快了。DB2 Universal Database Version 10.1, 5622-044 (c) Copyright IBM Corp. 1991, 2011
Licensed Material - Program Property of IBM
IBM DATABASE 2 Explain Table Format Tool



******************** EXPLAIN INSTANCE ********************

DB2_VERSION:       10.01.4
SOURCE_NAME:       SQLC2J25
SOURCE_SCHEMA:     NULLID  
SOURCE_VERSION:      
EXPLAIN_TIME:      2014-12-08-14.55.00.829608
EXPLAIN_REQUESTER: DB2INST1

Database Context:
----------------

Parallelism:          Inter-Partition Parallelism

CPU Speed:            1.889377e-07

Comm Speed:           100

Buffer Pool size:     656360

Sort Heap size:       256

Database Heap size:   1200

Lock List size:       4096

Maximum Lock List:    10

Average Applications: 1

Locks Available:      13107

Package Context:
---------------

SQL Type:           Dynamic

Optimization Level: 5

Blocking:           Block All Cursors

Isolation Level:    Cursor Stability



---------------- STATEMENT 1  SECTION 201 ----------------

QUERYNO:       1

QUERYTAG:      CLP                 

Statement Type:   Select

Updatable:        No

Deletable:        No

Query Degree:     1

Original Statement:
------------------

SELECT  a.ne_id
,b.GRID_ID
,A.REGION_NAME
,A.CITY_NAME
,b.long_min
,b.long_max
,b.lat_min
,b.lat_max
,a.city_name2
FROM  pmart.TB_FACT_NEW_GRID_INFO b
,pmart.LTE_CELL_GRID_500  A
where  (a.long >= b.long_min and a.long    and
            (a.lat>=b.lat_min and a.lat
Optimized Statement:
-------------------
SELECT
  Q1.NE_ID AS "NE_ID",
  Q2.GRID_ID AS "GRID_ID",
  Q1.REGION_NAME AS "REGION_NAME",
  Q1.CITY_NAME AS "CITY_NAME",
  Q2.LONG_MIN AS "LONG_MIN",
  Q2.LONG_MAX AS "LONG_MAX",
  Q2.LAT_MIN AS "LAT_MIN",
  Q2.LAT_MAX AS "LAT_MAX",
  Q1.CITY_NAME2 AS "CITY_NAME2"
FROM
  PMART.LTE_CELL_GRID_500 AS Q1,
  PMART.TB_FACT_NEW_GRID_INFO AS Q2
WHERE
  (Q1.LAT < Q2.LAT_MAX) AND
  (Q2.LAT_MIN <= Q1.LAT) AND
  (Q1.LONG < Q2.LONG_MAX) AND
  (Q2.LONG_MIN <= Q1.LONG)

Access Plan:
-----------

Total Cost:
8.56899e+06

Query Degree:
1

                Rows
               RETURN
               (   1)
                Cost
                 I/O
                 |
             4.60225e+09
               DTQ   
               (   2)
             8.56899e+06
                 290
                 |
             7.67041e+08
               NLJOIN
               /----+  (   3)
             7.34906e+06
                 290
          ----
      1.55982e+06     491.75
        BTQ           TBSCAN
        (   4)        (   6)
        738.79        89.8634
          278           12
          |             |
        259970         7868
        TBSCAN    TABLE: PMART   
        (   5)   LTE_CELL_GRID_500
        417.995         Q1
          278
          |
        259970
    TABLE: PMART   
TB_FACT_NEW_GRID_INFO
          Q2


Operator Symbols :
------------------

   Symbol      Description
   ---------   ------------------------------------------
   ATQ       : Asynchrony
   BTQ       : Broadcast
   DTQ       : Directed
   LTQ       : Intra-partition parallelism
   MTQ       : Merging (sorted)
   STQ       : Scatter
   XTQ       : XML aggregation
    TQ*      : Listener


Extended Diagnostic Information:
--------------------------------

No extended Diagnostic Information for this statement.


Plan Details:
-------------



1) RETURN: (Return Result)

Cumulative Total Cost:
8.56899e+06

Cumulative CPU Cost:
4.53514e+13

Cumulative I/O Cost:
290

Cumulative Re-Total Cost:
8.56857e+06

Cumulative Re-CPU Cost:
4.53513e+13

Cumulative Re-I/O Cost:
0

Cumulative First Row Cost:
14.3113

Cumulative Comm Cost:
3.14112e+08

Cumulative First Comm Cost:
0

Estimated Bufferpool Buffers:
290


Arguments:

---------

BLDLEVEL: (Build level)

DB2 v10.1.0.4 : s140509

HEAPUSE : (Maximum Statement Heap Usage)

112 Pages

PREPNODE: (Prepare Node Number)

0

PREPTIME: (Statement prepare time)

        38 milliseconds

STMTHEAP: (Statement heap size)

8192


Input Streams:

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

7) From Operator #2


Estimated number of rows:
4.60225e+09

Partition Map ID:  
-100

Partitioning:      
(COOR )

Coordinator Partition

Number of columns:
9

Subquery predicate ID:
Not Applicable


Column Names:

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

+Q3.CITY_NAME2+Q3.LAT_MAX+Q3.LAT_MIN

+Q3.LONG_MAX+Q3.LONG_MIN+Q3.CITY_NAME

+Q3.REGION_NAME+Q3.GRID_ID+Q3.NE_ID


Partition Column Names:

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

+NONE



2) TQ    : (Table Queue)

Cumulative Total Cost:
8.56899e+06

Cumulative CPU Cost:
4.53514e+13

Cumulative I/O Cost:
290

Cumulative Re-Total Cost:
8.56857e+06

Cumulative Re-CPU Cost:
4.53513e+13

Cumulative Re-I/O Cost:
0

Cumulative First Row Cost:
14.3113

Cumulative Comm Cost:
3.14112e+08

Cumulative First Comm Cost:
0

Estimated Bufferpool Buffers:
290


Arguments:

---------

LISTENER: (Listener Table Queue type)

FALSE

TQMERGE : (Merging Table Queue flag)

FALSE

TQNUMBER: (Runtime Table Queue number)

1

TQREAD  : (Table Queue Read type)

READ AHEAD

TQSECNFM: (Runtime Table Queue Receives From Section #)

1

TQSECNTO: (Runtime Table Queue Sends to Section #)

0

TQSEND  : (Table Queue Write type)

DIRECTED

UNIQUE  : (Uniqueness required flag)

FALSE


Input Streams:

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

6) From Operator #3


Estimated number of rows:
7.67041e+08

Partition Map ID:  
3

Partitioning:      
(MULT )

Multiple Partitions

Number of columns:
9

Subquery predicate ID:
Not Applicable


Column Names:

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

+Q3.CITY_NAME2+Q3.LAT_MAX+Q3.LAT_MIN

+Q3.LONG_MAX+Q3.LONG_MIN+Q3.CITY_NAME

+Q3.REGION_NAME+Q3.GRID_ID+Q3.NE_ID


Partition Column Names:

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

+1: Q3.NE_ID



Output Streams:

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

7) To Operator #1


Estimated number of rows:
4.60225e+09

Partition Map ID:  
-100

Partitioning:      
(COOR )

Coordinator Partition

Number of columns:
9

Subquery predicate ID:
Not Applicable


Column Names:

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

+Q3.CITY_NAME2+Q3.LAT_MAX+Q3.LAT_MIN

+Q3.LONG_MAX+Q3.LONG_MIN+Q3.CITY_NAME

+Q3.REGION_NAME+Q3.GRID_ID+Q3.NE_ID


Partition Column Names:

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

+NONE



3) NLJOIN: (Nested Loop Join)

Cumulative Total Cost:
7.34906e+06

Cumulative CPU Cost:
3.88946e+13

Cumulative I/O Cost:
290

Cumulative Re-Total Cost:
7.34864e+06

Cumulative Re-CPU Cost:
3.88945e+13

Cumulative Re-I/O Cost:
0

Cumulative First Row Cost:
14.2965

Cumulative Comm Cost:
322313

Cumulative First Comm Cost:
0

Estimated Bufferpool Buffers:
290


Arguments:

---------

EARLYOUT: (Early Out flag)

NONE

FETCHMAX: (Override for FETCH MAXPAGES)

IGNORE

ISCANMAX: (Override for ISCAN MAXPAGES)

IGNORE


Predicates:

----------

2) Predicate used in Join,

Comparison Operator:
Less Than (<)

Subquery Input Required:
No

Filter Factor:
0.499923


Predicate Text:

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

(Q1.LAT < Q2.LAT_MAX)



3) Predicate used in Join,

Comparison Operator:
Less Than or Equal (<=)

Subquery Input Required:
No

Filter Factor:
0.500077


Predicate Text:

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

(Q2.LAT_MIN <= Q1.LAT)



4) Predicate used in Join,

Comparison Operator:
Less Than (<)

Subquery Input Required:
No

Filter Factor:
0.499919


Predicate Text:

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

(Q1.LONG < Q2.LONG_MAX)



5) Predicate used in Join,

Comparison Operator:
Less Than or Equal (<=)

Subquery Input Required:
No

Filter Factor:
0.500081


Predicate Text:

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

(Q2.LONG_MIN <= Q1.LONG)




Input Streams:

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

3) From Operator #4


Estimated number of rows:
1.55982e+06

Partition Map ID:  
3

Partitioning:      
(REPL )

Replicated Data on all nodes

Number of columns:
5

Subquery predicate ID:
Not Applicable


Column Names:

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

+Q2.GRID_ID+Q2.LAT_MAX+Q2.LAT_MIN+Q2.LONG_MAX

+Q2.LONG_MIN


Partition Column Names:

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

+NONE


5) From Operator #6


Estimated number of rows:
491.75

Partition Map ID:  
3

Partitioning:      
(MULT )

Multiple Partitions

Number of columns:
4

Subquery predicate ID:
Not Applicable


Column Names:

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

+Q1.CITY_NAME2+Q1.CITY_NAME+Q1.REGION_NAME

+Q1.NE_ID


Partition Column Names:

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

+1: Q1.NE_ID



Output Streams:

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

6) To Operator #2


Estimated number of rows:
7.67041e+08

Partition Map ID:  
3

Partitioning:      
(MULT )

Multiple Partitions

Number of columns:
9

Subquery predicate ID:
Not Applicable


Column Names:

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

+Q3.CITY_NAME2+Q3.LAT_MAX+Q3.LAT_MIN

+Q3.LONG_MAX+Q3.LONG_MIN+Q3.CITY_NAME

+Q3.REGION_NAME+Q3.GRID_ID+Q3.NE_ID


Partition Column Names:

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

+1: Q3.NE_ID



4) TQ    : (Table Queue)

Cumulative Total Cost:
738.79

Cumulative CPU Cost:
2.1936e+09

Cumulative I/O Cost:
278

Cumulative Re-Total Cost:
405.377

Cumulative Re-CPU Cost:
2.14556e+09

Cumulative Re-I/O Cost:
0

Cumulative First Row Cost:
7.08335

Cumulative Comm Cost:
322313

Cumulative First Comm Cost:
0

Estimated Bufferpool Buffers:
278


Arguments:

---------

JN INPUT: (Join input leg)

OUTER

LISTENER: (Listener Table Queue type)

FALSE

TQMERGE : (Merging Table Queue flag)

FALSE

TQNUMBER: (Runtime Table Queue number)

2

TQREAD  : (Table Queue Read type)

READ AHEAD

TQSECNFM: (Runtime Table Queue Receives From Section #)

2

TQSECNTO: (Runtime Table Queue Sends to Section #)

1

TQSEND  : (Table Queue Write type)

BROADCAST

UNIQUE  : (Uniqueness required flag)

FALSE


Input Streams:

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

2) From Operator #5


Estimated number of rows:
259970

Partition Map ID:  
3

Partitioning:      
(MULT )

Multiple Partitions

Number of columns:
5

Subquery predicate ID:
Not Applicable


Column Names:

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

+Q2.GRID_ID+Q2.LAT_MAX+Q2.LAT_MIN+Q2.LONG_MAX

+Q2.LONG_MIN


Partition Column Names:

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

+1: Q2.GRID_ID



Output Streams:

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

3) To Operator #3


Estimated number of rows:
1.55982e+06

Partition Map ID:  
3

Partitioning:      
(REPL )

Replicated Data on all nodes

Number of columns:
5

Subquery predicate ID:
Not Applicable


Column Names:

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

+Q2.GRID_ID+Q2.LAT_MAX+Q2.LAT_MIN+Q2.LONG_MAX

+Q2.LONG_MIN


Partition Column Names:

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

+NONE



5) TBSCAN: (Table Scan)

Cumulative Total Cost:
417.995

Cumulative CPU Cost:
4.95717e+08

Cumulative I/O Cost:
278

Cumulative Re-Total Cost:
84.5817

Cumulative Re-CPU Cost:
4.4767e+08

Cumulative Re-I/O Cost:
0

Cumulative First Row Cost:
7.05959

Cumulative Comm Cost:
0

Cumulative First Comm Cost:
0

Estimated Bufferpool Buffers:
278


Arguments:

---------

CUR_COMM: (Currently Committed)

TRUE

LCKAVOID: (Lock Avoidance)

TRUE

MAXPAGES: (Maximum pages for prefetch)

ALL

PREFETCH: (Type of Prefetch)

SEQUENTIAL         

ROWLOCK : (Row Lock intent)

SHARE (CS/RS)

SCANDIR : (Scan Direction)

FORWARD

SKIP_INS: (Skip Inserted Rows)

TRUE

SPEED   : (Assumed speed of scan, in sharing structures)

SLOW

TABLOCK : (Table Lock intent)

INTENT SHARE

TBISOLVL: (Table access Isolation Level)

CURSOR STABILITY

THROTTLE: (Scan may be throttled, for scan sharing)

TRUE

VISIBLE : (May be included in scan sharing structures)

TRUE

WRAPPING: (Scan may start anywhere and wrap)

TRUE


Input Streams:

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

1) From Object PMART.TB_FACT_NEW_GRID_INFO


Estimated number of rows:
259970

Partition Map ID:  
3

Partitioning:      
(MULT )

Multiple Partitions

Number of columns:
6

Subquery predicate ID:
Not Applicable


Column Names:

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

+Q2.$RID$+Q2.GRID_ID+Q2.LAT_MAX+Q2.LAT_MIN

+Q2.LONG_MAX+Q2.LONG_MIN


Partition Column Names:

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

+1: Q2.GRID_ID



Output Streams:

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

2) To Operator #4


Estimated number of rows:
259970

Partition Map ID:  
3

Partitioning:      
(MULT )

Multiple Partitions

Number of columns:
5

Subquery predicate ID:
Not Applicable


Column Names:

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

+Q2.GRID_ID+Q2.LAT_MAX+Q2.LAT_MIN+Q2.LONG_MAX

+Q2.LONG_MIN


Partition Column Names:

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

+1: Q2.GRID_ID



6) TBSCAN: (Table Scan)

Cumulative Total Cost:
89.8634

Cumulative CPU Cost:
2.81755e+07

Cumulative I/O Cost:
12

Cumulative Re-Total Cost:
4.71095

Cumulative Re-CPU Cost:
2.49339e+07

Cumulative Re-I/O Cost:
0

Cumulative First Row Cost:
7.2131

Cumulative Comm Cost:
0

Cumulative First Comm Cost:
0

Estimated Bufferpool Buffers:
12


Arguments:

---------

CUR_COMM: (Currently Committed)

TRUE

JN INPUT: (Join input leg)

INNER

LCKAVOID: (Lock Avoidance)

TRUE

MAXPAGES: (Maximum pages for prefetch)

ALL

PREFETCH: (Type of Prefetch)

NONE               

ROWLOCK : (Row Lock intent)

SHARE (CS/RS)

SCANDIR : (Scan Direction)

FORWARD

SKIP_INS: (Skip Inserted Rows)

TRUE

SPEED   : (Assumed speed of scan, in sharing structures)

FAST

TABLOCK : (Table Lock intent)

INTENT SHARE

TBISOLVL: (Table access Isolation Level)

CURSOR STABILITY

THROTTLE: (Scan may be throttled, for scan sharing)

TRUE

VISIBLE : (May be included in scan sharing structures)

TRUE

WRAPPING: (Scan may start anywhere and wrap)

TRUE


Predicates:

----------

2) Sargable Predicate,

Comparison Operator:
Less Than (<)

Subquery Input Required:
No

Filter Factor:
0.499923


Predicate Text:

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

(Q1.LAT < Q2.LAT_MAX)



3) Sargable Predicate,

Comparison Operator:
Less Than or Equal (<=)

Subquery Input Required:
No

Filter Factor:
0.500077


Predicate Text:

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

(Q2.LAT_MIN <= Q1.LAT)



4) Sargable Predicate,

Comparison Operator:
Less Than (<)

Subquery Input Required:
No

Filter Factor:
0.499919


Predicate Text:

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

(Q1.LONG < Q2.LONG_MAX)



5) Sargable Predicate,

Comparison Operator:
Less Than or Equal (<=)

Subquery Input Required:
No

Filter Factor:
0.500081


Predicate Text:

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

(Q2.LONG_MIN <= Q1.LONG)




Input Streams:

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

4) From Object PMART.LTE_CELL_GRID_500


Estimated number of rows:
7868

Partition Map ID:  
3

Partitioning:      
(MULT )

Multiple Partitions

Number of columns:
7

Subquery predicate ID:
Not Applicable


Column Names:

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

+Q1.$RID$+Q1.CITY_NAME2+Q1.CITY_NAME

+Q1.REGION_NAME+Q1.NE_ID+Q1.LAT+Q1.LONG


Partition Column Names:

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

+1: Q1.NE_ID



Output Streams:

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

5) To Operator #3


Estimated number of rows:
491.75

Partition Map ID:  
3

Partitioning:      
(MULT )

Multiple Partitions

Number of columns:
4

Subquery predicate ID:
Not Applicable


Column Names:

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

+Q1.CITY_NAME2+Q1.CITY_NAME+Q1.REGION_NAME

+Q1.NE_ID


Partition Column Names:

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

+1: Q1.NE_ID


Objects Used in Access Plan:
---------------------------


Schema: PMART   

Name:
LTE_CELL_GRID_500

Type:
Table

Time of creation:
2014-12-08-10.54.55.623798

Last statistics update:
2014-12-08-14.54.45.241985

Number of columns:
6

Number of rows:
7868

Width of rows:
66

Number of buffer pool pages:
12

Number of data partitions:
1

Distinct row values:
No

Tablespace name:
PMARTSPACE

Tablespace overhead:
6.725000

Tablespace transfer rate:
0.320000

Source for statistics:
Single Node

Prefetch page count:
32

Container extent page count:
32

Table overflow record count:
0

Table Active Blocks:
-1

Average Row Compression Ratio:
1.91967

Percentage Rows Compressed:
96.848

Average Compressed Row Size:
40


Schema: PMART   

Name:
TB_FACT_NEW_GRID_INFO

Type:
Table

Time of creation:
2014-12-08-11.08.49.035414

Last statistics update:
2014-12-08-14.54.29.255276

Number of columns:
5

Number of rows:
259970

Width of rows:
50

Number of buffer pool pages:
278

Number of data partitions:
1

Distinct row values:
No

Tablespace name:
PMARTSPACE

Tablespace overhead:
6.725000

Tablespace transfer rate:
0.320000

Source for statistics:
Single Node

Prefetch page count:
32

Container extent page count:
32

Table overflow record count:
0

Table Active Blocks:
-1

Average Row Compression Ratio:
1.92049

Percentage Rows Compressed:
83.4596

Average Compressed Row Size:
26