Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Duke.

Asked: March 28, 2004 - 10:17 pm UTC

Last updated: August 02, 2010 - 8:59 am UTC

Version: 8.1.7

Viewed 1000+ times

You Asked

Tom-- First, let me say that I became a big fan of yours because you advocate and practice benchmarking. When I first started as a database modeler/developer/architect, I lost a some arguments because the DBAs at my former employer collectively argued against my ideas, touting their lengthy experience while sneering at the concept of benchmarking (I ultimately left because of that attitude). Benchmarking, to me, is the difference between computer "science" and computer "quackery".

My question is this: what certifications / training do you recommend for becoming a database developer like yourself? Oracle seems to offer lots of DBA training, but the developer certification track seems more focused on Forms, and doesn't seem to offer the depth your website and 2 books offer. I've been working my way through your books for the past 6 months (and I'm far from completing them!). It's made a world of difference to my professional abilities, but are there any formal courses you'd recommend? A deep and respectful thank you for your work!
-- Duke

and Tom said...

I have no certifications myself, short of the "school of life".

I find answering questions, participating in the discussions, taking part in the community to be the biggest benefit.

groups.google.com -> comp.databases.oracle.*

otn.oracle.com -> discussion forums

reading dbazine.com or any of the hundreds of sites out there and asking questions (eg: read an article, find something you don't think "is right", setup a test case, put it on the groups and see what people say).

That is the way I learn myself (learn, present tense)


I'll publish this to see if anyone wants to comment further.

Rating

  (30 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

What? You dont have certifications??

Venkatesh, March 29, 2004 - 11:56 am UTC

Tom,

I am really startled when i read that you dont have any certifications.

Yes i do agree that, we can learn a lot from helping people in resolving their technical issues, joining the discussions, reading articles, etc.

But many organizations ask certified people. What do you say about this? Even people with strong technical skills are rejected just because they are not certified.

I hope u have a justified reply.

Thanx n regards
Venkatesh

Tom Kyte
March 29, 2004 - 12:03 pm UTC

no one that works for me has them either....



My 2 cents

Marcio, March 29, 2004 - 12:03 pm UTC

My two cents on this thread.

o Have a good Mentor

o DIY (Do it yourself) as whenever as possible

o Try solve any problem post on any forums. This gave me knowledge to solve my own problems when they happen.




Tom Kyte
March 29, 2004 - 12:09 pm UTC

exactly -- the mentor part is very relevant.

Thanks!

Robert, March 29, 2004 - 12:19 pm UTC

Tom,

Thanks for being a faithful and gracious mentor to a lot of us!

P.S. Duke: "computer science" vs. "computer quackery" good one! very funny.

Robert.

practice makes perfect.....

denni50, March 29, 2004 - 2:28 pm UTC

The only way to learn this stuff is 'use-it' and 'do-it'. I'm in my third year working with Oracle and everyday is a "WOW" day for me...I learn something today I didn't know yesterday. Tom's website and books(other Oracle forums as well) provide real-world problems with real-world solutions that you can take and put into practice yourself.

There's nothing like 'hands-on' training and that is what you get with Tom.

I would never be where I am today (which is a whole lot more than where I was 2 years ago) if there was no Tom, books or website.

I recall one Oracle Professor telling us students..."when you come across a problem and need help there's only one website you need to go to 'AskTom.com'" ...we all scrambled to our class room workstations and logged on to see what this AskTom website was all about.

for what it's worth...that's my 2 cents.





Alberto Dell'Era, March 29, 2004 - 3:26 pm UTC

> I find answering questions, participating in the discussions, taking part in
> the community to be the biggest benefit.

I agree - I have started answering questions on a small italian-language forum and i've discovered that I very often learn more by providing an answer than the other way around. It seems illogical at first sight, but that's the way it works ("when teaching, the Teacher learns more than the Pupil" as we say in Italy).

I think that a question is a kind of small-scale work assignment - a way of expanding or perfecting your knowledge on a small real-case problem, and so getting more experienced (especially since i always provide a test case as you do).
Then, of course, if the answer is well received, you get the satisfaction coming from a work well done, and perhaps a bit of recognition.

Strange that no one else has yet commented on this; that's the main part of your answer IMHO ...

Just my 2 Eurocents.
bye
Alberto

Tom Kyte
March 29, 2004 - 3:30 pm UTC

....I agree - I have started answering questions on a small italian-language forum and i've discovered that I very often learn more by providing an answer than the other way around.

well said -- people don't believe me when they ask me "how did you learn so much". it was by answering questions on the newsgroups starting in 1994. You realize how much you DON'T know that way....

it is that i credit with much -- answering the questions. It is like doing a crossword puzzle -- gets the brain going in the morning.



Alberto Dell'Era, March 29, 2004 - 4:03 pm UTC

> It is like doing a crossword puzzle -- gets the brain going in the morning.

Especially with SQL, it's like solving a differential equation - playing in a perfect world of pure logic or mathematics (well, almost).

That's the reason I'm very fond of Oracle - it's always logical and predictable, and open as Nature is - a test case being like an experiment in a Physics class, and the kernel the subject under study; building a solution on it being like exploiting the laws of mechanics to build a car.

It's one of the last sw platforms of this kind - almost all the others requiring you to act like a Graduated Trained Monkey, pushing buttons here and there without really knowing what you're doing. Please preserve this ecosystem ;-)

Alberto

My 2.5 cents...

Kashif, March 29, 2004 - 4:07 pm UTC

Recently, while doing the interviewing rounds, I came across a company (actually a financial giant) that used Brainbench.com as a skills assessment test. And I must say that quite a few of those questions had me thinking, which is in stark contrast to the Oracle Certification exams that an experienced developer can ace blindfolded (maybe I exaggerate, but I'm sure other certified professionals know what I mean). So my suggestion would be to find out how valuable that certification is to the companies you're targeting, and get certified from them.
As far as becoming a better database developer goes, besides visiting the PL/SQL developer's mecca i.e. Tom's website, I'd suggest spending a lot of time going through the Oracle manuals, it's amazing what the documentation already explains, and I always keep reminding myself to go back to the docs for a solid understanding of various concepts.

And finally, maybe picking a book or two by Feuerstein, although don't take all his words to be gospel, there's quite a bit of stuff he explains that is more his preference than the correct way to do it. You might also want to check out Connor's book, "Mastering Oracle PL/SQL: Practical Solutions". I haven't read it myself, but if his postings over here and on Google are anything to go by then that book's definitely worth the buy.

Enough said.

Kashif

Database Developer Certification

Gordon, March 29, 2004 - 4:39 pm UTC

Kashif from Weston hit the nail on the head when he contrasted Brainbench with the Oracle certification exams. I, too, have been out there looking lately and have had similar thoughts about measurement metrics between the official Oracle exams and the standards that companies are using to weed out potential employees. I have taken all exams but 1Z0-033 on the 9i OCP track and felt a bit intimidated by some of the Brainbench questions.

On the bright side, the comment Kashif had about the Oracle documentation I couldn't more strongly agree with. I bought an Oracle press book for each of the exams (bought them all at one time..) before I realized that the depth of the Oracle 9i documentation is what is required to really learn the topics covered on the certification exams. I have spent months actually reading and trying examples from the Administrators Guide, the SQL Reference, etc prior to taking the exams and I would not recommend going into an exam without that level of preparation. Yeah, I do need a life, but that will come after certification...


To Alberto

denni50, March 29, 2004 - 4:59 pm UTC

Well I have to differ with you especially in the realm of Quantum Mechanics..Albert Einstein was quoted as saying..."God does not play with dice".

The laws of nature are far from predictable and logical, they are random and chaotic at this level of elementary particle physics.
Einstein remained forever conflicted with his dream of producing a Unified Field Theory linking QM and the General Theory of Relativity.

how's that for getting the synapses going in the morning.



to denni50

Alberto Dell'Era, March 29, 2004 - 5:23 pm UTC

Morning ? It's 12PM here in Italy ;-)

Well, definitely Oracle is built on a scale much bigger than that of an electron or a quark, and much smaller than the Galaxy or the compressed space of a black hole - i think that we can apply the (approximate) laws of classical mechanics to it ... or to its cpus and ram devices ;-)

I've followed the joke, only to stress that anyone really interested in Informatic as a science, not just as a way of earning your meals, is likely to enjoy Oracle much more than other play-with-me-but-don't-open-my-hood "platforms".

'nite
Alberto

PS May i recommend the book "Black Holes, Wormholes & Time Machines" by J. S. Al-Khalili ? In that book, there are a few examples of QM and GR "links", including the "black hole evaporation theory" by Stephen Hawkings ... very intriguing.

Tom Kyte
March 29, 2004 - 5:47 pm UTC

I enjoyed this one myself

</code> http://www.amazon.com/exec/obidos/tg/detail/-/0767908171/ <code>

little bit of that science, but pretty broad.

Alberto...

denni50, March 29, 2004 - 6:42 pm UTC

read Brian Greene's book 'The Elegant Universe'...theory
about "superstrings"..the link between QM and GTR still
remains elusive.

enough of this topic on Tom's website before he admonishes
both of us.

:~)

Certifications can simply do this

Bob, March 29, 2004 - 8:04 pm UTC

In my opinion, the certification is only a plus when you are applying for work at a place where they value it. Alone it's not much. But all other things being equal, it's one more thing to weigh in your favor. Sometimes the higher ups, with less technical experience than the underlings, look at it as a sign of a continued pursuit of interest in the field. He went to college 10 years ago, but since then, in addition to working he got his certifications in XYZ.

A nice topic

Muhammad Ibrahim, March 29, 2004 - 9:04 pm UTC

A nice and different topic to read. I really enjoyed and felt the reality while reading all your postings!

Regards,
Ibrahim.

How about show others that certification is very easy!!!!

sharmin, March 29, 2004 - 10:27 pm UTC

Do you think that if you go for certification exam without any prior preparation will pass it easily?

Does any certification whether DBA or Developer bear no value to you??



Tom Kyte
March 30, 2004 - 7:40 am UTC

bears no value to me, no.

I took my SATS some 20 odd years ago. multiple guess tests, never much into them. Give me a real problem, not a bunch of factoids that I should be looking up for clarity when I need them.



certs not worth a lot

Jim, March 29, 2004 - 10:51 pm UTC

Certificates in my mind are not worth a lot in and of themselves. What even makes them worth less is about a month ago I got an spam from somone trying to sell me certs for Oracle, MS, Cisco, Novell, and somthing else. All I had to do was sign up for the test through Oracle, MS, et al and send these people a fee. The manager of a testing center in India would have someone take the test for me.

I forwarded as much information about this as possible to the companies. (never heard anything back though)

Mirjana, March 30, 2004 - 3:22 am UTC

Certification is just another exam...past tense
And one has to learn every day...present tense (as Tom already said)

Nice topic & great site...





Tom can't say it loud !

SR, March 30, 2004 - 4:01 am UTC

The greatest virtue of this site is nothing but that it provides the professionals (and even aspirants) with a chance to have an open discussion with one of Oracle's most admired knowledge expert. Tom has always been kind enough to share his experiences and thus to guide and lead his "disciples" whenever they are in professional dilemma, technically or otherwise - that's why this thread is here. Tom is an indirect mentor and a role model for thousands of Oracle professionals across the globe, and hence he is bound to be cautious in his words, thanks to the possible impact it can bring about on the perception of his readers.

I know we people should be smart enough to read between the lines of Tom's answer to this thread. By revealing that he is not certified, Tom just wants to say this: "I know how pointless certifications really are!” He has chosen to restrain himself from being more blunt here; for such a remark from him will be against the interests of the company he is serving.

Tom, one more thing - purely out of context, kindly excuse me for that - how do you manage to get time to read other stuff like the works of Bill Bryson? I, even with a substantially lower professional profile, am having a tough time in finding some time to go through the morning paper, let alone the books in other areas of my interest. How are you managing your time?


Tom Kyte
March 30, 2004 - 8:18 am UTC

I fly alot :)

