Continuation of my notes to review before taking the 1Z0-047 certification. Rollup / Cube / Grouping Sets grouping(column_name) = 1 indicates a superaggregate row Data Dictionary Adding Comments: Comments can be added to tables, columns, indextype, etc.: comment on table ships is ‘Listing of all ships’; comment on column ships.capacity is ‘Maximum number of passengers’;… Read More »
Continuation of my notes to review before taking the 1Z0-047 certification. Sequences Rules: CURRVAL can’t be invoked in a session before NEXTVAL has been called in the same session If NEXTVAL used in statement, counter incremented even if statement doesn’t complete successfully CURRVAL / NEXTVAL can’t be used as column default CURRVAL / NEXTVAL can’t… Read More »
Continuation of my notes to review before taking the 1Z0-047 certification. Aggregate Functions Can only be nested 2 levels deep: select avg(sum(count(x))) from table will throw an exception The GROUP BY and HAVING clauses can appear in either order: select mod(rownum,10), count(*) from dual connect by level
Nothing groundbreaking here… Just some notes of some things I don’t want to forget when taking the exam. There will most likely be a few more posts like this one in the near future as I continue to study. Object Naming – After first character, can contain letters, numbers, $, _, and # without being… Read More »
I was happy to read Iggy Fernandez announce that the second installment of the NoCOUG SQL Challenge is coming soon. I was very impressed with the quality of answers that were submitted for the first challenge last year, especially the winners answers which made my answer appear extremly elementary. 🙂
I have decided to try to get some Oracle certifications, starting with 1Z0-047 (SQL Expert). My posts for a while will probably just be some notes of things that I find while studying that I didn’t know off the top of my head. If you have taken this exam and have any advice I would… Read More »
Continuing on my last post, I was curious to how the stopkey optimization is affected when using a between range instead of just an upper bound. This would be similar to queries that have pagination (e.g. page 1 is rows 1-100, page 2 is 101-200, etc.) Note that typically 100 rows per page is about… Read More »
I have been seeing a lot on stopkey optimization lately. First, at the Michigan OakTable Symposium, I attended Randolf Geist’s presentation on FIRST_ROWS_N. It was a very good presentation with great examples that got me wanting to test some other cases to see the results. Second, Jonathan Lewis posted about a stopkey limitation with updates.… Read More »
I was looking at an execution plan the other day and saw in the predicate section that my query had been rewritten to use the function LNNVL. This function was new to me, so a quick look in the documentation provides some more information. Basically, it will convert TRUE expression to FALSE and both FALSE… Read More »
Every now and again I will need to create multiple rows of dummy data for whatever reason. The following will generate 10 rows that contain the rownumber and a random 10 character lowercase string: select rownum, dbms_random.string(‘l’,10) rand_string from dual connect by level <= 10; Output: ROWNUM RAND_STRING 1 ppwuuixwuj 2 icvidoofrl 3 … Read More »