I was just playing the xmlgroup function, and i have a table with 70
columns and 10 million rows. I just gave the following query
Query :
====
select xmlgroup(c.client_cd option as attributes row "clients" root
"clients" )
from arun.clients c
fetch first 2 rows only

Plan :
====
Rows
Operator
(ID)
Cost

1
n/a
RETURN
( 1)
49133.3
n/a
GRPBY
( 2)
49133.3
n/a
BTQ
( 3)
38725.5
n/a
IXSCAN
( 4)
25584
Index:
arun.test1

The index arun.test1 contains two columns - client_cd, and type_id.
The index is unique and is used for enforsing the primary key.

I am astounded to why the cost should be so huge, I am still waiting
for the query to give me two rows under a xquery doc node. Can someone
please help me?

Also the table arun.clients is table partitioned - contains 85
partitions - each one for a client. The size of partitions may differ
between 10 rows to 100 k rows. Also the index is not partitioned. The
table is compressed.

XMLGROUP implies grouping, and you didn't specified GROUP BY clause
nand WHERE clause.

So, all rows in the table will be grouped in one group.
Then, the query fetches first two rows
(actually only one row will be returned.)


Also refer to the manual
"DB2 for Linux, UNIX, and Windows SQL Reference Volume 1"
---> Chapter 5. SQL queries ---> Subselect

The clauses of the subselect are processed in the following sequence:
1. FROM clause
2. WHERE clause
3. GROUP BY clause
4. HAVING clause
5. SELECT clause
6. ORDER BY clause
7. FETCH FIRST clause