When on a plane, there is no network. I either watch a movie on the laptop or read books.

Never thought I'd be giving Tom advice, but here goes...

A reader, March 30, 2004 - 8:30 am UTC

install Personal Oracle on your laptop, get the docs in PDF on to your laptop, and lets get this party going! Though of course that's what I would do, tinker with stuff during downtime, and since you're the Oracle oracle, I suppose you could get away without following my suggestions... Rock on!

Tom Kyte
March 30, 2004 - 8:49 am UTC

[tkyte@tkyte-pc allOraDoc]$ du -ks *
411968  10g
58544   73
42572   80
234532  817
270492  9iR1
312296  9iR2



I only run EE on my laptop! but trust me, the Oracle docs are all there in pdf and i've been known to read them as well on the plane (just working through the 10g docs which as you can see are slightly larger than prior releases :)

See, If I didn't read the docs myself, I wouldn't have stumbled on this tiny demo I wrote up on the way to Puget Sound for the PSOUG conference last week...  

The concept is "you found a bad query.  You know if you rewrote it -- it goes really good.  However, it is buried in a 3rd party application.  You cannot touch the code.  Now what?".... Well:

ops$tkyte@ORA10G> create table dept
  2  as
  3  select *
  4    from scott.dept;
                                                                                                                       
Table created.
                                                                                                                       
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> create or replace view dept_v
  2  as
  3  select deptno, initcap(dname) dname, loc
  4    from dept
  5   order by loc;
                                                                                                                       
