Sybase IQ SQL开发指南_v1.1

[e-mail].200961¾¾™™¾™T-SQL™W-SQL¾¾¾9¾™™¾™T-SQL™W-SQL¾¾¾¾™™bulkinserts™¾“”¾IQIQ’’IQ¾IQ¾IQ¾ABISABISCMSCMSOthersOthersETL/stagingdatabaseOLAPReportAdhocqueries(1)ABISABISCMSCMSOthersOthersBCP(ASE)stagingdatabase(ASE)OLAPReportAdhocqueries••ASEETL•bcp(2)ABISABISCMSCMSOthersOthersBCP(IQ)stagingdatabase(ASE)OLAPReportAdhocqueriesLoadTableInsertLocation•IQ!!!¾9™™¾™T-SQL™W-SQL¾¾¾¾™™“”I/O™3NF™¾™()™“”99“”9“”™3NF™¾™9999–HG–™99™9“”–“”“”“”DATEDATETIMESMALLDATETIMETIMESTAMP–TINYINT(1byte)SMALLINT(2)INTEGER(4)BIGINT(8)–NUMERICDECIMAL(:126:38;ASE180)–CHAR(n)VARCHAR(n)19“”9™NULLNOTNULL99¾¾Column-WiseDataStructures¾¾Bit-WiseIndexing¾¾Continued…Bit-WiseIndexingIQ¾¾™B-Tree™¾™¾¾FastProjection(FP)Index¾™Alsoknownasthe“DefaultIndex”™ServercreatestheindexnameintheformASIQ_IDX_…_FP™AlterTableDropcolumn_name¾™ProjectingdataintheSELECTlist™Wildcardsearch-LIKE'%sys%'™ExpressionCalculation-SUM(A+B)™JoinOperations–matchesupcolumnvalues¾FPIndexStorage¾™Storagemethoddependsonthecardinalityofthecolumn¾“”™()™9––¾™12™DBAIQUniqueConstraintsyntaxFlatFP’¾–Continued…1ByteFP¾Continued…2ByteFP¾™LookuptablecreatedwithalldistinctvaluesContinued…IQUnique()Constraint¾CreateTablecustomer(idchar(10)NOTNULLIQUnique(3000),…)¾™256values-Single-byte(1-byteFPindex)™255and65537-Two-byte(2-byteFPindex)™Defaultvalueis65,537(FlatFP)¾Minimize_StorageDatabaseOption¾™EliminatesneedtoaddIQUnique()forallcolumnsinscripts¾™Thedatabaseoptioncouldbe‘turnedoff'foratableFPIndexStorage¾™SybaseIQQueryoptimizerexploitstheseindexes™™I/O¾¾¾¾LowFast(LF)Continued…87654321RowIDLFIndex¾…™Equality(=),Inequality(!=),INandNOTINsearcharguments™MIN(),MAX(),COUNT(),COUNTDISTINCTargument™GroupBy™Joins(inplaceofanHGindex)¾™RangeSearches(,,=,Between,etc)¾HighGroup(HG)HGIndex¾™Allkey/joincolumns(regardlessofcardinality)™SearchargumentsinWHEREclause™GROUPBY™SELECTDISTINCT,COUNTDISTINCTclauses¾¾HighNonGroup(HNG)¾SELECTSUM(sales)FROMcustomer(2*64)+(3*32)+(2*16)+(1*8)+(3*4)+(2*2)+(3*1)=283HNGIndex¾™Exception:Date/DatetimeandoptimizedFPindexcolumns™CannotbedeclaredUnique™Cannotbeacompositeindex¾™Ranges™BETWEEN™MIN(),MAX(),SUM()andAVG()functions¾¾Compare(CMP)CMPIndex¾¾™,=,,=,=¾™PurchasePrice,ListCost™EmployeeID,ManagerID™Date/DatetimeDate(DATE),Time(TIME),andDatetime(DTTM)Indexes¾™ADATEindexisonlyforcolumnsofdatatypeDATEusedtoprocessqueriesinvolvingdatequantities™ATIMEindexisonlyforcolumnsofdatatypeTIMEusedtoprocessqueriesinvolvingtimequantities™ADTTMindexisonlyforcolumnsofdatatypeDATETIMEorTIMESTAMPusedtoprocessqueriesinvolvingdatetimequantitiesContinued…IQ¾™9999™IQ9–Ad-HocjoinFP“”FPIQ¾™IQ9–SELECT–SUMA+B–WHERELIKESUBSTRcolumnlike“%x”9––GROUPBYORDERBY–MINMAXSUMAVGCOUNT–COUNTDISTINCTSELECTDISTINCT–WHERE==BETWEENINIQ¾™IQ9––SELECTDISTINCTCOUNTDISTINCT–COUNTMINMAX–GROUPBYORDERBY–IN–9–AVGSUM–BETWEEN–RootStringWHEREcust_namelike“Stan%”likeFP¾¾™™9™T-SQL™W-SQL¾¾¾IQ¾™IQASASQL999™T-SQL999™W-SQL99IQ¾™IQT-SQLW-SQL™IQIQDML™9™T-SQLT-SQL9999999IQ¾T-SQLbegindeclare@jinteger,@kintegerselect@k=30set@j=10declare@iintegerselect100into@iendIQ¾™T-SQLW-SQL999999IQ¾W-SQLbegindeclarejinteger;declareiinteger;declarekinteger;setk=30;setj=10;select100intoi;//endIQSQL¾™DDL™DMLDMLPPT™DCL™¾IQSQL¾™Localvariables™Connectioin-levelvariables(staticvariables)™Globalvariables¾™9™9999™9™999IQSQL¾™,ASE™99™Localvariables¾™IQASE™IQ9™IQ3SQLLanguageElements¾™T-SQLW-SQL™¾IFexpression...statement...[ELSE[IFexpression]statement]...……if@i10print'@i=%1!',@ielseprint'@i=%1!',@i-10……¾……ifi10thenprint'i=%1!',ielseprint'i=%1!',i-10endif……CASE¾™T-SQLDMLCASE™selectkhh,jgh,bz,casewhenleft(cpbm,3)='101'thenzbyeelse0endashqckpjyefromcrmo_gg_ckyepjwheretjrq=@datadateCASE¾¾…………¾™™WHILE(SELECTAVG(unit_price)FROMproduct)$30BEGINDELETEFROMproductWHEREunit_price=MAX(unit_price)IF(SELECTMAX(unit_price)FROMproduct)$50BREAKENDDCL¾™™SETi=1;WHILEi=10LOOPINSERTINTOCounters(number)VALUES(i);SETi=i+1;ENDLOOP;¾¾™™SQLEXECUTE¾™™SQLEXECUTEIMMEDIATE99¾¾™SQLbegindeclare@iinteger,@jinteger;set@i=101select@j=100if@i100thenprint'@i=%1!',@i;elseprint'@i=%1!',@j-@i;endifendbegindeclare@iinteger,@jintegerset@i=101select@j=100if@i100print'@i=%1!',@ielseprint'@i=%1!',@j-@iendIQSQL¾™3TypesofTemporaryTables9“”–createtable#temptable(col1int)–selectinto#tablename9–declarelocaltemporarytabletemp(col1int)–Behavejustlike“#”tables9–createglobaltemporarytabletemp(col1int)–Tablesstructureisstaticacrossconnectionsandreboots–IQSQL¾™99–13T-SQLW-SQL–2W-SQL9–1committruncateable–23commit“OnCommitPreserveRows”¾¾™™¾™T-SQL™W-SQL9¾¾IQSQL¾™9999“”™commitwithhold™CurosorIQSQL¾™T-SQLbegindeclare@fnamechar(15)declare@lnamechar(20)declarecust_cursorcursorforselectfname,lnamefromcustomerwhereid110opencust_cursorfetchcust_cursorinto@fname,@lnamewhile(@@sqlstatus=0)beginfetchcust_cursorinto@fname,@lnameprint'@fname=%1!,@lname=%2!',@fname,@lnameendclosecust_cursordeallocatecursorcust_cursorend;IQSQL¾™W-SQL(1)BEGINdeclarecust_cursorcursorforselectfnamefromcustomerwhereid110;DECLAREnameCHAR(40);OPENcust_cursor;curL