partition outer join in oracle 10g
Partition outer join is a new mechanism in 10g to "invent" data to fill the gaps in non-contiguous results. In 10g there are many methods to deal with such a problem (including the awe-inspiring, but equally terrifying, MODEL clause). In older versions of Oracle, "data-densification" was not as simple and certainly less efficient than it has now become.
the problem
This article has been motivated by a response I gave to a problem raised on an Oracle developer forum. Our requirement is to produce a report that details customer spending for each month of the year. Our database only records actual spend, so for any given month, data for dormant or idle customers will have to be generated.
setup
First, we'll create a mock CUSTOMER_ORDERS table with sparse data to represent customer spending. To keep the example simple, we'll denormalise the customer name onto the orders table.
SQL> CREATE TABLE customer_orders (name, dt, amt)
2 AS
3 SELECT *
4 from (
5 SELECT owner
6 , TRUNC(created) + MOD(ROWNUM,6)
7 , TRUNC(object_id/ROWNUM)
8 from all_objects
9 WHERE created > TRUNC(SYSDATE,'YEAR')
10 AND owner IN ('ORDSYS','WKSYS')
11 ORDER BY
12 DBMS_RANDOM.RANDOM
13 &nbs
Ïà¹ØÎĵµ£º
ʲôÊǺϲ¢¶àÐÐ×Ö·û´®£¨Á¬½Ó×Ö·û´®£©ÄØ£¬ÀýÈ磺
SQL> desc test;
Name Type Nullable Default Comments
------- ------------ -------- ------- --------
COUNTRY VARCHAR2(20) Y &nb ......
---------------------------------------------------------------------------
---- ±¾ÎÄΪandkylee¸öÈËÔ´´£¬ÇëÔÚ×ðÖØ×÷ÕßÀͶ¯³É¹ûµÄÇ°ÌáϽøÐÐתÔØ£»
---- תÔØÎñ±Ø×¢Ã÷Ôʼ³ö´¦
:
http://blog.csdn.net/andkylee
--- 2010-05-31 22:56:43
---- ¹Ø¼ü×Ö£º oracle ·ÖÒ³ rownum
--------------------------------- ......
¼Ç¼һÏÂÒÔ±¸Ï´οìËÙÕÒµ½¡£¡£¡£
Íùtb_wf_privgrant±íÖвåÈëÒ»Ìõ¼Ç¼£¬workflow_id×Ö¶ÎÖµ´Ótb_wf_workflow ±íÖлñÈ¡workflow_name='֪ʶÉóºË'µÄËùÓмǼÖÐworkflow_id×î´óÖµ¡£
--oracle
declare a NUMBER(10);
begin
select max(wo ......
²é¿´Êý¾Ý»µ¿éËùÔÚÊý¾ÝÎļþºÅ¼°¿éºÅ¿ÉÒÔ¶Ô±í½øÐÐÒ»´ÎÈ«±íɨÃ裬È磺
¡¡¡¡select count(*) from tablename;
¡¡¡¡1¡¢Ã»Óб¸·ÝµÄÇé¿öÏ£º
¡¡¡¡1.1¡¢Ê¹ÓÃexp/imp»Ö¸´
¡¡¡¡ÔÚÕâÖÖÇé¿öÏ¿϶¨»áÔì³ÉÊý¾ÝµÄ¶ªÊ§£¬ÔÚÕâÖÖÇé¿öÏÂÓ¦²ÉÈ¡½«Êý¾Ýµ¼³öÈ»ºóÖؽ¨±íÔÙ½øÐе¼ÈëµÄ·½·¨£¬À´¾¡Á¿»Ö¸´Ëð»µÊý¾Ý¿éÖеÄÊý¾Ý£¬µ«ÊÇÔÚÓлµ¿éµÄÇé¿öÏÂ
......
Shrink spaceºÏ²¢±íµÄËéƬ
Ò»°ã±íÀïÓÐËéƬÎÒÃǶ¼²ÉÓÃalter table table_name move tablespace_name,»òÕßexp,drop table table_name,impµÄ2ÖÖ·½Ê½10G¸øÎÒÃÇÆäËûµÄ·½·¨.ÏÂÃæÎÒÀ´ÊÔÒ»°É
ÓÃShrink SpaceÊÕËõOracleÊý¾Ý¶Î
ÔÚoracleÖпÉÒÔʹÓÃalter table table_name shrink spaceÊÕËõ±í,ʹÓÃshrinkÓÐÁ½¸öÇ°ÌáÌõ¼þ:
1¡ ......