(my answer to #3 was "most of it")

I use documentation -- but mostly rely on tests to prove it. Documentation can be misleading or incorrect from time to time (not often) -- nothing like a test case to prove the point (or more likely, disprove the point)

a lob is a lob locator generally, a pointer. it is small.


lobs can be inline or out of line. by default lobs are allowed to be inline, meaning that if they are under about 4000 bytes (like a varchar2 field), they will be stored "in the row". Once they exceed that threshold - they are replaced with a pointer and stored out of line -- in the lob segment elsewhere.


LONGS and LONG RAWS did not behave this way, they were always stored inline

what about the obvious answer?

select * from t where column = 'particular text';


then again, they might have been trying to get you to say "interMedia text" -- context indexing.

see the text application developers guide:

</code> http://docs.oracle.com/docs/cd/B10501_01/nav/docindex.htm#index-TEX <code>

when you search on asktom, you are using that technology.

show me Oracle documentation that says "the redo generation is no different after an alter tablespace begin backup has been issued"


otn.oracle.com has all of the docs, post a link to it.

what "necessary info".

if your search is as specific as your question -- I can understand why you got lots of hits :)

it doesn't maintain read consistency, the backups are fuzzy/inconsistent (unless is was "cold").. Think of the ora-1555's that would happen during a long running backup otherwise.

what tool would your company use for java? for VB? for C? use the same tool, same methodology. It is just "code"

what is handed over to a client is what the client askes to be handed. Oracle Designer is frequently used as our repository on large systems. The needs vary from situation to situation

the undo generated for an insert is the sum of

a) the changes made to any indexes affected
b) a "delete rowid" entry for each inserted row (so we can "un-insert" them)

bad synonym perhaps.

they did a alter session set current_schema=something_else perhaps (so select * frm t isn't select * from t, it is from something_else.t, not the t you thought)

(1) that is a hard/ambiguous question to answer.

Do they mean capacity planning for a system that doesn't exist yet, one you are going to build? (many do).

In which case my answer is "tell me how much money you have and I'll buy all of the equipment I can with that much money and we'll see after it is done if it will work". I've not seen anyone really be able to spec out what they need before they build it. No one can answer things like "how many transactions", "how big are the transactions", "how many concurrent transactions" and so on.

Do they mean capacity planning for a 3rd party product that hasn't been implemented yet?

In which case my answer is "ask the vendor for a system sizer - they can answer the above questions - we cannot - and you need to know those answers"

Do they mean capacity planning for an existing application that has been running for a while?

Then I want access to AWR and ASH information - so I can discover the answers to those questions. It'd be nice to have access to the change management pack too so I could see growth rates and stuff. In other words - it takes a million metrics to plan for capacity and you'd have to have been gathering them for a while - as the database does (as long as you have access to it)


(2) The only answer to this is "it depends".

Please give me a scenario. They are all different. The answer might be "turn on sql trace, run the application, find the slowest sql, tune it". The answer might be "give me a statspack/awr and let's see what we see".

The process would be:

(a) define what the performance issue is and who is affected by it.
(b) identify the root cause of the performance issue (and the ways to do that are many - it depends on the performance issue)
(c) address the root cause.


For example, if a single screen is "slow", discover the code behind the screen - and see if the time is spent in the database or in the application. Once you determine it is in the database - tune it. Tune the application approach to the database (the algorithms), the sql, whatever it is that is causing it to be slow.


There is no "ten step list to tuning"

you can either

a) write your own function to reverse a string and call it

b) use the undocumented (and hence, possibly unsafe to use) reverse function.

ops$tkyte%ORA11GR2> select reverse( 'abc' ), reverse( '123' ) from dual;

REV REV
--- ---
cba 321

the listener does NOT receive sql requests. the listener just redirects you to a database and gets out of the conversation.


If you just enable sql trace - we'll tell you EXACTLY how much time is spent in the database - we record that very very exactly. So, just trace it.

I don't care about typos, I make typo's.

I care when people speak like 12 year old children - using "u", "ur" and the like. It just makes you look "not smart". I've seen it on resumes, cover letters, emails, posting in forums like this. It is not very professional, it makes you sound "not smart". It is insulting to the person reading it. Nothing good comes from it.


I don't care if your are Indian, French, Spanish, Australian, Martian or whatever - if you cannot take the time to type out actual words - why should I bother answering you or even giving you a modicum of respect? You gave us none by using 'text speak'


By the way, you have a typo, it isn't 'dump', it is dumb - but that is OK - a typo is an accident. Text speak is on purpose.


Charankumar didn't use text speak - he may have had a typo or two, but it is clear that english is his second language and I was able to read over all of that. It is only those that have actually mastered english that can use text speak (think about that - those that are capable of putting out perfect prose are the ones that choose to speak like children on purpose???)

in the other schema:

create table my_table as select * from the_other_schema.their_table;


then you can drop the other schema's table, that is the way to do this.

In an interview, if you don't know the answer to something, I would just state it - say "In my prior workload, I never had a need to do that"

However, if you were going for a DBA job and the DBA job required experience (it was not an entry level job) and you couldn't answer that particular question "how to defragment a table", well, that would be something I would expect almost any DBA with a year or two of experience to answer - answers could be:

