Ò׽ؽØÍ¼Èí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö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º¯Êý´óÈ«


SQLÖеĵ¥¼Ç¼º¯Êý
1.ASCII
·µ»ØÓëÖ¸¶¨µÄ×Ö·û¶ÔÓ¦µÄÊ®½øÖÆÊý;
SQL> select ascii('A') A,ascii('a') a,ascii('0') zero,ascii(' ') space from dual;
        A         A      ZERO   &nbs ......

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¡ ......

oracle ´æ´¢¹ý³Ì ½»¼¯

create or replace procedure TestJiaoJi
  is
   type nt_table_type is table of number;
   nt1 nt_table_type:=nt_table_type(0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0);
   nt2 nt_table_type:=nt_table_type(0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0 ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