View created.
                                                                                                                       
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> select * from dept;
                                                                                                                       
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
 
<b>that is our "problem query".  we KNOW if we rewrote it as "select * from dept_v", it would be really cool... </b>


ops$tkyte@ORA10G>
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> pause
 
ops$tkyte@ORA10G> REM clear screen
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> begin
  2          sys.dbms_advanced_rewrite.declare_rewrite_equivalence
  3          ( name             => 'DEMO_TIME',
  4            source_stmt      => 'select * from dept',
  5            destination_stmt => 'select * from dept_v',
  6            validate         => FALSE,
  7            rewrite_mode     => 'TEXT_MATCH' );
  8  end;
  9  /
 
PL/SQL procedure successfully completed.

<b>there we go -- when we see select * from dept, rewrite it !</b>
 
ops$tkyte@ORA10G> pause
 
ops$tkyte@ORA10G> REM clear screen
ops$tkyte@ORA10G> select * from dept;
 
    DEPTNO DNAME          LOC
---------- -------------- -------------
        40 Operations     BOSTON
        30 Sales          CHICAGO
        20 Research       DALLAS
        10 Accounting     NEW YORK

<b>Note the sort order and the initcap, we rewrote the query without rewriting the query in the code...</b>
 
ops$tkyte@ORA10G> select * from dept for_real;
 
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

