Today's musings

I seem to be developing interest to study for and give 2 of Oracle certifications since yesterday:

1.Oracle Database SQL Certified Expert
2. Oracle Database 11g: Performance Tuning

The latter one requires first as a prerequisite to be undergoing rigorous dba exams, which is a long shot at this time. Let us see.

"Certifications are, like most things in life: The more you put into them, the more you will get out." 

http://jaffardba.blogspot.com/2007/07/1z0-047-oracle-database-sql-expert.html

Today's attempts were to study PCTFREE and PCTUSED parameters, studying those.

The PCTFREE parameter is used to set the percentage of a block to be reserved for possible updates to rows that already are contained in that block.

So less chances of updates means keep PCTFREE low.

Multi-table INSERT

INSERT ALL|FIRST
   [WHEN condition THEN] INTO target [VALUES]
   [WHEN condition THEN] INTO target [VALUES]
   ...
   [ELSE] INTO target [VALUES]
SELECT ...
FROM   source_query;

ALL - each condition is checked and all matching conditions result in the corresponding INSERT

FIRST - as name suggests, stop at the first matching condition from top-bottom. Only do the FIRST insert that encountered a matching condition.  

List of all objects in Dictionary, comes with comments on what each can be used for 

select * from dictionary

Example:

USER_IND_EXPRESSIONS	Functional index expressions in user's indexes and indexes on user's tables
ALL_IND_EXPRESSIONS	FUNCTIONAL INDEX EXPRESSIONs on accessible TABLES
USER_JOIN_IND_COLUMNS	Join Index columns comprising the join conditions
ALL_JOIN_IND_COLUMNS	Join Index columns comprising the join conditions
USER_OBJECTS	Objects owned by the user
ALL_OBJECTS	Objects accessible to the user
USER_PROCEDURES	Description of the user functions/procedures/packages/types/triggers
ALL_PROCEDURES	Functions/procedures/packages/types/triggers available to the user 

V$ACTIVE_INSTANCES	Synonym for V_$ACTIVE_INSTANCES
V$ACTIVE_SESS_POOL_MTH	Synonym for V_$ACTIVE_SESS_POOL_MTH
V$ADVISOR_PROGRESS	Synonym for V_$ADVISOR_PROGRESS
V$AW_AGGREGATE_OP	Synonym for V_$AW_AGGREGATE_OP 

Whats more i just found the synonym V$ objects can be queried even if we dont have access to the V_$ views... is that true?? huh.. 

ROLLUP 

This clause is an extension to GROUP BY. It calculates sub-totals at different levels as specified in the rollup clause from right to left AFTER...

it has done the aggregation normal to GROUP BY. Also it calculates a grand total in the end.

Good for reporting kind of work i presume.. must see more to confirm.

http://psoug.org/reference/rollup.html 

Example:

SELECT o.customer_id, oi.product_id, SUM(oi.unit_price*oi.quantity) "Total" FROM order_items oi
JOIN orders o ON oi.order_id=o.order_id WHERE MONTHS_BETWEEN(order_date, SYSDATE) <= 6
GROUP BY ROLLUP (o.customer_id, oi.product_id) ; 

Yet to understand GROUP_ID and GROUPING on its related lines. 

Analyzing my existing model column domains:

  SELECT data_type,
         data_length,
         count_of_such,
         data_precision,
         data_scale
    FROM (  SELECT data_type,
                   data_length,
                   data_precision,
                   data_scale,
                   COUNT (*) count_of_such
              FROM cols
          GROUP BY data_type,
                   data_length,
                   data_precision,
                   data_scale)
ORDER BY data_type, count_of_such, data_length ASC;