Explain Plan

DB2 Universal Database Version 9.7, 5622-044 (c) Copyright IBM Corp. 1991, 2008
Licensed Material - Program Property of IBM
IBM DATABASE 2 Explain Table Format Tool



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

DB2_VERSION:       09.07.0
SOURCE_NAME:       SQLC2H20
SOURCE_SCHEMA:     NULLID
SOURCE_VERSION:
EXPLAIN_TIME:      2009-10-19-16.10.17.570001
EXPLAIN_REQUESTER: CLARALIU

Database Context:
----------------
	Parallelism:          None
	CPU Speed:            3.818116e-007
	Comm Speed:           100
	Buffer Pool size:     6250
	Sort Heap size:       256
	Database Heap size:   600
	Lock List size:       4096
	Maximum Lock List:    22
	Average Applications: 1
	Locks Available:      28835

Package Context:
---------------
	SQL Type:           Dynamic
	Optimization Level: 5
	Blocking:           Block All Cursors
	Isolation Level:    Cursor Stability



---------------- STATEMENT 1  SECTION 201 ----------------
	QUERYNO:       15
	QUERYTAG:      CLP
	Statement Type:   Select
	Updatable:        No
	Deletable:        No
	Query Degree:     1

Original Statement:
------------------
SELECT patient_id,date_of_consultancy,
XMLQUERY('$i/ClinicalDocument/component/structuredBody/component/section/text'
        PASSING GRP1255977523782_12.out_patient_data.PMD as "i") as
        Past_Medical_Details
FROM GRP1255977523782_12.out_patient_data
WHERE
xmlexists('$i/ClinicalDocument/recordTarget/patientRole/patient/name[given =
        "James"]' passing PMD as "i")


Optimized Statement:
-------------------
SELECT Q2.PATIENT_ID AS "PATIENT_ID", Q2.DATE_OF_CONSULTANCY AS
        "DATE_OF_CONSULTANCY", Q1.$C1 AS "PAST_MEDICAL_DETAILS"
FROM $INTERNAL_FOR$ ((TABLE ($INTERNAL_XPATH$
        ('($INTERNAL_XMLTOXML_NIEO$(Q2.PMD))/{(.[ $INTERNAL_EBV_BOOLEAN$(Clinic
alDocument/recordTarget/patientRole/patient/name[(given =
        "James")])])(:-->$C0:),
ClinicalDocument/component/structuredBody/component/section/(text)(:-->$C1:)}'
        ))) AS Q1), GRP1255977523782_12.OUT_PATIENT_DATA AS Q2

Access Plan:
-----------
	Total Cost: 		37.86
	Query Degree:		1

                           Rows
                          RETURN
                          (   1)
                           Cost
                            I/O
                            |
                             1
                          NLJOIN
                          (   2)
                           37.86
                             5
                          /-+--\
                         1        1
                      FETCH    XSCAN
                      (   3)   (   7)
                      7.59764  30.2623
                         1        4
                /-------+--------\
               1                   102
            RIDSCN    DP-TABLE: GRP1255977523782_12
            (   4)          OUT_PATIENT_DATA
           0.0312501               Q2
               0
              |
               1
            SORT
            (   5)
           0.0307587
               0
              |
               1
            XISCAN
            (   6)
           0.0296034
               0
              |
              102
 XMLIN:    GRP1255977523782_12
           NAMEINDEX
              Q2



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

