Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

In Oracle's document The Query Optimizer, under View Merging, I found the following information

The view merging optimization applies to views that contain only selections, projections, and joins. That is, mergeable views do not contain set operators, aggregate functions, DISTINCT, GROUP BY, CONNECT BY, and so on. (emphasis mine)

Yet, I can only guess as what such a projection actually refers to.

share|improve this question

3 Answers 3

up vote 11 down vote accepted

In Relational algebra, projection means collecting a subset of columns for use in operations, i.e. a projection is the list of columns selected.

In a query optimiser step, the projection will manifest itself as a buffer or spool area of some description containing a subset of the columns from the underlying table or operator, or a logical view based on those columns that is used by subsequent operations.

In a view, a projection equates to the list of columns selected in the query underlying the view.

share|improve this answer

Rightly said by the earlier Answer: Simply Projection means selecting the name of the columns which one wishes to see appearing in the answer. The Underlying could be a table or a view. And the Projections highlighted by you is also the same.

share|improve this answer

Projection refers to that subset of the set of all columns found in a table, that you want returned. It can range anywhere from 0** up to the complete set.

There are two "sets" in a table that correspond to a table's two dimensions. Each table has a set of columns as well as a set of rows. Each individual value in a table can be found at a specific intersection of these two sets*. However, your value is not found by "going to" an address, such as T60, the way you would with Excel.

There is no order inherent in relational tables. That's important to remember. Instead, to retrieve your single value, you would select for a subset of the columns (one in this case) and a subset of the rows (one in this case). To select a subset of one column from all those available, you only need to know its name, as well as the table name. The column name will be unique within its table.

Once you've chosen the column your value is in, you'll need to choose the particular row it's in. However, rows don't have names the way columns do. The way we specify a row is . . . well, we don't exactly . . . We specify something that we know about the value we're looking for. You have to know something related to (literally) the value you're looking for. If you can specify enough related information then you may be able to reduce the set of returned rows to just the one you're looking for.

Think of it as though a college student lost his or her backpack. Let's say a guy lost it. He calls lost and found, and the woman attending says "Yea, we have a couple dozen backpacks. Can you describe it?" You say "Well, it's blue?". To which she replies "Are you asking me or telling me", and then says "I have eight blue ones, gonna have to do better than that, and you didn't sound too sure about that." You say "Let's see, umm, oh yea! It had my math 1010 book in it." "Good, now you're down to four." Then you remember you're supposed to meet your girlfriend, Lucy, in 15 minutes, and that triggered another memory - of the time you were bored in Math 1010 and wrote, - in real small letters on the bottom of the backpack, "I love Lucy". She turns them over and sure enough, she says "the third one says 'I love Lucy' on it. Come pick it up, Ricky."

Projection might be likened to saying what you lost - a backpack. Selection may be likened to describing its attributes: it's blue, has a math 1010 book inside, and has "I love Lucy" written on the bottom.

You do the same thing with SQL. First, what kind of information you want to see. Second, criteria describing which one or ones you want to see. These are called predicates or truth statements. They are true for one or more members of the set. If they aren't, they do not return any values.

  • Some vendors' SQL implementations provide means to break these rules, such as Nested Tables in Oracle. However, standard two dimensional tables are still the predominate form.

** C.J. Date wrote a very interesting article called "A Table With No Columns". I found it very worth reading, as I do most of his many "Writings" articles. I highly recommend them!

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.