<b>it did it in this example by pure text matching only -- other queries won't be affected...  just the problem query</b>
 
ops$tkyte@ORA10G> pause
 
ops$tkyte@ORA10G> REM clear screen
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> exec sys.dbms_advanced_rewrite.drop_rewrite_equivalence( 'DEMO_TIME' );
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> select * from dept;
 
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
 
ops$tkyte@ORA10G>
 

connor's book

A reader, March 30, 2004 - 8:43 am UTC

Someone above had mentioned Connor's book, "Mastering Oracle PL/SQL: Practical Solutions". I have gotten this book and I am so happy that I waited to buy a PL/SQL book. I am only through a few chapters but it is exactly what I was looking for. It is excellent.

define: sats

Marcio, March 30, 2004 - 8:48 am UTC

sorry off-topic Tom, but I think I lost definition -- foreign people like me doesn't know what SATS is.
Though, I found this five definition, wich one were you taking about?

google - define: sats

1) Standard Assessment Tests/Tasks.

2) short for saturation levels (of oxygen in the blood)

3) Statutory Assessment Tests. National Tests in English, Maths and Science taken by all children at the end of Key Stages 1, 2 and 3.

4) Standard Assessment Tests

5) Standard Army Training System




Tom Kyte
March 30, 2004 - 9:02 am UTC

1) 3) and 4) would apply. 1) and 4) specifically....

