473,833 Members | 2,087 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

SQL explain plan

JAW
This plan seems like it should perform well. Does anyone see anything.
SQL Statement Text:

DECLARE MTR - RDG - EST - CSR CURSOR

FOR

SELECT A.KY_MTR_BILL_G RP , A.KY_MRDG_ASGNM T , A.KY_MRDG_SEQ_A CTL ,

A.CD_MPT_TYPE , A.KY_PREM_NO , A.KY_MRDG_SEQ_N O , A.KY_BA ,

A.DT_MTR_GRP_EF F , A.DT_NXT_SCH_RD G , A.CD_SIC , A.FL_AC ,
A.KY_SPT ,
A.KY_MPT_NO , A.KY_RDG , A.CD_MPT_STAT , A.QY_MTR_MULT ,
A.CD_MTR_MFGR ,
A.KY_MTR_NO , A.QY_NO_OF_DIAL , A.CD_TAR_SCH , A.CD_TAR_TYPE ,

A.QY_CONS_UNIT , A.CD_STAT

FROM MRDG_STAT A , BILL_ACCT B , OFFICE C , CO_RULE D

WHERE A.KY_BA = B.KY_BA

AND B.CD_OFFICE = C.CD_OFFICE

AND C.CD_CO = D.CD_CO

AND D.KY_RULE_NO = :CU05TB08.KY-RULE-NO

AND

(

(

A.KY_MTR_BILL_G RP = :MISC-APPL-VARIABLES.WS-KY-BILL-GRP-3

AND D.QY_RULE_DATA = 3

)

OR

(

A.KY_MTR_BILL_G RP = :MISC-APPL-VARIABLES.WS-KY-BILL-GRP-1

AND D.QY_RULE_DATA = 1

)

)

AND A.FL_READ_REC = :CU04TB28.FL-READ-REC

AND A.KY_BA >= :WS-COUNTERS.WS-MRDG-STAT-KY-BA-LOW

AND A.KY_BA <= :WS-COUNTERS.WS-MRDG-STAT-KY-BA-HIGH

ORDER BY A.KY_BA , A.KY_MRDG_SEQ_A CTL , A.CD_MPT_TYPE

Access Path Analysis:

PQbkNo: 0 QblkNo: 1 PlanNo: 1 MxOpSq: 0 TBTyp:
TABLE
Access: IXDATA TSLock: IS Prefet: S QblkTy: SEL TabNo: 2

TBCrtr: CISC50 TBName: BILL_ACCT CorrNm: B

IXCrtr: CISC50 IXName: X01002U1 MCols : 1

PllMod: QUERY CP AcDegr: 0 AcPgrp: 1 SrtnPg:

PQbkNo: 0 QblkNo: 1 PlanNo: 2 MxOpSq: 0 TBTyp:
TABLE
Access: IXDATA TSLock: IS Prefet: QblkTy: SEL TabNo: 3

Method: Nested Loop Join JoinTy: Inner MergJC:

TBCrtr: CISC50 TBName: OFFICE CorrNm: C

IXCrtr: CISC50 IXName: X01148U1 MCols : 1

PllMod: QUERY CP AcDegr: 0 AcPgrp: 1 SrtnPg:

JnDegr: 0 JnPgrp: 1 SrtcPg:

PPA REL. 2.3 --------- Plan Analyzer Enhanced Explain -------------
Page
1 2006/05/08 20:54
PAGE 5
------------------------------------------------------------- USER
ID: OPRJAW0
DATE 05-08-06 SQL Statement Detail Reports
TIME 20:4


PQbkNo: 0 QblkNo: 1 PlanNo: 3 MxOpSq: 0 TBTyp:
TABLE
Access: IXDATA TSLock: IS Prefet: QblkTy: SEL TabNo: 4

Method: Nested Loop Join JoinTy: Inner MergJC:

TBCrtr: CISC50 TBName: CO_RULE CorrNm: D

IXCrtr: CISC50 IXName: X01307U1 MCols : 2

PllMod: QUERY CP AcDegr: 0 AcPgrp: 1 SrtnPg:

JnDegr: 0 JnPgrp: 1 SrtcPg:

PQbkNo: 0 QblkNo: 1 PlanNo: 4 MxOpSq: 0 TBTyp:
TABLE
Access: IXDATA TSLock: IS Prefet: S QblkTy: SEL TabNo: 1

