Re: Nested data types and query speed?
Al Wang (alwang@NOSPAMdoubt.com) wrote:
: Can anyone give me any information about the effect of IUS's
: implementation of nested data types(using row types) on query speed?
: How does it compare to a flat relational table?
Well, even though the definition of the table consists of
multiple, nested data types, a row's data is still stored contiguously
on the data page. There may be a small impact with the way that
rows must be unravelled to make nested dot notation work, but
that won't be a very big hit. With apologies to those not in the
US (for the ZipCode phenom).
CREATE ROW TYPE PersonName (
Family_Name varchar(32) NOT NULL,
First_Name varchar(32) NOT NULL,
Middle_Names varchar(32) NOT NULL,
Title Title_Enum NOT NULL
);
CREATE ROW TYPE ZipCode (
Major INTEGER NOT NULL,
Minor INTEGER
);
CREATE ROW TYPE MailAddress (
Line_1 varchar(48) NOT NULL,
Line_2 varchar(48) NOT NULL,
City varchar(32) NOT NULL,
State State_Enum NOT NULL,
ZipCode ZipCode NOT NULL
);
CREATE ROW TYPE Customer (
Name PersonName NOT NULL,
Address MailAddress NOT NULL,
Photo Image NOT NULL
);
CREATE TABLE Customers
OF TYPE Customer;
-- Each row will have all of the row's data stored together
-- on the page. Therefore, queries like;
SELECT Name
FROM Customers
WHERE Address.ZipCode.Major = 1234;
-- Will do (close to) the same amount of I/O as the alternative.