No extended Diagnostic Information for this statement.


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


	1) RETURN: (Return Result)
		Cumulative Total Cost: 		37.86
		Cumulative CPU Cost: 		157087
		Cumulative I/O Cost: 		5
		Cumulative Re-Total Cost: 	30.2916
		Cumulative Re-CPU Cost: 	135049
		Cumulative Re-I/O Cost: 	4
		Cumulative First Row Cost: 	37.8581
		Estimated Bufferpool Buffers: 	9

		Arguments:
		---------
		BLDLEVEL: (Build level)
			DB2 v9.7.0.441 : s090521
		HEAPUSE : (Maximum Statement Heap Usage)
			160 Pages
		PREPTIME: (Statement prepare time)
			        58 milliseconds
		STMTHEAP: (Statement heap size)
			2048

		Input Streams:
		-------------
			9) From Operator #2

				Estimated number of rows: 	1
				Number of columns: 		3
				Subquery predicate ID: 		Not Applicable

				Column Names:
				------------
				+Q3.PAST_MEDICAL_DETAILS
				+Q3.DATE_OF_CONSULTANCY+Q3.PATIENT_ID

				Expected Sequence Sizes:
				------------------------
				1.000000,NA,NA


	2) NLJOIN: (Nested Loop Join)
		Cumulative Total Cost: 		37.86
		Cumulative CPU Cost: 		157087
		Cumulative I/O Cost: 		5
		Cumulative Re-Total Cost: 	30.2916
		Cumulative Re-CPU Cost: 	135049
		Cumulative Re-I/O Cost: 	4
		Cumulative First Row Cost: 	37.8581
		Estimated Bufferpool Buffers: 	9

		Arguments:
		---------
		EARLYOUT: (Early Out flag)
			NONE
		FETCHMAX: (Override for FETCH MAXPAGES)
			IGNORE
		ISCANMAX: (Override for ISCAN MAXPAGES)
			IGNORE

		Input Streams:
		-------------
			7) From Operator #3

				Estimated number of rows: 	1
				Number of columns: 		4
				Subquery predicate ID: 		Not Applicable

				Column Names:
				------------
				+Q2.$RID$(A)+Q2.DATE_OF_CONSULTANCY
				+Q2.PATIENT_ID+Q2.PMD

				Expected Sequence Sizes:
				------------------------
				NA,NA,NA,1.000000

			8) From Operator #7

				Estimated number of rows: 	1
				Number of columns: 		1
				Subquery predicate ID: 		Not Applicable

				Column Names:
				------------
				+Q1.$C1

				Expected Sequence Sizes:
				------------------------
				0.010539


		Output Streams:
		--------------
			9) To Operator #1

				Estimated number of rows: 	1
				Number of columns: 		3
				Subquery predicate ID: 		Not Applicable

				Column Names:
				------------
				+Q3.PAST_MEDICAL_DETAILS
				+Q3.DATE_OF_CONSULTANCY+Q3.PATIENT_ID

				Expected Sequence Sizes:
				------------------------
				1.000000,NA,NA


	3) FETCH : (Fetch)
		Cumulative Total Cost: 		7.59764
		Cumulative CPU Cost: 		98575
		Cumulative I/O Cost: 		1
		Cumulative Re-Total Cost: 	0.0317091
		Cumulative Re-CPU Cost: 	83049
		Cumulative Re-I/O Cost: 	0
		Cumulative First Row Cost: 	7.59575
		Estimated Bufferpool Buffers: 	5

		Arguments:
		---------
		CUR_COMM: (Currently Committed)
			TRUE
		GLOBLOCK: (Global Lock intent)
			INTENT SHARE
		JN INPUT: (Join input leg)
			OUTER
		LCKAVOID: (Lock Avoidance)
			TRUE
		MAXPAGES: (Maximum pages for prefetch)
			1
		PREFETCH: (Type of Prefetch)
			NONE
		ROWLOCK : (Row Lock intent)
			SHARE (CS/RS)
		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)
			FALSE
		VISIBLE : (May be included in scan sharing structures)
			FALSE
		WRAPPING: (Scan may start anywhere and wrap)
			FALSE

		Input Streams:
		-------------
			5) From Operator #4

				Estimated number of rows: 	1
				Number of columns: 		1
				Subquery predicate ID: 		Not Applicable

				Column Names:
				------------
				+Q2.$RID$(A)

			6) From Object GRP1255977523782_12.OUT_PATIENT_DATA

				Estimated number of rows: 	102
				Number of columns: 		3
				Subquery predicate ID: 		Not Applicable

				Column Names:
				------------
				+Q2.DATE_OF_CONSULTANCY+Q2.PATIENT_ID+Q2.PMD

				Expected Sequence Sizes:
				------------------------
				NA,NA,1.000000


		Output Streams:
		--------------
			7) To Operator #2

				Estimated number of rows: 	1
				Number of columns: 		4
				Subquery predicate ID: 		Not Applicable

				Column Names:
				------------
				+Q2.$RID$(A)+Q2.DATE_OF_CONSULTANCY
				+Q2.PATIENT_ID+Q2.PMD

				Expected Sequence Sizes:
				------------------------
				NA,NA,NA,1.000000


	4) RIDSCN: (Row Identifier Scan)
		Cumulative Total Cost: 		0.0312501
		Cumulative CPU Cost: 		81847
		Cumulative I/O Cost: 		0
		Cumulative Re-Total Cost: 	0.0302857
		Cumulative Re-CPU Cost: 	79321
		Cumulative Re-I/O Cost: 	0
		Cumulative First Row Cost: 	0.0307587
		Estimated Bufferpool Buffers: 	0

		Arguments:
		---------
		NUMROWS : (Estimated number of rows)
			1

		Input Streams:
		-------------
			4) From Operator #5

				Estimated number of rows: 	1
				Number of columns: 		1
				Subquery predicate ID: 		Not Applicable

				Column Names:
				------------
				+Q2.$RID$(A)


		Output Streams:
		--------------
			5) To Operator #3

				Estimated number of rows: 	1
				Number of columns: 		1
				Subquery predicate ID: 		Not Applicable

				Column Names:
				------------
				+Q2.$RID$(A)


	5) SORT  : (Sort)
		Cumulative Total Cost: 		0.0307587
		Cumulative CPU Cost: 		80560
		Cumulative I/O Cost: 		0
		Cumulative Re-Total Cost: 	0.0296034
		Cumulative Re-CPU Cost: 	77534
		Cumulative Re-I/O Cost: 	0
		Cumulative First Row Cost: 	0.0307587
		Estimated Bufferpool Buffers: 	0

		Arguments:
		---------
		DUPLWARN: (Duplicates Warning flag)
			TRUE
		NUMROWS : (Estimated number of rows)
			1
		ROWWIDTH: (Estimated width of rows)
			20
		SORTKEY : (Sort Key column)
			1: Q2.$RID$(A)
		TEMPSIZE: (Temporary Table Page Size)
			8192
		UNIQUE  : (Uniqueness required flag)
			TRUE

		Input Streams:
		-------------
			3) From Operator #6

				Estimated number of rows: 	1
				Number of columns: 		1
				Subquery predicate ID: 		Not Applicable

				Column Names:
				------------
				+Q2.$RID$


		Output Streams:
		--------------
			4) To Operator #4

				Estimated number of rows: 	1
				Number of columns: 		1
				Subquery predicate ID: 		Not Applicable

				Column Names:
				------------
				+Q2.$RID$(A)


	6) XISCAN: (Index Scan over XML)
		Cumulative Total Cost: 		0.0296034
		Cumulative CPU Cost: 		77534
		Cumulative I/O Cost: 		0
		Cumulative Re-Total Cost: 	0.0296034
		Cumulative Re-CPU Cost: 	77534
		Cumulative Re-I/O Cost: 	0
		Cumulative First Row Cost: 	7.57934
		Estimated Bufferpool Buffers: 	0

		Arguments:
		---------
		DPNUMPRT: (Number of data partitions accessed)
			4
		GLOBLOCK: (Global Lock intent)
			INTENT NONE
		ROWLOCK : (Row Lock intent)
			NONE
		SCANDIR : (Scan Direction)
			FORWARD
		TABLOCK : (Table Lock intent)
			INTENT NONE
		UR_EXTRA: (UR plus extra table lock and may upgrade to CS)
			TRUE
		XDFOUT  : (Expected Documents per Input Value)
			0.009804
		XLOGID  : (Logical Index over XML)
			GRP1255977523782_12.NAMEINDEX
		XPHYID  : (Physical Index over XML)
			SYSIBM  .SQL091019152046360

		Predicates:
		----------
		 *) Start Key Predicate,
			Comparison Operator: 		Equal (=)
			Subquery Input Required: 	No

			Predicate Text:
			--------------
			($INTERNAL_XMLTOXML_NIEO$(Q2.PMD))/./ClinicalDocument/r