Method: Nested Loop Join JoinTy: Inner MergJC:

TBCrtr: CISC50 TBName: MRDG_STAT CorrNm: A

IXCrtr: CISC50 IXName: X01040U2 MCols : 0

PllMod: QUERY CP AcDegr: 0 AcPgrp: 1 SrtnPg:

JnDegr: 0 JnPgrp: 1 SrtcPg:

PQbkNo: 0 QblkNo: 1 PlanNo: 5 MxOpSq: 0 TBTyp:

Method: Result Table Sorted

Sorts Composite Unique: N Join : N Ordrby: Y Grpby: N

SQL Dependency Analysis:

Tablespace Database L Buffpool Pgsze PS/SS
NTbl
Table/Alias/View/Synm Creator Encoding

Index (# Cols) Type Creator U Buffpool Pgsze

----------------------- -------- -------- - ---------- ----- -----
-----
S01G040 Segment DCIS02C1 A BP2 4 64 1

MRDG_STAT Table CISC50 EBCDIC

X01040D1 Type-2 CISC50 D BP3 4096

1

X01040U1 C Type-2 CISC50 U BP3 4096

(UNIQUE,CLUSTER I 4

X01040U2 C Type-2 CISC50 U BP3 4096

(UNIQUE) 6

S01G148 Segment DCIS02C1 A BP2 4 4 1

OFFICE Table CISC50 EBCDIC

X01148U1 C Type-2 CISC50 U BP3 4096

(UNIQUE,CLUSTER I 1

S01G307 Segment DCIS02C1 A BP2 4 4 1

CO_RULE Table CISC50 EBCDIC

X01307U1 Type-2 CISC50 U BP3 4096

(UNIQUE,CLUSTER I 4

S01P002 Parttn DCIS02C1 A BP2 4 4 1

BILL_ACCT Table CISC50 EBCDIC

X01002D2 Type-2 CISC50 D BP3 4096

4

X01002D3 Type-2 CISC50 D BP3 4096

1

1 2006/05/08 20:54
PAGE 6
------------------------------------------------------------- USER
ID: OPRJAW0
X01002D4 Type-2 CISC50 D BP3 4096

1

X01002U1 C Type-2 CISC50 U BP2 4096

(UNIQUE,CLUSTER I 1

X01002U2 C Type-2 CISC50 U BP3 4096

PPA REL. 2.3 --------- Plan Analyzer Enhanced Explain -------------
Page
DATE 05-08-06 SQL Statement Detail Reports
TIME 20:4
(UNIQUE) 2

X01002U3 Type-2 CISC50 U BP3 4096

(UNIQUE) 5

X01002U5 C Type-2 CISC50 U BP3 4096

(UNIQUE) 2

SQL Predicate Analysis:


Filter
Predicate Index
Sarge Factor
-------------------------------------------------------- -----
----- -------
STATEMENT #001

---> A . KY_BA = B . KY_BA YES YES
0.00001
---> B . CD_OFFICE = C . CD_OFFICE YES YES
0.01724
---> C . CD_CO = D . CD_CO YES YES
0.50000
---> D . KY_RULE_NO = :CU05TB08.KY-RULE-NO YES YES
0.40000
---> A . KY_MTR_BILL_GRP = YES YES
0.16666
:MISC-APPL-VARIABLES.WS-KY-BILL-GRP-3

---> D . QY_RULE_DATA = 3 YES YES
0.40000
---> A . KY_MTR_BILL_GRP = YES YES
0.16666
:MISC-APPL-VARIABLES.WS-KY-BILL-GRP-1

---> D . QY_RULE_DATA = 1 YES YES
0.40000
---> A . FL_READ_REC = :CU04TB28.FL-READ-REC YES YES
0.40000
---> A . KY_BA >= :WS-COUNTERS.WS-MRDG-STAT-KY-BA-LOW YES YES
0.33333
---> A . KY_BA <= :WS-COUNTERS.WS-MRDG-STAT-KY-BA-HIGH YES YES
0.33333


PPA REL. 2.3 --------- Plan Analyzer Enhanced Explain -------------
Page 1
DATE 05-08-06 Performance Report
TIME 20:4
Performance data not Available.

Performance Group id 0

1 2006/05/08 20:54
PAGE 7
------------------------------------------------------------- USER
ID: OPRJAW0
Statement id 0

PPA REL. 2.3 --------- Plan Analyzer Enhanced Explain -------------
Page 1
DATE 05-08-06 SQL Statement Detail Reports
TIME 20:4
Source DB2 SSID: DB2D Date/time: 05-08-06,
13:16:09:7
Collection: CISC5 DB2 SSID : DB2D

Package : CUBES001 SQL Code : +0

Stmt : 5,865 (5) Message :

Version :

Hint Used : N/A

May 9 '06 #1
0 1910

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

10
2174
by: Greg Stark | last post by:
This query is odd, it seems to be taking over a second according to my log_duration logs and according to psql's \timing numbers. However explain analyze says it's running in about a third of a second. What would cause this? Is it some kind of postgresql.conf configuration failure? I have the same query running fine on a different machine. QUERY PLAN...
14
20404
by: Ina Schmitz | last post by:
Hello all, I don't succeed in displaying the explain plan. I use IBM DB2 Universal Database 8.2. I tried to do the example given in the online help for "Visual Explain". The tables EXPLAIN_STATEMENT and EXPLAIN_INSTANCE exist. With VESAMPL.DDL, I loaded the predefined execution plans. In the next step, I'ld like to display the loaded access plans. So, I right clicked on "Show Explained Statements History" and got the result:
5
3641
by: Jon Lapham | last post by:
I have been using the EXPLAIN ANALYZE command to debug some performance bottlenecks in my database. In doing so, I have found an oddity (to me anyway). The "19ms" total runtime reported below actually takes 25 seconds on my computer (no other CPU intensive processes running). Is this normal for EXPLAIN ANALYZE to report a total runtime so vastly different from wall clock time? During the "explain ANALYZE delete from msgid;" the CPU is...
4
12733
by: marklawford | last post by:
Not having earned my DBA badge from the scouts just yet I'm a little lost with an error I'm getting. We've just upgraded our development database from 7.2 to 8.2 as the first step in upgrading our wider environment. Of course, development doesn't stop so I'm running some explain plans over some new views. The problem is, when the view is accessed as part of the explain plan script, the following error is returned.
0
1859
by: jfnorris | last post by:
I'm having some difficulty with Visual Explain. When I explain reletively simple sql, VE will generate an access plan. If I try to explain sql with several joins both outer and inner, or anthing more complicated, the tool will act like it's processing the sql but does not generate the access plan. No errors or anything. Any Ideas?? Thanks in adavnce.
5
8119
by: kabotnet | last post by:
Hi, I'm new in db2, I'm trying to execute EXPLAIN command on some queries but i have error like: And message similar to: Token EXPLAIN is not valid, valid tokens ( END GET SET CALL DROP FREE HOLD LOCK OPEN WITH ALTER. I've created tables explain_* How can I start to find solution? Is it possible that my db2 doesn't support explain?
7
5790
by: skaushik | last post by:
Hi all, I was working on a SQL query where a history table is joined with a small table to get some information. There is an index on the history table column but the explain plan tells that there is a full table scan on both the tables. The query and the explain plan is below select * from history_table ht, small_table st where ht.columnA > st.columnB and sysdate between st.datetime1 and st.datetime2; Operation Object...
3
18625
by: raviva | last post by:
Hi, I want the explain plan. But when I clicked on the explain plan it says ORA-02404: specified plan table not found. I investigated on net and some books. I was asked to load the utlxplan.sql. I did that but of no use. I also changed the name of the plan table in TOAD, still no use. New thing I heard recently is to run TOADPREP.SQL. But this script is not available on our server($ORACLE_HOME/rdbms/admin). Can someone please suggest me what...
0
927
by: db2admin | last post by:
hello, I have compressed table 442992 rows and when i run explain plan on this table for some SQL using this table, i see table scan and cardinality on top of table node as 18458. I am new to compression and do not understand why cardinality is not the actual number of rows but 18458. here is the piece of explain plan. table ABC.XYZ is the table i am talking about ( right most in plan )
0
9796
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
10782
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10500
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10543
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10213
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
7753
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6951
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
1
4422
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
3972
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.