Sybase ® IQ Query Plan
Query:
Version: 16.0.0.481/130503/P/sp01/Enterprise Linux64 - x86_64 - 2.6.18-194.el5/64bit

Query Tree
| 31 rows
#08 Root
| 31 rows
#70 Order By
| | 31 rows
#04 Filter
|| || 19,353 rows
#03 Join (Sort-Merge)
||| ||| 7,532,313 rows
#87 Order By
|||| |||| 15,000,000 rows
#01 Leaf <CDRs_15000000 AS a>
  
||| ||| 7,522,580 rows
#104 Order By
|||| |||| 15,000,000 rows
#02 Leaf <CDRs_15000000 AS b>

Query Timings

Timing Legend
Condition Execution Prepare 1st Fetch Subsequent Fetches Complete

Elapsed Time (sec)
0.665 1.33 1.995 2.66 3.325 3.99 4.655 5.32 5.985 6.65
#08 Root
. . . . . . . . . . .
#70 Order By .
. . . . . . . . . . .
#04 Filter ..
. . . . . . . . . . .
#03 Join (Sort-Merge) ...
. . . . . . . . . . .
#87 Order By ....
. . . . . . . . . . .
#01 Leaf .....
. . . . . . . . . . .
Normal100
Affinity90
Stolen80
IO %70
60
50
40
30
20
10
. . .
.
.
.
.
.
.
.
.
.
Time (sec)
1.0542 2.1085 3.1627 4.2170 5.2712
#104 Order By ....
. . . . . . . . . . .
#02 Leaf .....
. . . . . . . . . . .
Normal100
Affinity90
Stolen80
IO %70
60
50
40
30
20
10
. . .
.
.
.
.
.
.
.
.
.
Time (sec)
1.0618 2.1237 3.1855 4.2473 5.3092
Threads21
20
19
18
17
16
15
14
13
12
11
10
9
8
7
6
5
4
3
2
1
. . . . . . . . . . . . 104 . . . . . . . .
.
. 87
.
. 70 70
. 104 104 104 104 70
.
.
.
.
.
. 104 104
. 87 87 87 87
. 104
.
. 87
. 87 87
. 70
.
. 03 03 03 03 03 03 03 03
. . . . . . . . . . . . . . . . . . . .
CPU %100
95
90
85
80
75
70
65
60
55
50
45
40
35
30
25
20
15
10
5
. . . . . . . . . . . . . . . . . . . . .
.
.
.
.
.
.
.
.
. . .
. . . . . . . . .
.
.
.
. .
.
.
.
.
.
Wall Time
19:58:56.214 19:58:57.544 19:58:58.874 19:59:00.204 19:59:01.534

Query Text
 

