תÔØoracle rollupºÍcubeº¯ÊýʹÓÃÐĵÃ
ת×Ô£ºhttp://blog.csdn.net/wangdongzjk/archive/2005/11/18/532424.aspx
ʹØCUBE ROLLUP GROUPING SETS£¨1£©
ÔÎÄÒý×Ô£º
¾ÛºÏÊÇÊý¾Ý²Ö¿âµÄ»ù´¡¡£ÎªÁËÌá¸ß¾ÛºÏµÄÐÔÄÜ¡£OracleÌṩÁËGroup By Ìõ¿îµÄÀ©Õ¹¡£
1£® CUBE, ROLLUPÀ©Õ¹
2£® 3¸ögroupingº¯Êý
3£® Grouping setÀ©Õ¹
CUBE ROLLUP GROUPING SETS¶ÔSQLµÄÀ©Õ¹Ê¹µÃ²éѯºÍ±¨¸æ¶¼±äµÃ¼òµ¥ºÍѸËÙ¡£Rollup¼ÆËãÖîÈçsum count max min avgµÄº¯Êý£¬Ôö¼ÓÁ˾ۺϵļ¶±ð.CUBEÊÇÒ»¸öÀàËÆROLLUPµÄÀ©Õ¹£¬Ê¹µÃ¿ÉÒÔÓÃÒ»¸öÓï¾ä¼ÆËãËùÓпÉÄܵľۺϡ£CUBE¿ÉÒÔͨ¹ýµ¥ÌõÉú³ÉCross-tabulation£¨½»²æ±¨±í£©±¨¸æµÄÐÅÏ¢¡£CUBE£¬ROLLUP£¬GROUPING SETSÀ©Õ¹ÁîÄã¿ÉÒÔ¾«È·µÄ¶ÔÄã¸ÐÐËȤµÄgroup by Ìõ¿î½øÐÐgrouping¡£²»ÔËÐÐcube²Ù×÷Ò²¿ÉÒÔ¸ßЧµÄ´Ó¶à¸öά¶È½øÐзÖÎö¡£¼ÆËãÒ»¸öfull cube½«»á´øÀ´ºÜ¸ßµÄ¸ºÔØ£¬ËùÒÔ½«cubeÌ滻Ϊgrouping sets¿ÉÒÔÃ÷ÏÔµØÌá¸ßÐÔÄÜ¡£CUBE ROLLUP GROUPING SETS¿ÉÒÔÉú³Éµ¥¸ö½á¹û¼¯£¬µÈЧÓÚUNION ALL¡£ÎªÁËÌá¸ßÐÔÄÜ£¬CUBE, ROLLUP, and GROUPING SETS¿ÉÒÔ²¢ÐнøÐУº¶à½ø³ÌͬʱÔËËãËùÓеÄÓï¾ä¡£ÕâÖÖ¹¦ÄÜʹµÃ¾ÛºÏ¼ÆËã¸ü¼Ó¸ßЧ£¬Òò´ËÌá¸ßÁËÊý¾Ý¿âÐÔÄܺͿɲâÐÔ¡£
Analyzing Across Multiple Dimensions
¶àά·ÖÎö
ÔÚDSSϵͳÖÐÒ»¸öºÜÖØÒªµÄ¸ÅÄîÊǶàά·ÖÎö£º
´Ó¶à¸ö½Ç¶È¶ÔÆóÒµµÄ×´¿ö½øÐв鿱¡£Demension£¬ÖÚ¶àµÄάһ°ãÊÇʱ¼ä£¬¿Õ¼ä£¬²úÆ·£¬²¿ÃÅ£¬ÇþµÀ£¬ÆóÒµµÄά¶È¶àÖÖ¶àÑù¡£Ê¼þºÍʵÌå¹ØÁªÌض¨µÄά¶È¾ÍÊÇËùνµÄfacts.ÕâЩËùνµÄfacts¿ÉÒÔÊÇÏúÊÛ£¬ÏÖ½ð£¬ÀûÈ󣬿ͻ§ÕË»§£¬»òÊÇÆäËûһЩֵµÃ¼Ç¼µÄ¶«¶«¡£
ÏÂÃæÊÇһЩ¶àάÐèÇóµÄÀý×Ó
1£® չʾ1999ºÍ2000Ä꣬ËùÓвúÆ·µÄ×ÜÏúÁ¿£¬¾ÛºÏ¼¶±ðΪµØÓòά¶È£¬´ÓÖݵ½¹ú¼Òµ½µØÇø¡£
2£® ´´½¨½»²æ±¨±í·ÖÎö1999ºÍ2000ÄêÄÏÃÀÖÞ·¶Î§µÄ·ÑÓ㬰üÀ¨ËùÓеÄС¼Ç
ËùÓеÄÐèÇ󶼰üº¬Á˶àά¡£ºÜ¶à¶àάµÄÎÊÌâÐèÒªÊý¾ÝµÄ¾ÛºÏ£¬Êý¾Ý¼¯µÄ±È½Ï£¬Í¨³£¿çԽʱ¼ä£¬µØÓò¡£
Optimized Performance
²»½ö½öÊǶàάµÄÎÊÌ⣬»¹ÓÐËùÓÐÀàÐ͵Ĵ¦Àí¿ÉÒÔ´ÓÔöÇ¿¾ÛºÏ¹¦ÄÜÖеõ½ºÃ´¦¡£ÊÂÎñµÄ´¦Àí£¬½ðÈÚÖÆÔìϵͳ-ËùÓеÄÕâЩ¶¼²úÉú´óÁ¿µÄÉú²ú±¨¸æÐèÒª´óÁ¿µÄϵͳ×ÊÔ´¡£´´½¨ÕâЩ±¨¸æ¼õÉÙϵͳ¸ºÔØ£¬ÌáÉýÁËϵÍ
Ïà¹ØÎĵµ£º
ORACLEѧϰÖгöÏÖµÄÎÊÌ⣨³ÖÐø¸üÐÂÖУ©
1¡¢ ORA:12545 ÒòÄ¿±êÖ÷»ú»ò¶ÔÏó²»´æÔÚ£¬Á¬½Óʧ°Ü
ÍøÉϲο¼µÄ½â¾ö°ì·¨
1.ËÑË÷ÄãµÄoracle°²×°Ä¿Â¼£¬ÕÒµ½ÕâÁ©¸öÎļþ tnsnames.ora ºÍ listener.ora ¼Çס¾Í¸ÄÕâÁ©¸öÎļþ¾ÍÐÐÁË£¬»¹ÓÐÒ»¸ö´óдµÄTNSNAMES.ORA²»ÓùÜËü¡£ËûÃǵÄ·¾¶ÈçÏ£ºÈç E:\oracle\ora92\n ......
²ÎÊý
UNDO_MANAGEMENT = AUTO --¹ÜÀíģʽ,¿ÉΪAUTO»òMANUAL.Ö»ÄÜÔÚÆôʼ²ÎÊýÎļþÀïÃæÐÞ¸Ä
UNDO_TABLESPACE = undo --Öƶ¨´æ´¢»¹ÔÊý¾ÝµÄ±í¿Õ¼ä,Òà¿ÉÓÃALTER SYSTEM SET undo_tablespace = 'abc'À´¸ ......
ÒòΪÏîĿijЩģ¿éµÄÊý¾Ý½á¹¹Éè¼ÆûÓÐÑϸñ°´ÕÕij¹æ·¶Éè¼Æ£¬ËùÒÔÖ»ÄÜ´ÓÊý¾Ý¿âÖвéѯÊý¾Ý½á¹¹£¬ÐèÒª²éѯµÄÐÅÏ¢ÈçÏ£º×Ö¶ÎÃû³Æ¡¢Êý¾ÝÀàÐÍ¡¢ÊÇ·ñΪ¿Õ¡¢Ä¬ÈÏÖµ¡¢Ö÷¼ü¡¢Íâ¼üµÈµÈ¡£
ÔÚÍøÉÏËÑË÷Á˲éѯÉÏÊöÐÅÏ¢µÄ·½·¨£¬×ܽáÈçÏ£º
Ò»£¬²éѯ±í»ù±¾ÐÅÏ¢
select
utc.column_name,utc.data_type,utc.data_le ......
About parent vs child latches. There is no fundamental low level difference between parent and child latches, they are all small regions of memory modified with atomic test-and-set style opcodes.
You see parent (and solitary) latches from x$ksll ......
¡¡1¡¢Ä¿µÄ£º
¡¡¡¡OracleϵͳÖеÄprofile¿ÉÒÔÓÃÒÔ¶ÔÓû§ËùÄÜÊ©ÓõÄÊý¾Ý¿â×ÊÔ´½øÕ¹ÏÞÖÆ£¬ÔËÓÃCreate ProfileÏÂÁî´´°ìÒ»¸öProfile£¬ÓÃËüÀ´¶ÒÏÖ¶ÔÊý¾Ý¿â×ÊÔ´µÄÏÞÖÆÔËÓã¬Èç¹û°Ñ¸Ãprofile·ÖÅä¸øÓû§£¬Ôò¸ÃÓû§ËùÄÜÊ©ÓõÄÊý¾Ý¿â×ÊÔ´¶¼ÔÚ¸ÃprofileµÄÏÞÖÆÒÔÄÚ¡£
¡¡¡¡2¡¢Ìõ¼þ£º
¡¡¡¡´´ÉèprofileÎñ±ØÒªÓÐCREATE PROFILEµÄÏµÍ ......