a) alter table t move;
b) dbms_redefinition for an online re-organization
c) data pump export/import IF the table has a long in it (otherwise I would not want to use it)
d) alter table t shrink space compact


As for your specific questions

(1) typically NOTHING unless you frequently full scan the table. If you access the table solely by index - a reorganization doesn't typically do much for you (there are cases when it does, but in general it does not).

If you frequently full scan the table AND the table has large sections of "white space" (empty space, space where rows once where but have been deleted), then a reorganization could be very beneficial as it will tend to remove all of this white space.


If you frequently do large index range scans (getting more than a handful of rows via an index) and there is large amounts of white space again (lowering the number of rows per block in the table AND increasing the number of table blocks we have to inspect because of that lower number), then a reorganization might be beneficial and might knock off a couple of table IO's

If you frequently do large index range scans and when you do the reorganization, you sort the table data by the index key (improving the clustering factor of that index), you can make a real impact as well (but remember - a table can be sorted only one way - if you 'fix' one range scan - you probably broke another!)

see
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1032431852141#433021700346025909


(2) I would not use a separate table like that, just alter table t move; would do it. Much safer, faster, easier.

(3) yes, see above :)

by CF I assume you mean clustering factor (why do people invent acronyms and then never define them?? Just to be purposely obtuse and confusing? Can you tell this is a huge pet peeve of mine, our industry is horrible when it comes to acronyms...)


You can have more than one index with a 'good' clustering factor. Consider a table:

create table emp( empno number primary key, hiredate date, ename varchar2(30), termination_date date );

empno is populated by emp_seq.nextval
hiredate is populated by sysdate
ename is a randomly arriving string - sometimes you hire someone with a name that starts with A, then M, then Z, then B, then X and so on...

In that table, you usually insert and update only - when you fire someone, their termination_date field is filled in (a flag delete)

It is a normal heap based table.

Given the above, you can sort of assume that the data on disk will be sort of sorted by EMPNO and also sorted by HIREDATE - since both are monotonically increasing. The clustering factor on the EMPNO column would be near the number of blocks in the table. The clustering factor on HIREDATE would probably be near the number of blocks as well. The clustering factor on ENAME and TERMINATION_DATE would likely be near the number of rows - since they are randomly arriving.



As for this comment:

,the one whose clustering factor is close to number of blocks is best to use >


that is a bit misleading. The best index to use is the one that can answer your question efficiently. Suppose the indexes are such that:

(b,c,d) has clustering factor of 1000
(b,e,f) has clustering factor of 2000
(c,g,i) has clustering factor of 1,000,000

If you have a predicate

where c = ? and g = ? and i = ?

the only index to consider would be the one on C,G,I - the other indexes would not make sense to retrieve from the table given that predicate.


The clustering factor is used to determine how many rows from a given table an index would be efficient for. The lower the clustering factor, the higher the number of rows we'll retrieve from the table - and vice versa. The indexes on b,c,d and b,e,f would not really be considered very much for this query since they do not help with the predicate and they cannot be used instead of the table to answer the question.

I don't know why someone would ask a plsql developer how to reorganize a table.

That would be like asking them to balance the books - totally irrelevant to the job description.

I'd actually prefer a developer that didn't even know tables could be reorganized - they wouldn't ask for it to be done "just in case it makes my code go faster"

The only way to guarantee your rows appear in a given sequence is to use an order by.

Other operations may give the appearance of sorting, but this is an illusion. For example, older versions of Oracle Database implemented group by using a sort. Which lead some people believe that group by => order by. So were surprised when we switched to group by hash, "breaking" the sort.

So the answer is: there isn't one!

Man this kind of crap really gets me riled up.

(I stress - this is directed at the interviewer and interview process, not at you).

I mean seriously...what a bunch of rubbish. When I went for my car license test I don't recall them saying: "Now then....lets assume you didn't have a steering wheel...what are your options for turning?"

I've seen this question on the internet and there's always answers like "use clustered tables/indexes" (for certain platforms), or recursive with etc etc...

For example, here's a "solution" that uses scalar queries:

SQL> with idx as
  2   ( select
  3       rownum r ,
  4       x ,
  5       ( select count(*) from t t_inner
  6         where t_inner.x <= t.x
  7       ) seq
  8     from t
  9   )
 10  select idx.* ,
 11   ( select x
 12     from  idx idx_inner
 13     where idx_inner.seq = idx.r
 14   ) sorted
 15  from idx;

         R          X        SEQ     SORTED
---------- ---------- ---------- ----------
         1         32          3          4
         2         96         10         19
         3         62          5         32
         4         39          4         39
         5         74          7         62
         6         19          2         67
         7          4          1         74
         8         95          9         76
         9         67          6         95
        10         76          8         96

But *all* such solutions are:

- ridiculous because they have no real world application

- wrong! Because no matter HOW you write an SQL, there is NEVER any means to completely predict the order WITHOUT an order by clause. This is a fundamental premise on how database are implemented - the *optimizer* and execution engines decide on how to access the data.

Be happy you didn't get the job. If the job is anything like the interview there's a lack of fundamental understanding how relational databases work.

There's no guarantee that the database will implement the union using a sort. This could change.