Query Detail
#08 Root
Child Node 1#70
Generated Result Rows31
Estimated Result Rows14,250,000
DQP Ineligible ReasonNot a multiplex
User NameDBA (SA connHandle: 8 SA connID: 23)
Est. Temp Space Used (Mb)3704.3
Act. Temp Space Used (Mb)2294.5
Cursor NameSQL_CUR_c127100
Requested attributesNo Scroll Read Only Hold Chained
Effective Number of Users1
Number of CPUs16
IQ Main Cache Size (Mb)28000
IQ Temp Cache Size (Mb)68000
IQ Large Memory Size (Mb)28000
Threads used for executing local invariant predicates1
Number of CPUs (actual)32
Option Query_Plan_As_HTMLON
Option Max_Hash_Rows250000000
Time of Cursor Creation 2013-09-22 19:58:56.214526
Time of First Prepare 2013-09-22 19:58:56.858659
Time of First Prepare Return2013-09-22 19:58:56.861315
Time of First Fetch Call 2013-09-22 19:58:56.862542
Time of First Fetch Return 2013-09-22 19:59:02.735289
Time of Second Fetch Call 2013-09-22 19:59:02.735366
Time of Last Fetch Return 2013-09-22 19:59:02.735436
Time of Complete Call 2013-09-22 19:59:02.854044
Time of Complete Return 2013-09-22 19:59:02.854048
Elapsed Condition time 0:00:00.000000
Working time 0:00:06.520914
Elapsed time 0:00:06.639522
Production 1(SACast ((b.STARTTIME AS STARTTIME_2))
Production 2(SACast ((a.STARTTIME AS STARTTIME_1))
Production 3(SACast ((a.ENDTIME AS ENDTIME_1))
Production 4(SACast ((b.ENDTIME AS ENDTIME_2))
Output Vector11 entries (101 data bytes)
Output 1a.CDR_ID AS CDR_ID_1
Output 1 Data Typeunsigned bigint (20, 0)
Output 1 Base Distincts15,000,000
Output 1 NoteEffective Primary Key
Output 1 IndexesFP, HG(U)
Output 2b.CDR_ID AS CDR_ID_2
Output 2 Data Typeunsigned bigint (20, 0)
Output 2 Base Distincts15,000,000
Output 2 NoteEffective Primary Key
Output 2 IndexesFP, HG(U)
Output 3a.NUM_ORIG AS NUM_A
Output 3 Data Typevarchar (15, 0)
Output 3 IndexesFP, HG
Output 4a.NUM_DEST AS NUM_B
Output 4 Data Typevarchar (15, 0)
Output 4 Base Distincts14,996,119
Output 4 NoteHG index used by optimizer
Output 4 IndexesFP, HG
Output 5(SACast ((a.STARTTIME AS STARTTIME_1))
Output 5 Data Typedatetime (23, 0)
Output 6(SACast ((a.ENDTIME AS ENDTIME_1))
Output 6 Data Typedatetime (23, 0)
Output 7FPVALUE(FPORDINAL(a.DURATION AS DURATION_1, 2))
Output 7 Data Typeunsigned integer (10, 0)
Output 8b.NUM_DEST AS NUM_C
Output 8 Data Typevarchar (15, 0)
Output 8 IndexesFP, HG
Output 9(SACast ((b.STARTTIME AS STARTTIME_2))
Output 9 Data Typedatetime (23, 0)
Output 10(SACast ((b.ENDTIME AS ENDTIME_2))
Output 10 Data Typedatetime (23, 0)
Output 11FPVALUE(FPORDINAL(b.DURATION AS DURATION_2, 2))
Output 11 Data Typeunsigned integer (10, 0)
#70 Order By
Parent Node#08
Child Node 1#04
Generated Result Rows31
Estimated Result Rows14,250,000
Optimization NoteParallel sort load
SMP ineligible reasonOptimizer cost too low: 4.00
Parallel Work Time 0.42907801
Finish Inserting Time 0.00004400
Prepare To Retrieve Time 0.00005400
Max. Possible Parallel Arms16
Sort Statistics14 pages over 14 runs (1.00 pages per run)14-way merge
Max. Active Parallel Threads16
Parallel Sink Work Units77
Time of First Prepare 2013-09-22 19:58:56.858664
Time of First Prepare Return2013-09-22 19:58:56.861313
Time of First Fetch Call 2013-09-22 19:58:56.862546
Time of First Fetch Return 2013-09-22 19:59:02.735279
Time of Second Fetch Call 2013-09-22 19:59:02.735367
Time of Last Fetch Return 2013-09-22 19:59:02.735435
Time of Complete Call 2013-09-22 19:59:02.853969
Time of Complete Return 2013-09-22 19:59:02.854041
Elapsed time 0:00:06.639515
Ordering Expression 1a.STARTTIME AS STARTTIME_1
Production 1FPVALUE(FPORDINAL(b.DURATION AS DURATION_2, 2))
Production 2FPVALUE(FPORDINAL(a.DURATION AS DURATION_1, 2))
Output Vector11 entries (101 data bytes)
Output 1a.CDR_ID AS CDR_ID_1
Output 2b.CDR_ID AS CDR_ID_2
Output 3a.NUM_ORIG AS NUM_A
Output 4a.NUM_DEST AS NUM_B
Output 5a.STARTTIME AS STARTTIME_1
Output 6a.ENDTIME AS ENDTIME_1
Output 7FPVALUE(FPORDINAL(a.DURATION AS DURATION_1, 2))
Output 7 Data Typeunsigned integer (10, 0)
Output 8b.NUM_DEST AS NUM_C
Output 9b.STARTTIME AS STARTTIME_2
Output 10b.ENDTIME AS ENDTIME_2
Output 11FPVALUE(FPORDINAL(b.DURATION AS DURATION_2, 2))
Output 11 Data Typeunsigned integer (10, 0)
#04 Filter
Parent Node#70
Child Node 1#03
Generated Result Rows31
Estimated Result Rows14,250,000
Time of First Prepare 2013-09-22 19:58:56.858667
Time of First Prepare Return2013-09-22 19:58:56.861021
Time of First Fetch Call 2013-09-22 19:58:56.862548
Time of First Fetch Return 2013-09-22 19:59:02.305680
Time of Second Fetch Call 2013-09-22 19:59:02.465585
Time of Last Fetch Return 2013-09-22 19:59:02.734771
Time of Complete Call 2013-09-22 19:59:02.852556
Time of Complete Return 2013-09-22 19:59:02.852558
Elapsed time 0:00:06.638032
Condition 1 (Invariant)DATEDIFF(second,a.ENDTIME`(1) AS ENDTIME_1,b.STARTTIME`(1) AS STARTTIME_2) BETWEEN [5 AND 60]
Condition 1 Selectivity0.40000000
Output Vector11 entries (97 data bytes)
Output 1a.CDR_ID`(1) AS CDR_ID_1
Output 2b.CDR_ID`(1) AS CDR_ID_2
Output 3a.NUM_ORIG`(1) AS NUM_A
Output 4a.NUM_DEST`(1) AS NUM_B
Output 5a.STARTTIME`(1) AS STARTTIME_1
Output 6a.ENDTIME`(1) AS ENDTIME_1
Output 7b.NUM_DEST`(1) AS NUM_C
Output 8b.STARTTIME`(1) AS STARTTIME_2
Output 9b.ENDTIME`(1) AS ENDTIME_2
Output 10FPORDINAL(b.DURATION`(1) AS DURATION_2, 2)
Output 11FPORDINAL(a.DURATION`(1) AS DURATION_1, 2)
#03 Join (Sort-Merge)
Parent Node#04
Left Child Node#87
Right Child Node#104
Generated Result Rows19,353
Estimated Result Rows14,250,000
Valid Join AlgorithmsNLJ, SMJ, ASMJ, HJ
Optimizer est. max hash rows for these keys148233540
Optimization NoteOptimized for primary key join
Optimization NoteReplaced equated b.NUM_ORIG with a.NUM_DEST AS NUM_B
Left Input Table 1CDRs_15000000 AS a
Estimated Left Inputs15,000,000
Right Input Table 1CDRs_15000000 AS b
Estimated Right Inputs15,000,000
Join Result ConstraintMany to 1
Time of First Prepare 2013-09-22 19:58:56.858668
Time of First Prepare Return2013-09-22 19:58:56.861019
Time of First Fetch Call 2013-09-22 19:58:56.862549
Time of First Fetch Return 2013-09-22 19:59:02.305678
Time of Second Fetch Call 2013-09-22 19:59:02.306314
Time of Last Fetch Return 2013-09-22 19:59:02.734770
Time of Complete Call 2013-09-22 19:59:02.852541
Time of Complete Return 2013-09-22 19:59:02.852552
Elapsed time 0:00:06.638026
Actual Join Inputs Ratio1.00129384
Condition 1 (Invariant)(a.NUM_DEST`(1) AS NUM_B = b.NUM_ORIG`(1))
Production 1DATEDIFF(second,a.ENDTIME`(1) AS ENDTIME_1,b.STARTTIME`(1) AS STARTTIME_2)
Output Vector12 entries (105 data bytes)
Output 1a.CDR_ID`(1) AS CDR_ID_1
Output 2b.CDR_ID`(1) AS CDR_ID_2
Output 3a.NUM_ORIG`(1) AS NUM_A
Output 4a.NUM_DEST`(1) AS NUM_B
Output 5a.STARTTIME`(1) AS STARTTIME_1
Output 6a.ENDTIME`(1) AS ENDTIME_1
Output 7b.NUM_DEST`(1) AS NUM_C
Output 8b.STARTTIME`(1) AS STARTTIME_2
Output 9b.ENDTIME`(1) AS ENDTIME_2
Output 10FPORDINAL(b.DURATION`(1) AS DURATION_2, 2)
Output 11FPORDINAL(a.DURATION`(1) AS DURATION_1, 2)
Output 12DATEDIFF(second,a.ENDTIME`(1) AS ENDTIME_1,b.STARTTIME`(1) AS STARTTIME_2)
Output 12 Data Typebigint (19, 0)
#87 Order By
Parent Node#03
Child Node 1#01
Generated Result Rows7,532,313
Estimated Result Rows15,000,000
Optimization NoteParallel sort load
Optimization NoteParallel sort retrieval
Parallel Work Time 5.44162989
Finish Inserting Time 0.00004600
Prepare To Retrieve Time 0.00052500
Max. Possible Parallel Arms16
Max. Possible Parallel Arms16
Parallel Source Work Units77
Initial Source Work Unit size196,029
Optimization NotePrimary Parallel Join Source
Sort Statistics4582 pages over 6 runs (763.67 pages per run)6-way merge
Max. Active Parallel Threads6
Parallel Sink Work Units6
Time of First Prepare 2013-09-22 19:58:56.858671
Time of First Prepare Return2013-09-22 19:58:56.860928
Time of First Fetch Call 2013-09-22 19:58:56.862806
Time of First Fetch Return 2013-09-22 19:59:02.305635
Time of Second Fetch Call 2013-09-22 19:59:02.306012
Time of Last Fetch Return 2013-09-22 19:59:02.733926
Time of Complete Call 2013-09-22 19:59:02.852537
Time of Complete Return 2013-09-22 19:59:02.852539
Elapsed time 0:00:06.638013
Ordering Expression 1a.NUM_DEST`(1) AS NUM_B
Output Vector6 entries (56 data bytes)
Output 1a.CDR_ID`(1) AS CDR_ID_1
Output 2a.NUM_ORIG`(1) AS NUM_A
Output 3a.NUM_DEST`(1) AS NUM_B
Output 4a.STARTTIME`(1) AS STARTTIME_1
Output 5a.ENDTIME`(1) AS ENDTIME_1
Output 6FPORDINAL(a.DURATION`(1) AS DURATION_1, 2)
#01 Leaf
Table NameCDRs_15000000 AS a
Parent Node#87
Table Row Count15,000,000
Generated Result Rows15,000,000
Estimated Result Rows15,000,000
Parallel Source Work Units6
Initial Source Work Unit size5,329,679
Optimization NotePrimary Parallel Join Source
Projection Buffer Cache Stats - nesspro_bigdataHitRate: 100% Finds: 6394 Hits: 6394 Waits: 0 Pageouts: 0 IOms: 0
Time of First Prepare 2013-09-22 19:58:56.858672
Time of First Prepare Return2013-09-22 19:58:56.858698
Time of First Fetch Call 2013-09-22 19:58:56.862810
Time of First Fetch Return 2013-09-22 19:58:56.862942
Time of Second Fetch Call 2013-09-22 19:58:56.864420
Time of Last Fetch Return 2013-09-22 19:58:58.506051
Time of Complete Call 2013-09-22 19:59:02.853849
Time of Complete Return 2013-09-22 19:59:02.853966
Elapsed time 0:00:06.639440
Declared Primary Key Column 1a.CDR_ID AS CDR_ID_1
Projection Per Row6 columns (56 bytes)
Output Vector6 entries (56 data bytes)
Output 1a.CDR_ID`(17) AS CDR_ID_1
Output 1 Data Typeunsigned bigint (20, 0)
Output 1 Base Distincts15,000,000
Output 1 NoteCandidate Primary Key
Output 1 IndexesFP, HG(U)
Output 2a.NUM_ORIG`(17) AS NUM_A
Output 2 Data Typevarchar (15, 0)
Output 2 IndexesFP, HG
Output 3a.NUM_DEST`(17) AS NUM_B
Output 3 Data Typevarchar (15, 0)
Output 3 Base Distincts14,996,119
Output 3 NoteCandidate Primary Key
Output 3 NoteHG index used by optimizer
Output 3 IndexesFP, HG
Output 4a.STARTTIME`(17) AS STARTTIME_1
Output 4 Data Typedatetime (23, 0)
Output 4 IndexesFP, DTTM
Output 5a.ENDTIME`(17) AS ENDTIME_1
Output 5 Data Typedatetime (23, 0)
Output 5 IndexesFP, DTTM
Output 6FPORDINAL(a.DURATION`(17) AS DURATION_1, 2)
Output 6 Data Typeordinal (2, 0)
Output 6 IndexesFP(12-bit), HNG, HG
Maximum Row ID339,308,900
Start of Partition BM1
End of Partition BM5,329,680
#104 Order By
Parent Node#03
Child Node 1#02
Generated Result Rows7,522,580
Estimated Result Rows15,000,000
Optimization NoteParallel sort load
Optimization NoteParallel sort retrieval
Parallel Work Time 5.36068916
Finish Inserting Time 0.00008800
Prepare To Retrieve Time 0.00054500
Max. Possible Parallel Arms16
Max. Possible Parallel Arms16
Parallel Source Work Units77
Initial Source Work Unit size196,029
Sort Statistics4582 pages over 6 runs (763.67 pages per run)6-way merge
Max. Active Parallel Threads6
Parallel Sink Work Units6
Time of First Prepare 2013-09-22 19:58:56.859007
Time of First Prepare Return2013-09-22 19:58:56.860931
Time of First Fetch Call 2013-09-22 19:58:56.862580
Time of First Fetch Return 2013-09-22 19:59:02.224520
Time of Second Fetch Call 2013-09-22 19:59:02.306023
Time of Last Fetch Return 2013-09-22 19:59:02.734769
Time of Complete Call 2013-09-22 19:59:02.852531
Time of Complete Return 2013-09-22 19:59:02.852532
Elapsed time 0:00:06.638006
Ordering Expression 1b.NUM_ORIG`(1)
Output Vector6 entries (56 data bytes)
Output 1b.CDR_ID`(1) AS CDR_ID_2
Output 2b.NUM_DEST`(1) AS NUM_C
Output 3b.STARTTIME`(1) AS STARTTIME_2
Output 4b.ENDTIME`(1) AS ENDTIME_2
Output 5FPORDINAL(b.DURATION`(1) AS DURATION_2, 2)
Output 6b.NUM_ORIG`(1)
#02 Leaf
Table NameCDRs_15000000 AS b
Parent Node#104
Table Row Count15,000,000
Generated Result Rows15,000,000
Estimated Result Rows15,000,000
Parallel Source Work Units6
Initial Source Work Unit size5,329,679
Optimization NotePrimary Parallel Join Source
Projection Buffer Cache Stats - nesspro_bigdataHitRate: 100% Finds: 6394 Hits: 6394 Waits: 0 Pageouts: 0 IOms: 0
Time of First Prepare 2013-09-22 19:58:56.859008
Time of First Prepare Return2013-09-22 19:58:56.859027
Time of First Fetch Call 2013-09-22 19:58:56.862582
Time of First Fetch Return 2013-09-22 19:58:56.862701
Time of Second Fetch Call 2013-09-22 19:58:56.864096
Time of Last Fetch Return 2013-09-22 19:58:58.506049
Time of Complete Call 2013-09-22 19:59:02.853180
Time of Complete Return 2013-09-22 19:59:02.853301
Elapsed time 0:00:06.638775
Declared Primary Key Column 1b.CDR_ID AS CDR_ID_2
Projection Per Row6 columns (56 bytes)
Output Vector6 entries (56 data bytes)
Output 1b.CDR_ID`(33) AS CDR_ID_2
Output 1 Data Typeunsigned bigint (20, 0)
Output 1 Base Distincts15,000,000
Output 1 NoteEffective Primary Key
Output 1 IndexesFP, HG(U)
Output 2b.NUM_DEST`(33) AS NUM_C
Output 2 Data Typevarchar (15, 0)
Output 2 IndexesFP, HG
Output 3b.STARTTIME`(33) AS STARTTIME_2
Output 3 Data Typedatetime (23, 0)
Output 3 IndexesFP, DTTM
Output 4b.ENDTIME`(33) AS ENDTIME_2
Output 4 Data Typedatetime (23, 0)
Output 4 IndexesFP, DTTM
Output 5FPORDINAL(b.DURATION`(33) AS DURATION_2, 2)
Output 5 Data Typeordinal (2, 0)
Output 5 IndexesFP(12-bit), HNG, HG
Output 6b.NUM_ORIG`(33)
Output 6 Data Typevarchar (15, 0)
Output 6 Base Distincts15,000,000
Output 6 NoteEffective Primary Key
Output 6 NoteHG index used by optimizer
Output 6 IndexesFP, HG
Maximum Row ID339,308,900
Start of Partition BM1
End of Partition BM5,329,680