Sybase :: essays research papers

Table of contentsTypes of indicantes2Clustered ability2Non-clustered Index2Selection of indexes by Optimizer2 inquisition Arguments2Index Selection3Index Distribution Stats3Index Density4Index Covering4 optimisation of Cursors5Optimization of temp tables6 approximately general rules for optimization7Some useful commands10Types of indexesClustered Index (CI)     The entropy in this fibre is physically stored in the order of the index. The leaf take aim of the index is the selfsame(prenominal) as the selective information pages. There can be besides one CI on a table as the data can physically be sorted in only one order. The select is extremely econo(prenominal)ical with CI. The CI is extremely efficient in the following casesa) where fname like "Ram%"b) where author_id between 1 and 7c) where set > 345.34d) group by author_ide) order by author_nameNon-clustered index (NI)     The data in this case is not stored in the order an index is stored. The leaf-level of the index contains the various index keys and a pointer to the row as rowID (page no. + row offset). There can be 249 NI on a table. The NI should be used whena) The number of rows returned is small.b) When where clause limits the number of rows (usually = operator)c) When the interrogate can be covered.Selection of indexes by OptimizerSEARCH ARGUMENTS (SARG)     These are the expressions on the RHS of the where clause. They act as a kind of (dis)incentive to the optimizer to use the index on the column. Some search arguments are     where author_id = 13     where fname like "Ram%"     where Price > 2347.32Some expressions that are not valid SARGs are     Invalid                    ValidPrice*1.5 = 1000          Price = 1000/1.5Qty + 10 = 200               Qty = 200 - 10fname + + lname          fname = John     = "John Gray"               and lname = GraySubstring(1,3,fname) = KIR     Name like "KIR%"is goose egg(lname,"N") = "N"          lname is unreal          The index might not be used in the case of following SARGs1) No start point for the index.     where lname like "%abc"2) Non-matching data-types     In SQL server, null and not null are held differently.Char null is same as varchar. So when char null and char not null is compared, the optimizer has to implicitly convert the data type, which it does not at the planning time.  &nb sp   In both the above cases, distribution statistics arenot used.INDEX weft     The optimizer first looks at the query if the columnscontained in the where clause match with the columns qualify in whatsoever of the index. If yes, then it proceeds further.     The optimizer then looks if the where clause contains any SARG. If there is a valid SARG, the optimizer then looks for the distribution statistics of the index as