ecordTarget/patientRole/patient/name/given = "James"


		Input Streams:
		-------------
			1) From Object GRP1255977523782_12.NAMEINDEX

				Estimated number of rows: 	102
				Number of columns: 		1
				Subquery predicate ID: 		Not Applicable

				Column Names:
				------------
				+Q2.$RID$

			2) From Object SYSIBM.SQL091019152046360

				Estimated number of rows: 	102
				Number of columns: 		1
				Subquery predicate ID: 		Not Applicable

				Column Names:
				------------
				+Q2.$RID$


		Output Streams:
		--------------
			3) To Operator #5

				Estimated number of rows: 	1
				Number of columns: 		1
				Subquery predicate ID: 		Not Applicable

				Column Names:
				------------
				+Q2.$RID$


	7) XSCAN : (XML Doc Navigation)
		Cumulative Total Cost: 		30.2623
		Cumulative CPU Cost: 		58511.8
		Cumulative I/O Cost: 		4
		Cumulative Re-Total Cost: 	30.2599
		Cumulative Re-CPU Cost: 	52000
		Cumulative Re-I/O Cost: 	4
		Cumulative First Row Cost: 	30.2623
		Estimated Bufferpool Buffers: 	408

		Arguments:
		---------
		INPUTXID: (Context Node)
			PMD
		JN INPUT: (Join input leg)
			INNER
		XPATH   : (Internal XPath Expression)
			($INTERNAL_XMLTOXML_NIEO$(Q2.PMD))/{(.[ $INTERNAL_EBV_B
OOLEAN$(ClinicalDocument/recordTarget/patientRole/patient/name[(given
			        = "James")])])(:-->$C0:),
ClinicalDocument/component/structuredBody/component/section/(text)(:-->$C1:)}

		Output Streams:
		--------------
			8) To Operator #2

				Estimated number of rows: 	1
				Number of columns: 		1
				Subquery predicate ID: 		Not Applicable

				Column Names:
				------------
				+Q1.$C1

				Expected Sequence Sizes:
				------------------------
				0.010539


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

	Schema: GRP1255977523782_12
	Name: 	OUT_PATIENT_DATA
	Type: 	Data Partitioned Table
			Time of creation: 		2009-10-19-14.41.46.758003
			Last statistics update: 	2009-10-19-16.09.17.211000
			Number of columns: 		4
			Number of rows: 		102
			Width of rows: 			284
			Number of buffer pool pages: 	7
			Number of data partitions: 	4
			Distinct row values: 		No
			Tablespace name: 		
			Tablespace overhead: 		7.500000
			Tablespace transfer rate: 	0.060000
			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: 	0
			Percentage Rows Compressed: 	0
			Average Compressed Row Size: 	0

	Schema: SYSIBM
	Name: 	SQL091019152046360
	Type: 	Physical Index over XML
			Time of creation: 		2009-10-19-15.20.46.320001
			Last statistics update: 	2009-10-19-16.09.17.211000
			Number of columns: 		1
			Number of rows: 		102
			Width of rows: 			-1
			Number of buffer pool pages: 	7
			Distinct row values: 		No
			Tablespace name:
			Tablespace overhead: 		7.500000
			Tablespace transfer rate: 	0.060000
			Source for statistics: 		Single Node
			Prefetch page count: 		32
			Container extent page count: 	32
			Index clustering statistic: 	100.000000
			Index leaf pages: 		1
			Index tree levels: 		1
			Index full key cardinality: 	1
			Index first key cardinality: 	1
			Index first 2 keys cardinality: 1
			Index first 3 keys cardinality: 1
			Index first 4 keys cardinality: 1
			Index sequential pages: 	0
			Index page density: 		0
			Index avg sequential pages: 	0
			Index avg gap between sequences:0
			Index avg random pages: 	1
			Fetch avg sequential pages: 	-1
			Fetch avg gap between sequences:-1
			Fetch avg random pages: 	-1
			Index RID count: 		1
			Index deleted RID count: 	0
			Index empty leaf pages: 	0
			Base Table Schema: 		GRP1255977523782_12
			Base Table Name: 		OUT_PATIENT_DATA
			Columns in index:
				PMD(A)

	Schema: GRP1255977523782_12
	Name: 	NAMEINDEX
	Type: 	Logical Index over XML
			Time of creation: 		2009-10-19-15.20.46.320001
			Last statistics update: 	See physical index(es):
							SYSIBM.SQL091019152046360
			Number of columns: 		1
			Number of rows: 		102
			Width of rows: 			-1
			Number of buffer pool pages: 	7
			Distinct row values: 		No
			Tablespace name:
			Tablespace overhead: 		7.500000
			Tablespace transfer rate: 	0.060000
			Source for statistics: 		Single Node
			Prefetch page count: 		32
			Container extent page count: 	32
			Base Table Schema: 		GRP1255977523782_12
			Base Table Name: 		OUT_PATIENT_DATA
			Columns in index:
				PMD(A)