Useful SQL Info - Part-2

1. If we want to generate random numbers, random strings from Oracle (let say for the sake of testing etc...) then use DBMS_RANDOM package.

select dbms_random.value , dbms_random.value (1,100), trunc(dbms_random.value (1,100)), dbms_random.string ('A',4), dbms_random.string ('U',4), dbms_random.string ('L',4),

 dbms_random.string ('X',4), sysdate + trunc(dbms_random.value (1,100))

from dual

connect by level <=100

2. Materialize hint

The Oracle materialize hint is used to ensure that the Oracle cost-based optimizer materializes the temporary tables that are created inside the "WITH" clause.

MATERIALIZE hint in with clause converts result set into global temporary table

WITH data AS (

  SELECT /*+ MATERIALIZE */ level AS id

  FROM   dual

  CONNECT BY level <= 10000

)

SELECT rownum AS id

FROM   data, data, data

WHERE  rownum <= 1000000;

3.Dynamic Sampling & gather_plan_statistics Hint

CREATE TABLE CUST

(

  CUST_ID     NUMBER,

  LAST_NAME   VARCHAR2(30 BYTE),

  FIRST_NAME  VARCHAR2(30 BYTE)

)

Insert into CUST

   (CUST_ID, LAST_NAME, FIRST_NAME)

 Values

   (1028, 'RDRIZTFLNZWA', 'UPGGKGGDYF');

Insert into CUST

   (CUST_ID, LAST_NAME, FIRST_NAME)

 Values

   (1029, 'DQDQNU', 'TZSHGTTKUOV');

Insert into CUST

   (CUST_ID, LAST_NAME, FIRST_NAME)

 Values

   (1030, 'JZSOW', 'BMU');

commit;

If we do the explain plan for this below SQL

select * from cust

We notice below NOTE.

As stats were not gathered on CUST table, default dynamic sampling level=2 to used to calculate the stats on the fly..

Note

-----

   - dynamic sampling used for this statement (level=2)

   - Warning: basic plan statistics not available. These are only collected when:

       * hint 'gather_plan_statistics' is used for the statement or

       * parameter 'statistics_level' is set to 'ALL', at session or system level

Execute below to get rid of the above warning

First, gather table stats using dbms_stats package

Second,  use below commands to see expected & actual cardinality values.

alter session set statistics_level = all

select /*+ gather_plan_statistics  */  * from cust

4. Dynamic Sampling levels