Implementation of Physical Operators

Passing result up the Query Tree
- Example Query Tree:
- Fact:
- Data flow:

Techniques to pass results from an operator to another operator

There are 2 techniques used to pass the result of one operator to another operator:

Materialization:
Graphically:
Pipelining:
Graphically:

Advantage/disadvantages:

Materialization:

Simple to implement
High I/O costs....
Low memory size requirement (because we do not use extra memory buffers)
Very attractive for small memory computer systems

Pipelining:

Harder to implement
Lower I/O costs....
High memory size requirement (because we need extra memory buffers)
Attractive for large memory computer systems

Iterator

Iterator:

Iterator = an (program) object that provides (at least) the following 3 methods (operations):

open():

This method starts (prepares) the process of getting tuples
The method does not return any tuple
The open() method will initialize some data structures (e.g., buffers) needed to get tuples
If the object is a stored relation (on disk), the open() method will open the file of the relation
Otherwise (the iterator uses the result of other operators), the open() method will call the open() method of its argument(s)

getNext():

The getNext() method returns the next tuple in the object if there are tuples remaining.
The getNext() method returns NOT-FOUND if the object has no more tuples.
The getNext() method must update its data structure to allow subsequent tuple(s) to be retrieved

close():

The close() method cleans up the object
De-allocate any memory used for data structures created by open()
If the object is a stored file, the close() method will close the opened file

Otherwise (the iterator uses the result of other operators), the close() method will call the close() method of its argument(s)

Example iterator: Table-Scan

The following pseudo code are the implementation of the Table-Scan iterator at the kernel level
I.e.:

Open():

Open( ) { Open target relation R; Allocate 1 block buffer space at b; b = read first block of R; t = points to first tuple in block b; }

getNext():

