Ò׽ؽØÍ¼Èí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö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 10g em¡¢isqlplus·ÃÎʲ»Á˵ÄÎÊÌâ½â¾ö

Èç¹ûweb·½Ê½µÄem£¬isqlplus·ÃÎʲ»ÁË¡£
1. ¼ì²éÖ÷»úÃû/IP¡¢¶Ë¿ÚÊÇ·ñÕýÈ·
°²×°Ê±µÄÖ÷»úÃû/IP¡¢¶Ë¿Ú¼Ç¼ÔÚ$ORACLE_HOME/install/portlist.ini ÎļþÖС£
ȱʡÊÇ£º
Ò»°ãÓû§                    
http://ip:5560/isq ......

oracleÃüÁî²Ù×÷

‘¿ªÊ¼’-->‘ÔËÐÐ’--> ÊäÈë‘cmd’ ´ò¿ªcmd.exe -->ÊäÈë‘cd c:\’ Çл»µ½ c:\>
Æô¶¯Oracle
c:\> svrmgrl
svrmgrl> connect   internal/oracle
svrmgrl> startup
svrmgrl> exit
Æô¶¯¼àÌýÆ÷
c:\> lsnrctl   start ......

oracle_°Ù·Ö±È¼ÆËã

select round(sum(case when o.close_date is null then 1 else 0 end)/count(*),3)*100||'%'  SCALE from order o
˵Ã÷£ºÊ¹ÓÃsum¶ÔÒ»¶¨Ìõ¼þµÄÊý¾ÝÇóºÍºó£¬Óë×ÜÊý½øÐбȽϡ£
         ||'%' ÎªÒԵİٷֺÅÐÎʽÊä³ö½á¹û(Àý£º2%) ......

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ºÅ