SELECT mumblings

SELECT statement:(Run on Oracle 9i)
SELECT EMPNO,EMPNAMEFROM EMPWHERE EMPNO <>1000
----------------------------------Order of visit:1. The 'FROM' clause is visited first2. The 'WHERE' clause is visited next3. The 'SELECT' clause is visited next----------------------------------
The 'FROM' clause gives the DBMS engine the list of data-sources that will used in the query, which is 'EMP' in this case.
The 'WHERE' clause gives us the filtering condition, based on which thetable's rows can be retrieved. All indexes on the table in the FROM clause are retrieved. Next a check is made to see if any of the indexes can be used retrieve data If it can be the index is used. Here the condition is that 'EMPNO' should lie between 1000 and 1005 Assuming NO index using the EMPNO column, every row of the EMP table is visited and in each row, the EMPNO value is checked. If it lies between 1005 and 1000, this row is considered for the next step else the row is discarded. So now we have a set of rows from the table(in the FROM clause),satisfying the conditions(in the WHERE clause). Each row having all the columns of the table EMP. The 'SELECT' clause allows us to SELECT the required columns from each row returned by the WHERE clause.----------------------------------Internally the SELECT query we issue will be translated into a function call.
get_details(TABLE_NAME,SELECT_LIST,WHERE_CONDITION){
--'FROM' clauseTABLE_STRUCTURE := get_table_details(TABLE_NAME);
--'WHERE' clauseFOR EVERY condition in WHERE_CONDITIONLOOP FOR EVERY row in TABLE_STRUCTURE loop
IF validate_condition(WHERE_CONDITION,TABLE_STRUCTURE[current_row]) = TRUE then TABLE_STRUCTURE[current_row].AVAILABLE_FOR_NEXT_STEP := TRUE; ELSE TABLE_STRUCTURE[current_row].AVAILABLE_FOR_NEXT_STEP := FALSE; END IF;
END LOOP;END LOOP;
--'SELECT' clauseFOR EVERY row in TABLE_STRUCTURE[current_row].AVAILABLE_FOR_NEXT_STEP=TRUELOOP RECORDSET := get_columns(TABLE_STRUCTURE,columns_in_select_list); RETURN THE RECORDSET to the querying programEND LOOP;} TABLE_STRUCTURE is a RECORD of{COLUMN_NAME_LIST,COLUMN_DATATYPE_LIST,LIST_ROW_DATAAVAILABLE_FOR_NEXT_STEP};
LIST_ROW_DATA is a RECORD of{ROW_ID,ROW_DATA[no_of_COLUMNS(TABLE_NAME)]}
RECORDSET is a RECORD of{COLUMN_HEADER_LIST,ROW_LIST}
ROW_LIST is a RECORD of{ROW_DATA[no_of_COLUMNS_header_list(RECORDSET)]}
The above function-call processing is one WAY the SELECT query can be processed.Depending on whether the table's data is already cached/or not, on Indexes coming into use, on the table present in a different schema, on the statisticscollected on the table etc, there can be hundreds of different ways the SELECT query we issue can be translated into a function call(This is whyOracle generates execution plans dynamically).
We have considered here the simplest SELECT we could have, and still the lower-leve function calls are fairly complex, me wondering how much MOREOracle's engine must be doing to ensure the execution of more complex(real-world)queries.