/*
lvl1 lvl2 lvl3 lvl4 lvl
4 3 4 1
3 2 2 1
2 2 3 4
4 4 3 4
3 1 2 2
Ôõôд´úÂë È¥±È½Ïlvl1¡¢lvl2¡¢lvl3¡¢lvl4 ¶ÔӦÿÐеÄÖµ£¬È¡ÆäÖÐ×îСµÄ£¬½«ÆäÖµÌí¼Óµ½lvlÁÐÀï
ÔËÐнá¹ûÓ¦¸ÃÊÇ
lvl
1
1
2
3
1
*/
--·½·¨(Ò») º¯數·¨
-->Title:Generating test data
-->Author:wufeng4552
-->Date :2009-10-16 09:58:16
if not object_id('Tempdb..#t') is null
drop table #t
Go
Create table #t([lv ......
---- ÈËÃÇÔÚʹÓÃSQLʱÍùÍù»áÏÝÈëÒ»¸öÎóÇø£¬¼´Ì«¹Ø×¢ÓÚËùµÃµÄ½á¹ûÊÇ·ñÕýÈ·£¬¶øºöÂÔ
Á˲»Í¬µÄʵÏÖ·½·¨Ö®¼ä¿ÉÄÜ´æÔÚµÄÐÔÄܲîÒ죬ÕâÖÖÐÔÄܲîÒìÔÚ´óÐ͵ĻòÊǸ´ÔÓµÄÊý¾Ý¿â
»·¾³ÖУ¨ÈçÁª»úÊÂÎñ´¦ÀíOLTP»ò¾ö²ßÖ§³ÖϵͳDSS£©ÖбíÏÖµÃÓÈΪÃ÷ÏÔ¡£±ÊÕßÔÚ¹¤×÷ʵ¼ù
Öз¢ÏÖ£¬²»Á¼µÄSQLÍùÍùÀ´×ÔÓÚ²»Ç¡µ±µÄË÷ÒýÉè¼Æ¡¢²»³ä·ÝµÄÁ¬½ÓÌõ¼þºÍ²»¿ÉÓÅ»¯µÄwhe
re×Ӿ䡣ÔÚ¶ÔËüÃǽøÐÐÊʵ±µÄÓÅ»¯ºó£¬ÆäÔËÐÐËÙ¶ÈÓÐÁËÃ÷ÏÔµØÌá¸ß£¡ÏÂÃæÎÒ½«´ÓÕâÈý¸ö
·½Ãæ·Ö±ð½øÐÐ×ܽ᣺
---- ΪÁ˸üÖ±¹ÛµØ˵Ã÷ÎÊÌ⣬ËùÓÐʵÀýÖеÄSQLÔËÐÐʱ¼ä¾ù¾¹ý²âÊÔ£¬²»³¬¹ý£±ÃëµÄ¾ù
±íʾΪ£¨< 1Ã룩¡£
---- ²âÊÔ»·¾³--
---- Ö÷»ú£ºHP LH II
---- Ö÷Ƶ£º330MHZ
---- Äڴ棺128Õ×
---- ²Ù×÷ϵͳ£ºOperserver5.0.4
----Êý¾Ý¿â£ºSybase11.0.3
Ò»¡¢²»ºÏÀíµÄË÷ÒýÉè¼Æ
----Àý£º±írecordÓÐ620000ÐУ¬ÊÔ¿´Ô ......
¼Ü¹¹
¼Ü¹¹ÈÃÓû§Äܹ»½«Êý¾Ý¿â¶ÔÏó¸ù¾ÝÆäÓÃ;½øÐзÖ×飬¿É½«¼Ü¹¹ÊÓΪÊý¾Ý¿âÖжÔÏóµÄÂß¼·Ö×é¡£µ÷Óüܹ¹ÖеĶÔÏóʱ£¬ÐèҪʹÓÃÖÁÉÙ°üº¬Á©²¿·ÖµÄÃû³Æ¡£Óû§±È½ÏÊìϤdbo¼Ü¹¹£¬¶ÔÓÚ¸ø¶¨Êý¾Ý¿â£¬ËüÊÇĬÈϵļܹ¹¡£×îºóµÄ·½·¨ÊÇʹÓÃÁ©²¿·ÖÃû³ÆÀ´ÒýÓÃÊý¾Ý¿â¶ÔÏó¡£
ͬÒå´Ê
ͬÒå´ÊÔÚÊý¾Ý¿â¶ÔÏóºÍ¿Í»§¶ËÖ®¼ä´´½¨Ò»¸ö³éÏó²ã£¬ÊµÖÊÉÏËüΪÊý¾Ý¿â¶ÔÏó´´½¨ÁíÒ»¸öÂß¼Ãû³Æ¡£ÕâÖÖ³éÏóʹµÃÁ¬½Ó·þÎñÆ÷ʱ·Ç³£·½±ã¡£¶ÔÓÚÁ´½Ó·þÎñÆ÷£¬ÐèҪʹÓÃÓÉ4²¿·Ö×é³ÉµÄÏÞ¶¨·û£¬ÀàËÆÈçÏ´úÂë¡£
Select column1,column2
from LinkedServerName.DatabaseName.SchemaName.TableName
Õâ¸ö³¤Ãû³Æ¸ø¿ª·¢ÈËÔ±´øÀ´ÁËÒ×ÓÃÐÔÎÊÌ⣬ËûÃÇÿÌìÔÚÊäÈë´óÁ¿ÕâÖÖ³¤¶ÈµÄ¶ÔÏóÃû³Æºó¿ÉÄÜ»áʹÊÖ³é½î¡£Í¨¹ýͬÒå´Ê£¬¿É´´½¨Öض¨Ïò·û£¬Ê¹µÃÈκÎʱºòÓÐÈËÊäÈëSchemaName.SynonymNameʱ½«×Ô¶¯Ó³Éäµ½LinkedServerName.DatabaseName.SchemaName.TableName¡£
×÷Ϊ³éÏó²ã£¬Í¬Òå´ÊÔÚÒª½«²éѯӳÉ䵽бí»ò·þÎñÆ÷ʱҲ·Ç³£ÓÐÓá£
×¢Ò⣺ͬÒå´Ê²»ÄÜÒýÓÃÁíÒ»¸öͬÒå´Ê¡£
¶¯Ì¬¹ÜÀíÊÓͼ
¶¯Ì¬¹ÜÀíÊÓͼ£¨DMV£©ºÍº¯Êý·µ»ØÓйØSQL ServerʵÀýºÍ²Ù×÷ϵͳµÄÐÅÏ¢¡£DMV¿ÉÌṩ¸÷ÖÖÀàÐ͵ÄÐÅÏ¢£¬°üÀ¨ÓйØI/O×ÓϵͳºÍRAMµÄÊý¾ÝÒÔ¼°ÓйØService BrokerµÄÐÅÏ¢¡£
Î ......
Using Oracle Index Hints in SQL statements
Hints are used to give specific information that we know about our data and application, to Oracle. This further improves the performance of our system. There can be instances where the default optimizer may not be efficient for a certain SQL statements. We can specify HINTS with the SQL statements, to improve the efficiency of those SQL statements.
In this article, we shall see how to specify INDEX hints and what the advantages of the same are.
1 How to specify Hints
The Hints are enclosed in comment, /* comment */, in an SQL statement, and can only be specified with SELECT, DELETE and UPDATE keyword.
SELECT /* comment */ ........ ;
All hints should start with a + sign. This tells the SQL Parser that the SQL has a hint specified with it.
SELECT /*+{hint} */ ........ ;
2 Using INDEX Hints
When you specify an INDEX Hint, the optimizer knows that it has to use the INDEX specif ......
Using Oracle Index Hints in SQL statements
Hints are used to give specific information that we know about our data and application, to Oracle. This further improves the performance of our system. There can be instances where the default optimizer may not be efficient for a certain SQL statements. We can specify HINTS with the SQL statements, to improve the efficiency of those SQL statements.
In this article, we shall see how to specify INDEX hints and what the advantages of the same are.
1 How to specify Hints
The Hints are enclosed in comment, /* comment */, in an SQL statement, and can only be specified with SELECT, DELETE and UPDATE keyword.
SELECT /* comment */ ........ ;
All hints should start with a + sign. This tells the SQL Parser that the SQL has a hint specified with it.
SELECT /*+{hint} */ ........ ;
2 Using INDEX Hints
When you specify an INDEX Hint, the optimizer knows that it has to use the INDEX specif ......
mysqlÔËÐÐsql½Å±¾Îļþ
3
ÍƼö
½¨Á¢my.sqlÎļþ:
create table mytable(name char(10),id char(4));
ÓÃrootÓû§µÇ½Êý¾Ý¿â:
E:\mysql\bin>mysql -u root -p password
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 26
Server version: 5.0.37-community-nt MySQL Community Edition (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
¸ü¸ÄÊý¾Ý¿â:
mysql> use mysql;
Database changed
µÚÒ»ÖÖ·½·¨£ºÔËÐиýű¾Îļþ:
ÃüÁîÔÚmysqlÃüÁîÌáʾ·ûÏÂÓã¬ÀýÈ磺mysql> source my.sql;(ÕâÀïmy.sqlÒª·ÅÔÚdataĿ¼ÏÂ)
Query OK, 0 rows affected (0.08 sec)
µÚ¶þÖÖ·½·¨£ºÔËÐиýű¾Îļþ:
ÃüÁîÔÚdosÃüÁîÌáʾ·ûÏÂÓã¬ÀýÈ磺c:\documents and settings\administrator>mysql -uroot -p password<my.sql
²é¿´hi±íµÄ½á¹¹:
mysql> desc mytable;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name&nbs ......
mysqlÔËÐÐsql½Å±¾Îļþ
3
ÍƼö
½¨Á¢my.sqlÎļþ:
create table mytable(name char(10),id char(4));
ÓÃrootÓû§µÇ½Êý¾Ý¿â:
E:\mysql\bin>mysql -u root -p password
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 26
Server version: 5.0.37-community-nt MySQL Community Edition (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
¸ü¸ÄÊý¾Ý¿â:
mysql> use mysql;
Database changed
µÚÒ»ÖÖ·½·¨£ºÔËÐиýű¾Îļþ:
ÃüÁîÔÚmysqlÃüÁîÌáʾ·ûÏÂÓã¬ÀýÈ磺mysql> source my.sql;(ÕâÀïmy.sqlÒª·ÅÔÚdataĿ¼ÏÂ)
Query OK, 0 rows affected (0.08 sec)
µÚ¶þÖÖ·½·¨£ºÔËÐиýű¾Îļþ:
ÃüÁîÔÚdosÃüÁîÌáʾ·ûÏÂÓã¬ÀýÈ磺c:\documents and settings\administrator>mysql -uroot -p password<my.sql
²é¿´hi±íµÄ½á¹¹:
mysql> desc mytable;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name&nbs ......
˵Ã÷£º¸´ÖƱí(Ö»¸´Öƽṹ,Ô´±íÃû£ºa бíÃû£ºb)
¡¡¡¡SQL: select * into b from a where 1<>1 ¡¡¡¡
¡¡¡¡ËµÃ÷£º¿½±´±í(¿½±´Êý¾Ý,Ô´±íÃû£ºa Ä¿±ê±íÃû£ºb)
¡¡¡¡SQL: insert into b(a, b, c) select d,e,f from b; ¡¡¡¡
¡¡¡¡ËµÃ÷£ºÏÔʾÎÄÕ¡¢Ìá½»È˺Í×îºó»Ø¸´Ê±¼ä
¡¡¡¡SQL: select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table
where table.title=a.title) b ¡¡
¡¡¡¡ËµÃ÷£ºÍâÁ¬½Ó²éѯ(±íÃû1£ºa ±íÃû2£ºb)
¡¡¡¡SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c ¡¡¡¡
¡¡¡¡ËµÃ÷£ºÈճ̰²ÅÅÌáÇ°Îå·ÖÖÓÌáÐÑ
¡¡¡¡SQL: select * from Èճ̰²ÅÅ where datediff('minute',f¿ªÊ¼Ê±¼ä,getdate())>5 ¡¡¡¡
¡¡¡¡ËµÃ÷£ºÁ½ÕŹØÁª±í£¬É¾³ýÖ÷±íÖÐÒѾÔÚ¸±±íÖÐûÓеÄÐÅÏ¢
¡¡¡¡SQL:
¡¡¡¡delete from info where not exists ( select * from infobz where info.infid=infobz.infid ) ¡¡¡¡
¡¡¡¡ËµÃ÷£º--
¡¡¡¡SQL:
¡¡¡¡SELECT A.NUM, A.NAME, B.UPD_DATE, B.PREV_UPD_DATE from TABLE1,(SELECT X.NUM, X.UPD_DATE,
Y.UPD_DATE PREV_UPD_DATE from (SELECT NUM, UPD_DATE, INBOUND_Q ......