Home » SQL & PL/SQL » SQL & PL/SQL » some interview questions
some interview questions [message #245614] Mon, 18 June 2007 04:04 Go to next message
beetel
Messages: 96
Registered: April 2007
Member
During an interview, I failed to answer the following questions. I hope you can help me figure out the answers..

1. What is the advantage of using Materialized views? Why do we need it if we can just truncate/insert into an ordinary table which can act as 'complete refresh' or simply update the table as equal to 'fast refresh'?

My answer was it has the ability to 'query-rewrite', but the interviewer said that there is something else..

2. Why don't we use the PCTFREE and PCTUSED clauses in creating an index? An index table also changes as the indexed column gets updated, or as we add/delete records to/from the table, the indexes also get affected, so why don't we have PCTFREE and PCTUSED in index creation?

I just shook my head..
Re: some interview questions [message #245766 is a reply to message #245614] Mon, 18 June 2007 13:24 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
you will find all answer over here
http://www.oracle.com/technology/documentation/index.html
Re: some interview questions [message #245821 is a reply to message #245766] Mon, 18 June 2007 21:57 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
1. I would say that Truncate/Rebuild is the equivalent of a Complete Refresh. MVs can also be configured as Fast Refresh, which save an awful lot of programmer effort to achieve the same functionality. There is also an argument for packaging the code in with the MV structure - it's nicely encapsulated in a way that a separate program and table would not be.

2. Indexes have PCTFREE but not PCTUSED. PCTUSED in a table determines when a shrinking 'full' block becomes empty enough to take new inserts. Oracle does not even try to insert rows into full blocks. Since indexes are 'organised' (I would say 'sorted', but we had a heated argument over that a while ago and it's not technically true), every new row has only one block where it 'belongs'. If that block has space, great, otherwise Oracle makes space by splitting it into two blocks. ie. Oracle doesn't care whether the block is full or not, the row belongs where it belongs. Without the need to track non-full blocks for availability, PCTUSED is not required.

Ross Leishman
Re: some interview questions [message #245842 is a reply to message #245821] Tue, 19 June 2007 00:10 Go to previous messageGo to next message
beetel
Messages: 96
Registered: April 2007
Member
Thanks a lot Ross. There's just something I cannot quite absorb about your statement below. Can you please explain further?

Quote:
If that block has space, great, otherwise Oracle makes space by splitting it into two blocks. ie. Oracle doesn't care whether the block is full or not.


The 'otherwise' in your statement makes me think that if the block has no more space, then the block is split into two. But then 'Oracle doesn't care whether the block is full or not'... ???
Re: some interview questions [message #245853 is a reply to message #245842] Tue, 19 June 2007 01:20 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Another advantage of materialized views can be that they allow you to create more complex check constraints. Here's an example.

MHE
Re: some interview questions [message #246098 is a reply to message #245853] Tue, 19 June 2007 22:15 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
"Oracle doesn't care whether the block is full or not" for the purpose of finding an appropriate place to stick the new index entry. Obviously it does care once it finds the block and discovers whether the new entry will fit.

PCTFREE and PCTUSED in tables assists in the finding process. This is immaterial to indexes, although indexes use PCTFREE as a measure of 'fullness' for the purpose of determing whether to split a block even though the new entry could technically fit into the free space.

Ross Leishman
Re: some interview questions [message #246485 is a reply to message #246098] Thu, 21 June 2007 01:34 Go to previous message
beetel
Messages: 96
Registered: April 2007
Member
Got it! Thanks for your patience.
Previous Topic: Change primary key value
Next Topic: getting error while executing SQL script
Goto Forum:
  


Current Time: Mon Dec 05 09:10:29 CST 2016

Total time taken to generate the page: 0.17247 seconds