sql for Days of Month

=============================
=============================
sql for Days of year
=============================
=============================

SELECT TRUNC(SYSDATE,'Y')+ROWNUM-1 THEDATE ,To_char(SYSDATE,'mm')
FROM ( SELECT 1 just_a_column
FROM dual
CONNECT BY LEVEL <= 366
)
where rownum <= add_months(trunc(:p11,'MM'),12) - trunc(:p11,'MM')


SELECT TRUNC(SYSDATE,'Y')+ROWNUM-1 THEDATE
FROM ( SELECT 1 just_a_column
FROM dual
CONNECT BY LEVEL <= 366
)
WHERE ROWNUM <= ADD_MONTHS(TRUNC(SYSDATE,'Y'),12)-TRUNC(SYSDATE,'Y')



select trunc(:p11,'MM')-1+rownum Date_month
from all_objects
where rownum <= add_months(trunc(:p11,'MM'),12) - trunc(:p11,'MM')


=================================
=================================
sql for Days of Month
=================================
=================================

SELECT TRUNC(SYSDATE,'Y')+ROWNUM-1 THEDATE ,To_char(SYSDATE,'mm')
FROM ( SELECT 1 just_a_column
FROM dual
CONNECT BY LEVEL <= 366
)
where rownum <= add_months(trunc(:p11,'MM'),1) - trunc(:p11,'MM')



select trunc(:p11,'MM')-1+rownum Date_month
from all_objects
where rownum <= add_months(trunc(:p11,'MM'),1) - trunc(:p11,'MM')

SQL or PL/SQL techniques to generate rows.

=======================================================
=======================================================
SQL or PL/SQL techniques to generate rows.
=======================================================
=======================================================

1.

SELECT ROWNUM
FROM ( SELECT 1 FROM DUAL GROUP BY CUBE(1,2,3,4,5,6,7,8,9,10) )
WHERE ROWNUM <= &N

2.

SELECT *
FROM ( SELECT ROWNUM FROM DUAL CONNECT BY LEVEL <= &N )

3.

SELECT ROWNUM FROM DUAL CONNECT BY LEVEL <= &N;

4.

create or replace type row_table as table of number;


Create or replace function gen_row (num_rows in number)
return row_table
parallel_enable pipelined is
begin
for x in 1..num_rows loop
pipe row (x);
end loop;
return;
end;


select * from table(gen_row(&N));

5.

select to_number(column_value) colval
from xmltable('for $i in 1 to &n return $i');

6.

select integer_value
from dual
where 1=2
model
dimension by ( 0 as key )
measures ( 0 as integer_value )
rules upsert ( integer_value[ for key from 1 to 9 increment 1 ] = cv(key) );

7.

select rownum from all_objects where rownum<=5;


8.

select val
from (select 1 rn from dual)
model
dimension by (rn)
measures (1 val)
rules
iterate (&N)
( val[iteration_number] = iteration_number+1 )
order by val


[note: this is not mine]

Composite Partitioned Table in oracle 10g

====================================================
Composite Partitioned Table - By Range And hash
====================================================

CREATE TABLE composite_rng_hash (
cust_id NUMBER(10),
cust_name VARCHAR2(25),
amount_sold NUMBER(10,2),
time_id DATE)
PARTITION BY RANGE(time_id)
SUBPARTITION BY HASH(cust_id)
SUBPARTITION TEMPLATE(
SUBPARTITION sp1 TABLESPACE users,
SUBPARTITION sp2 TABLESPACE users,
SUBPARTITION sp3 TABLESPACE users,
SUBPARTITION sp4 TABLESPACE users) (
PARTITION sales_pre05
VALUES LESS THAN(TO_DATE('01/01/2007','DD/MM/YYYY')),
PARTITION sales_2007
VALUES LESS THAN(TO_DATE('01/01/2008','DD/MM/YYYY')),
PARTITION sales_2008
VALUES LESS THAN(TO_DATE('01/01/2009','DD/MM/YYYY')),
PARTITION sales_future
VALUES LESS THAN(MAXVALUE));

set linesize 121
col table_name format a20

SELECT table_name, partitioned, secondary
FROM user_tables;

desc user_tab_partitions


SELECT table_name, partition_name, composite, subpartition_count SPC, high_value
FROM user_tab_partitions;

desc user_tab_subpartitions


SELECT table_name, partition_name, subpartition_name, subpartition_position
FROM user_tab_subpartitions;

desc user_subpartition_templates


SELECT subpartition_name, tablespace_name, high_bound
FROM user_subpartition_templates;



GRANT select ON emp TO uwclass;
GRANT select on dept TO uwclass;

INSERT INTO composite_rng_hash
SELECT c.deptno, c.dname || ' ' || c.dname,
s.sal, s.hiredate
FROM emp s, dept c
WHERE s.deptno = c.deptno
AND rownum < 250001; commit; exec dbms_stats.gather_table_stats(USER, 'COMPOSITE_RNG_HASH', 'SALES_PRE05'); exec dbms_stats.gather_table_stats(USER, 'COMPOSITE_RNG_HASH', 'SALES_2005'); exec dbms_stats.gather_table_stats(USER, 'COMPOSITE_RNG_HASH', 'SALES_2006'); exec dbms_stats.gather_table_stats(USER, 'COMPOSITE_RNG_HASH', 'SALES_2007'); exec dbms_stats.gather_table_stats(USER, 'COMPOSITE_RNG_HASH', 'SALES_2008'); exec dbms_stats.gather_table_stats(USER, 'COMPOSITE_RNG_HASH', 'SALES_FUTURE'); SELECT table_name, partition_name, num_rows FROM user_tab_partitions; SELECT table_name, partition_name, subpartition_name, num_rows FROM user_tab_subpartitions; exec dbms_stats.gather_table_stats(USER, 'COMPOSITE_RNG_HASH', GRANULARITY=>'ALL');

SELECT table_name, partition_name, subpartition_name, num_rows
FROM user_tab_subpartitions;

set long 1000000
select dbms_metadata.get_ddl('TABLE', 'COMPOSITE_RNG_HASH');



====================================================
Composite Partitioned Table - By Range And List
====================================================

CREATE TABLE composite_rng_list (
cust_id NUMBER(10),
cust_name VARCHAR2(25),
cust_state VARCHAR2(2),
time_id DATE)
PARTITION BY RANGE(time_id)
SUBPARTITION BY LIST (cust_state)
SUBPARTITION TEMPLATE(
SUBPARTITION west VALUES ('OR', 'WA') TABLESPACE users,
SUBPARTITION east VALUES ('NY', 'CT') TABLESPACE users,
SUBPARTITION cent VALUES ('OK', 'TX') TABLESPACE users) (
PARTITION per1 VALUES LESS THAN (TO_DATE('01/01/2000','DD/MM/YYYY')),
PARTITION per2 VALUES LESS THAN (TO_DATE('01/01/2005','DD/MM/YYYY')),
PARTITION per3 VALUES LESS THAN (TO_DATE('01/01/2010','DD/MM/YYYY')),
PARTITION future VALUES LESS THAN(MAXVALUE));

desc composite_rng_list

SELECT table_name, partition_name, composite, high_value
FROM user_tab_partitions
where table_name='COMPOSITE_RNG_LIST';


SELECT table_name, partition_name, subpartition_name, num_rows
FROM user_tab_subpartitions
where table_name='COMPOSITE_RNG_HASH';