getNext( ) { /* ============================================================== Make sure t points to a valide tuple before proceeding ============================================================== */ if ( tuple t has passed the last tuple in block b ) { /* ------------------------------------------------- Re-position t to be the first tuple of next block ------------------------------------------------- */ Read the next block into b; if ( there is no next block ) { return NotFound; } else { t = first tuple in block b; } } /* =========================================== Here: t points to a valid next tuple =========================================== */ curr_t = t; // Save the return value increment t to the next tuple in block b; return curr_t; }

Close( ):

Close( ) { free buffer; close file; }

Example iterator: bag union (union with duplicates)

Bag union:

Bag union = a union operation that does not remove duplicates

The implementation of BagUnion will illustrate the pipelining effect :

The tuples used by BagUnion are obtained by calling the getNext( ) method in ScanTable
The effect is:

Open( ):

ScanTable R, S = input relations to BagUnion Open( ) { R.Open(); // Call the Open() method in ScanTable !!! CurrRel = R; }

getNext():

getNext() { if ( currRel == R ) { t = R.getNext(); // Call ScanTable to get next tuple if ( t ≠ NotFound ) { /* ------------------------------ We read a valid tuple from R ------------------------------ */ return t; } else { /* =========================== R is done, we read S next =========================== */ S.Open(); // Call ScanTable's Open() currRel = S; // Make this if-statement stop executing } } /* ============================================= We will only reach here if R is exhausted... ============================================= */ return S.getNext(); }

Close( ):

Close( ) { R.Close( ); S.Close( ); }

Advantage of iterators

We can see the effect of a pipeline of iterators in the previous example:

BagUnion uses the following 2 input arguments to compute the union:

R S

The tuples of the relations are fetched by an iterator ScanTable
The iterator ScanTable passes the tuples of a relation to BagUnion through a memory buffer

Iterators can eliminate the need to materialize a relation as follows:

A pipeline of iterators is created:
the result of one operator is passed to the input of the next operator through memory (buffer) without having to write the tuples to disk

Example of a bad iterator: Sort-scan

Sort-scan:
The implementation of Sort-scan will illustrate that iterator is not always the most suitable approach to implement the operator

Open():

Open( ) { Open target relation R; Read all tuples of R into memory; Sort the tuples; b = points to first block of R; t = points to first tuple in block b; }

getNext():

getNext( ) { /* ============================================================== Make sure t points to a valide tuple before proceeding ============================================================== */ if ( tuple t has passed the last tuple in block b ) { /* ------------------------------------------------- Re-position t to be the first tuple of next block ------------------------------------------------- */ Make b point to the next block of R; if ( there is no next block ) { return NotFound; } else { t = first tuple in block b; } } /* =========================================== Here: t points to a valid next tuple =========================================== */ curr_t = t; // Save the return value increment t to the next tuple in block b; return curr_t; }

Close( ):

Close( ) { free buffers; close file; }

Note:

The implementation requires a large amount of memory buffer and it clearly undesirable.
Materialization in this case, will be a better approach
(A Scan-Table iterator can then be used to access the sorted result on disk.
The pipeline will become "segmented")

User-level implementation of iterator: C++ example

ScanTable:

Read tuples from a relation stored on disk
When you use UNIX system calls, you rely on the UNIX O.S. to perform the buffering for you....
Things will be a little different

The header file:

class ScanTable { public: FILE *fp; int fd; char TableName[33]; // Name of the relation DataDescription DataDes[10]; /* Holds data descriptions for upto 10 fields */ int n_fields; /* Actual # fields */ int record_size; char buf[1000]; // Buffer to store tuples int Open(char *relname); int GetNext(); void Close(); int getAttrType( char *attrName ); // Get data type of attr attrName int getAttrSize( char *attrName ); // Get size of attr attrName void *getAttrPtr( char *attrName ); // Get pointer to attrName in buf[1000] void PrintTuple(); void PrintRelationInfo(); };

The implementation of the Open() method:

int ScanTable::Open(char *relname) { /* ------------------------------------------------ Open the catalog to file the relation definition ------------------------------------------------ */ if ( (fp = fopen("catalog", "r")) == NULL ) { cout << "Error: cannot open database catalog `./catalog'" << endl; return 0; } n_fields = 0; record_size = 0; /* ================================================== Read attribute information on relation "relname" ================================================== */ while ( fscanf(fp, "%s %s %c %d %d", DataDes[n_fields].relname, DataDes[n_fields].fieldname, &DataDes[n_fields].fieldtype, &DataDes[n_fields].startpos, &DataDes[n_fields].fieldsize) > 0 ) { if ( strcmp( DataDes[n_fields].relname, relname ) == 0 ) { /* ========================================= This field belongs to the relation ! Adjust the computation of the record size ========================================= */ if ( DataDes[n_fields].startpos + DataDes[n_fields].fieldsize > record_size ) { record_size = DataDes[n_fields].startpos + DataDes[n_fields].fieldsize; } n_fields++; // Accept this field } } fclose(fp); // Close the catalog /* ----------------------------------------- Check if the relation "relname" was found ----------------------------------------- */ if ( n_fields > 0 ) { /* ======================================= Found: open the data file ======================================= */ if ( (fd = open(relname, O_RDONLY)) == -1 ) { cout << "Error: canot open relation file " << relname << endl; return 0; } strcpy( TableName, relname); return 1; // Return success code } else { return 0; // Return error code } }

Note:

I was lazy to allocate a buffer dynamically.

Better code is:

char *buf; buf = malloc( record_size + 1 );

The implementation of the getNext() method:

int ScanTable::GetNext() { int n; n = read(fd, buf, record_size+1); /* ---------------------------------------- Return a success or error code ---------------------------------------- */ if ( n == record_size+1 ) { return 1; } else { return 0; } }

The implementation of the Close() method:

void ScanTable::Close() { close(fd); // Close relation file }

Using an iterator

Here is a simple program on how to use an iterator:

ScanTable *f = new ScanTable(); if ( f->Open( "relationName" ) == 0 ) { cout << "Relation not found !" << endl; exit(1); } while ( f->GetNext() != 0 ) { /* ------------------ Print it... ------------------ */ f->PrintTuple(); // I added a print tuple method in the class.... } f->Close();

Example Program: (Demo above code)
- Prog files: see /home/cs554000/Handouts/Data
How to run the program: