Ò׽ؽØͼÈí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö160KB

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


Ïà¹ØÎĵµ£º

Oracle¶àÐмǼºÏ²¢/Á¬½Ó/¾ÛºÏ×Ö·û´®µÄ¼¸ÖÖ·½·¨

ʲôÊǺϲ¢¶àÐÐ×Ö·û´®£¨Á¬½Ó×Ö·û´®£©ÄØ£¬ÀýÈ磺
SQL> desc test;
Name     Type          Nullable Default Comments
------- ------------ -------- ------- --------
COUNTRY VARCHAR2(20) Y       &nb ......

OracleÖÐʵÏÖ·ÖÒ³µÄ·½·¨

---------------------------------------------------------------------------
---- ±¾ÎÄΪandkylee¸öÈËÔ­´´£¬ÇëÔÚ×ðÖØ×÷ÕßÀͶ¯³É¹ûµÄÇ°ÌáϽøÐÐתÔØ£»
---- תÔØÎñ±Ø×¢Ã÷ԭʼ³ö´¦
:
http://blog.csdn.net/andkylee
--- 2010-05-31 22:56:43
---- ¹Ø¼ü×Ö£º oracle ·ÖÒ³ rownum
--------------------------------- ......

oracleÓësqlserver²åÈëÊý¾Ý¶¯Ì¬×Ö¶ÎÖµ

¼Ç¼һÏÂÒÔ±¸Ï´οìËÙÕÒµ½¡£¡£¡£
       Íùtb_wf_privgrant±íÖвåÈëÒ»Ìõ¼Ç¼£¬workflow_id×Ö¶ÎÖµ´Ótb_wf_workflow ±íÖлñÈ¡workflow_name='֪ʶÉóºË'µÄËùÓмǼÖÐworkflow_id×î´óÖµ¡£
--oracle
declare   a NUMBER(10);
   begin
   select  max(wo ......

OracleÊý¾Ý¿éË𻵻ָ´×ܽá(תÌû)

²é¿´Êý¾Ý»µ¿éËùÔÚÊý¾ÝÎļþºÅ¼°¿éºÅ¿ÉÒÔ¶Ô±í½øÐÐÒ»´ÎÈ«±íɨÃ裬È磺
¡¡¡¡select count(*) from tablename;
¡¡¡¡1¡¢Ã»Óб¸·ÝµÄÇé¿öÏ£º
¡¡¡¡1.1¡¢Ê¹ÓÃexp/imp»Ö¸´
¡¡¡¡ÔÚÕâÖÖÇé¿öÏ¿϶¨»áÔì³ÉÊý¾ÝµÄ¶ªÊ§£¬ÔÚÕâÖÖÇé¿öÏÂÓ¦²ÉÈ¡½«Êý¾Ýµ¼³öÈ»ºóÖؽ¨±íÔÙ½øÐе¼ÈëµÄ·½·¨£¬À´¾¡Á¿»Ö¸´Ëð»µÊý¾Ý¿éÖеÄÊý¾Ý£¬µ«ÊÇÔÚÓлµ¿éµÄÇé¿öÏÂ
......

oracleºÏ²¢±íµÄËéƬ

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¡ ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØͼ | ¸ÓICP±¸09004571ºÅ