sorry about that (but thanks for google'ing it!)

dbms_advanced_rewrite.declare_rewrite_equivalence

Alberto Dell'Era, March 30, 2004 - 9:12 am UTC

That alone is worth upgrading to 10g (third-party applications being my nemesis) ... really interesting.

Alberto

so Tom are you saying....

denni50, March 30, 2004 - 9:39 am UTC

with 10g using this:
dbms_advanced_rewrite.declare_rewrite_equivalence

you can take a 50+ column table like Gift(one of our
larger tables) and avoid using statements like:

Select * from gift....(which is not performance efficient)

using the below procedure:
begin
2 sys.dbms_advanced_rewrite.declare_rewrite_equivalence
3 ( name => 'NEW_GIFT',
4 source_stmt => 'select * from gift',
5 destination_stmt => 'select * from gift_v',
6 validate => FALSE,
7 rewrite_mode => 'TEXT_MATCH' );
8 end;

to convert the 50+ columns & list individually into a view
then have third party apps read from the view instead of
select * from gift.....correct?





Tom Kyte
March 30, 2004 - 10:34 am UTC

No, if you try, you'd get:

ops$tkyte@ORA10G> begin
  2          sys.dbms_advanced_rewrite.declare_rewrite_equivalence
  3          ( name             => 'DEMO_TIME',
  4            source_stmt      => 'select * from dept',
  5            destination_stmt => 'select * from dept_v',
  6            validate         => FALSE,
  7            rewrite_mode     => 'TEXT_MATCH' );
  8  end;
  9  /
begin
*
ERROR at line 1:
ORA-30389: the source statement is not compatible with the destination
statement
ORA-01789: query block has incorrect number of result columns
ORA-06512: at "SYS.DBMS_ADVANCED_REWRITE", line 29
ORA-06512: at "SYS.DBMS_ADVANCED_REWRITE", line 176
ORA-06512: at line 2
 


You would have to at least NULL out the columns:

ops$tkyte@ORA10G> create or replace view dept_v
  2  as
  3  select deptno, initcap(dname) dname, NULL loc
  4    from dept
  5   order by loc;
 
View created.
 
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> select * from dept;
 
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
 
ops$tkyte@ORA10G>
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> pause
 
ops$tkyte@ORA10G> REM clear screen
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> begin
  2          sys.dbms_advanced_rewrite.declare_rewrite_equivalence
  3          ( name             => 'DEMO_TIME',
  4            source_stmt      => 'select * from dept',
  5            destination_stmt => 'select * from dept_v',
  6            validate         => FALSE,
  7            rewrite_mode     => 'TEXT_MATCH' );
  8  end;
  9  /
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA10G> pause
 
ops$tkyte@ORA10G> REM clear screen
ops$tkyte@ORA10G> select * from dept;
 
    DEPTNO DNAME          L
---------- -------------- -
        10 Accounting
        20 Research
        30 Sales
        40 Operations

 

Mentor / Recommended books

Duke Ganote, March 30, 2004 - 9:58 am UTC

Tom-- Thank you so much for your response(s) to my/our questions. My follow up question would be: so who was your mentor?
-- Duke

BTW, I searched your site for your recommended books, and found your interesting example of "Question Authority" at </code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:8128590522738,

My search found (beside your own books, of course) you recommended the following books (or referenced the author positively).  Note that I don't claim to be exhaustive or present these in any particular order:

"Oracle 9i Java Programming: Solutions for Developers Using PL/SQL and Java" (you were technical contributor... I got it a month or so again, and am reading this), e.g.
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:5259303334653

Jonathan Lewis's "Practical Oracle8i", e.g.
http://asktom.oracle.com/pls/ask/f?p=4950:12:2858699005165820689::NO::F4950_P12_DATE_YYYYMMDD:20040304

Steve Muench's "Building Oracle XML Applications", which is copyrighted 2000, so doesn't cover the latest in XML, like XML schemas (I got it a few months ago), e.g.
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:4061080732051,

Steve Adams is frequently mentioned for Oracle Internals, e.g.
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:745985307605, <code>
and has a book that you referenced entitled "Oracle8i Internal Services for Waits, Latches, Locks, and Memory"


Very cool

Dan Kefford, March 30, 2004 - 11:16 am UTC

Tom...

Where is the metadata from DBMS_ADVANCED_REWRITE stored? That is, if you used this to set up an equivalence between "select * from dept" and "select * from dept_v", is that reflected in the data dictionary somewhere? (Haven't installed 10g yet to answer this question myself ;)

Also, does DBMS_ADVANCED_REWRITE have anything to do with how query rewrites are used in materialized views?



Tom Kyte
March 30, 2004 - 1:23 pm UTC

ops$tkyte@ORA10G> select * from DBA_REWRITE_EQUIVALENCES;
 
OWNER                          NAME
------------------------------ ------------------------------
SOURCE_STMT
------------------------------------------------------------------------
DESTINATION_STMT
------------------------------------------------------------------------
REWRITE_MO
----------
OPS$TKYTE                      DEMO_TIME
select * from dept
select * from dept_v
TEXT_MATCH
 


it uses the same underlying techniques as the "mv" query rewrite. 

Certifications have a positive side

A reader, April 05, 2004 - 9:19 am UTC

Hi,

I agree that getting certified does not guarantee that you will be able to solve all your issues, but it has a positive side.
If you want to get ORACLE DBA certified you have to cover all the features of the database. That does not mean that you know them all but when you have an issue or you are implementing something you know that the features are there.
I met a girl who was certified that had trouble creating a table; on the other hand I am the only certified dba in my group of 10 and I became the technical lead.


On another note...

A reader, April 05, 2004 - 5:12 pm UTC

<Rant>
It's nice to know that even Tom has to read the documentation, although now that I spend some of my own CPU's cycles thinking about it, it isn't like Oracle's technology and its new features are revealed to him by some divine power... don't know what I was thinking. And the note on EE above, I installed it once on my Windows XP machine, and then un-installed it, and since then I have not been able to re-install EE on my machine, it just makes the fan go into overdrive, and at the end of it all, just happily stops working, as if I didn't command it to install Oracle but rather offered it up as a suggestion, and it decided that it was too much work. Anyway, I better get out of here in case I say something about Microsoft I regret... Later all!
</Rant>

ocp but no project exp.

Anuja, April 07, 2004 - 2:18 pm UTC

Dear Tom

I hope that you enjoyed your vacation and had really good time with your dear ones.

Now my question is not only to you but to the oracle management too. I did my certification (I know that you do not hold any certification, which is quite surprising) but anyways, my problem is that i do not have any project experience in oracle 8i or 9i. that is why, i am unable to get any job, though every training centre assured that after completion of certifcation track, there should not be any problem to get better job opportunity(s). so could you suggest some solution to this problem, which i am very much sure that lot of other professionals would also be benefitted.

Thanking you,

Master Certification

A reader, January 19, 2005 - 10:56 pm UTC

Hi Tom, I don't know where to ask it. If you can helpme please.

1. Could I ask you (Oracle) please, to allow remote Master certification, in this time, where we have internet, etc. etc.
I think is reliable to do a remote master examen, even when it costs 3000 instead of 2000.
I don't understand why I have to go to eeuu, to take that exam, is true is a hard exam, and is necesary comunication, but it is a limitation.

I don't know if you could move t his solicitude to Oracle University, because they still didn't published the 10g master exam details. :)

2. About getting the certification, I think prometric certification is very interesting because is centers in some critical tasks you should keep in mind, I find them very useful, and definetively the certification, even if you don't learn nothing with it, means a better salary soon or late.
I challenge you why don't you certify is a very interesting process, and maybe you could enrich it, no joking.

Thanks

dbms_advanced_rewrite

Anandji Varadarajan, June 18, 2007 - 5:28 am UTC

I seem to be missing dbms_advanced_rewrite in my 10g EE. Is there a script available?
Tom Kyte
June 20, 2007 - 9:09 am UTC

it is in the sys schema
might not have a synonym
you might need execute granted on it.

DBMS_ADVANCED_REWRITE - in 11gR1

Rajeshwaran, Jeyabal, July 31, 2010 - 10:00 am UTC

scott@ORCL> select * from v$version;

BANNER
------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE    11.1.0.6.0      Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

scott@ORCL> create table customer
  2  nologging
  3  as
  4  select rownum as cust_id,
  5         object_name as cust_name,
  6         object_type as cust_type,
  7         created  as  created_dt
  8  from all_objects
  9  /

Table created.

Elapsed: 00:00:10.34
scott@ORCL> create table sales
  2  nologging
  3  as
  4  select decode(mod(level,366),0,1) as cust_id,
  5         abs(dbms_random.random)/100 as sales_amt,
  6         trunc(sysdate,'yyyy')+level as trans_date
  7  from dual
  8  connect by level <=100000
  9  /

Table created.

Elapsed: 00:00:01.46
scott@ORCL> alter table customer add constraint cust_pk primary key(cust_id);

Table altered.

Elapsed: 00:00:02.14
scott@ORCL> alter table sales add constraint sales_fk foreign key(cust_id) references customer;

Table altered.

Elapsed: 00:00:00.89
scott@ORCL> begin
  2    dbms_stats.gather_table_stats(ownname=>USER,tabname=>'CUSTOMER',estimate_percent=>100,cascade=>true,method_opt=>'for all indexed columns size 254');
  3    dbms_stats.gather_table_stats(ownname=>USER,tabname=>'SALES',estimate_percent=>100,cascade=>true,method_opt=>'for all indexed columns size 254');
  4  end;
  5  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:08.59
scott@ORCL> create table cust_sales_mv
  2  nologging
  3  as
  4  select c.cust_id,count(s.cust_id) cust_count,sum(s.sales_amt) as sales_amt
  5  from customer c,sales s
  6  where c.cust_id = s.cust_id (+)
  7  and   c.created_dt between to_date('01/01/2005','mm/dd/yyyy') and to_date('12/31/2005','mm/dd/yyyy')
  8  group by c.cust_id
  9  /

Table created.

Elapsed: 00:00:00.06


scott@ORCL> begin
  2     sys.DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALENCE (
  3             name =>'demo_rewrite',
  4             source_stmt => ' select c.cust_id,count(s.cust_id) cust_count,sum(s.sales_amt) as sales_amt '||
  5                                             ' from customer c,sales s '||
  6                                             ' where c.cust_id = s.cust_id (+) '||
  7                                             ' and   c.created_dt between to_date(''01/01/2005'',''mm/dd/yyyy'') and to_date(''12/31/2005'',''mm/dd/yyyy'') ' ||
  8                                             ' group by c.cust_id ' ,
  9             destination_stmt =>' select * from cust_sales_mv ',
 10             validate => false,
 11             rewrite_mode =>'TEXT_MATCH' );
 12  end;
 13  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.76
scott@ORCL> set autotrace traceonly explain;
scott@ORCL> select c.cust_id,count(s.cust_id) cust_count,sum(s.sales_amt) as sales_amt
  2  from customer c,sales s
  3  where c.cust_id = s.cust_id (+)
  4  and   c.created_dt between to_date('01/01/2005','mm/dd/yyyy') and to_date('12/31/2005','mm/dd/yyyy')
  5  group by c.cust_id
  6  /
Elapsed: 00:00:00.14

Execution Plan
----------------------------------------------------------
Plan hash value: 134665541

--------------------------------------------------------------------------------
| Id  | Operation           | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |          |     1 |    29 |   225   (2)| 00:00:03 |
|   1 |  HASH GROUP BY      |          |     1 |    29 |   225   (2)| 00:00:03 |
|*  2 |   HASH JOIN OUTER   |          |   250 |  7250 |   224   (2)| 00:00:03 |
|*  3 |    TABLE ACCESS FULL| CUSTOMER |   170 |  2380 |   138   (1)| 00:00:02 |
|   4 |    TABLE ACCESS FULL| SALES    |   100K|  1464K|    85   (2)| 00:00:02 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C"."CUST_ID"="S"."CUST_ID"(+))
   3 - filter("C"."CREATED_DT">=TO_DATE(' 2005-01-01 00:00:00',
              'syyyy-mm-dd hh24:mi:ss') AND "C"."CREATED_DT"<=TO_DATE(' 2005-12-31
              00:00:00', 'syyyy-mm-dd hh24:mi:ss'))



Tom:
working with DBMS_ADVANCED_REWRITE in 11GR1 and founded that Query rewrite doesnot happens. Optimizer still uses Customer and Sales table rather than cust_sales_mv. Checked the product documentation http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/d_advrwr.htm#i999507
But not able to find out the problem in my code. Can you please help me what i am doing wrong?


Tom Kyte
August 02, 2010 - 8:59 am UTC

query rewrite integrity is set to what? needs to be trusted or stale tolerated
http://docs.oracle.com/docs/cd/B28359_01/appdev.111/b28419/d_advrwr.htm#sthref130