ORACLE INSIDE

the kernel has many importent tasks,such as handling  all physical data transport between memory and external storage,manageing concurrency and providing transaction isolation. application programs. application programs and users can communicate with the kernel by using the sql language. a database user has a password and certain database privileges. a database schema is a logical collection of database objects that is usually owned by the user of the same name. the data dictionary is more or less the internal housekeeping administration of oracle. the data dictionary stores information about the data also referred to as metadata. the data dictionary is automatically maintained by oracle, thereforem the data dictionary is always up-to-date.data dictionary access is a potential security risk. that is why the oracle dbms offers system privileges and roles to regulate and protect access to the data dictionary. the dynamic performance views are a special category. these views are not based on database tables,but rather on information from other sources such as internal memory structures. they are mainly relevant and accessible to database administrators. most data dictionary view ames gives a clear indication of their contents.  constraint definitions are stored in the data dictionary. the two most important views are user_constraints and user_cons_columns. oracle provides many diagnostic tools to help you with your performance-tuning efforts. the alter session command provides another convenient way to save you the effort of presixing object names with their schema name.  the rollup addtion results in four additional rows,marked with for readability. three of these four addtional rows show the head count per department over all jobs and the last row shows the total number of employees.any means the result is true for at least one value returned by the subquery. all means the result is true for all values returned by the subquery. the exists operator checks for only the existance of subquery result. if the subquery returns at least one resulting row, the exists operator evaluates to true, if the subquery returns no rows at all, the result is false.

so far, we have distinguished only single -row queries and subqueries returning any number of rows. at this point,it make sense to identify a third subquery type:scalar subqueries. the name indicates an inportant property of this type of subquiers: the result not noly consists of precisely one row,but also with precisely one column value. you can use scalar suibqueries almost everywhere in your sql command in places where a column expression or literal value is allowed and make sense. top-n-sql is based on  using a subqueries in the from clause. and offers very good performance and a syntax that is more readable than equivalent alternative solutions. if you define multiple subqueries in the with clause, you are allowed to refer to any subquery name that you defined earier in the same with clause . that is the definition of subquery v2 can refer to v1 in its  from clause, and the definition of v3 can refer to both v1 and v2.
under the hood, the oracle dbms has two ways to execcute queries with a with clause: merge the subquery definitions into the main query, this make the subqueries behave just like in line wviews; execute the subqueries, store the results in a temporary table and access the temporary tables from the main query. the default window is the entire table. the details about the partition by and order by components of a widow specifications.