Sql Server中的表訪問方式Table Scan, Index Scan, Index Seek
Jun 07, 2016 pm 04:20 PMSql Server中的表訪問方式Table Scan, Index Scan, Index Seek 0.參考文獻 oracle表訪問方式 Index Seek和Index Scan的區(qū)別以及適用情況 1.oracle中的表訪問方式 在oracle中有表訪問方式的說法,訪問表中的數(shù)據(jù)主要通過三種方式進行訪問: 全表掃描(full tab
? Sql Server中的表訪問方式Table Scan, Index Scan, Index Seek
0.參考文獻
oracle表訪問方式
Index Seek和Index Scan的區(qū)別以及適用情況
1.oracle中的表訪問方式
在oracle中有表訪問方式的說法,訪問表中的數(shù)據(jù)主要通過三種方式進行訪問:
全表掃描(full table scan),直接訪問數(shù)據(jù)頁,查找滿足條件的數(shù)據(jù)
通過rowid掃描(table access by rowid),如果知道數(shù)據(jù)的rowid,那么直接通過rowid進行查找
索引掃描(index scan),如果一個表創(chuàng)建了索引,那么可以通過索引來找出我們想要的數(shù)據(jù)在表中的存放位置,也就是rowid,通過返回rowid然后用rowid來進行訪問具體數(shù)據(jù)。
而索引掃描中又可分為索引全掃描(index full scan)、索引范圍掃描(index range scan)和索引唯一掃描(index unique scan)等。
2.sql server中clustered index scan,table scan,index scan
在sqlserver中也有類似的內(nèi)容,這里就要將的是table scan,index scan以及index seek.
A table scan is where the table is processed row by row from beginning to end.
An index scan is where the index is processed row by row from beginning to end.
If the index is a clustered index then an index scan is really a table scan.
總結:在sql server中,對表中數(shù)據(jù)從頭到尾一行一行的進行出來就是表掃描。這里的處理我們可以理解為sql中where子句的條件判斷。我們需要遍歷表中的每一行,判斷是否滿足where條件。最簡單的table scan是select * from table。
索引掃描就是對索引中的每個節(jié)點從頭到尾的訪問。假設我們的索引是B樹結構的,那么index scan就是訪問B樹中的每一個節(jié)點。
假如索引是聚集索引,那么B樹索引的葉子節(jié)點保存的是數(shù)據(jù)頁中的實際數(shù)據(jù)。假如索引是非聚集索引,那么B樹葉子節(jié)點保存的是指向數(shù)據(jù)頁的指針。
(ps:以下2.1-2.6于2012-9-4補充)
2.1實驗數(shù)據(jù)準備
在介紹完clustered index scan,table scan和index scan以后,我們將通過實驗來表述會在什么情況下使用這些表掃描方式。我們將使用AdventureWorks2008R2這個sample database進行實驗,首先準備實驗數(shù)據(jù),TSQL如下所示:
View Code
--準備測試數(shù)據(jù)--------------------------------------------------
use adventureworks2008R2
go
--如果表已存在,刪除
drop table dbo.SalesOrderHeader_test
go
drop table dbo.SalesOrderDetail_test
go
--創(chuàng)建表
select * into dbo.SalesOrderHeader_test
from Sales.SalesOrderHeader
go
select * into dbo.SalesOrderDetail_test
from Sales.SalesOrderDetail
go
--創(chuàng)建索引
create clustered index SalesOrderHeader_test_CL
on dbo.SalesOrderHeader_test (SalesOrderID)
go
create index SalesOrderDetail_test_NCL
on dbo.SalesOrderDetail_test (SalesOrderID)
go
--select * from dbo.SalesOrderDetail_test
--select * from dbo.SalesOrderHeader_test
declare @i int
set @i = 1
while @i
begin
insert into dbo.SalesOrderHeader_test
(RevisionNumber, OrderDate, DueDate,
ShipDate,Status, OnlineOrderFlag, SalesOrderNumber,PurchaseOrderNumber,
AccountNumber, CustomerID, SalesPersonID, TerritoryID,
BillToAddressID, ShipToAddressID, ShipMethodID, CreditCardID,
CreditCardApprovalCode, CurrencyRateID, SubTotal,TaxAmt,
Freight,TotalDue, Comment,rowguid,ModifiedDate)
select RevisionNumber, OrderDate, DueDate,
ShipDate,Status, OnlineOrderFlag, SalesOrderNumber,PurchaseOrderNumber,
AccountNumber, CustomerID,SalesPersonID, TerritoryID,
BillToAddressID, ShipToAddressID, ShipMethodID, CreditCardID,
CreditCardApprovalCode, CurrencyRateID, SubTotal,TaxAmt,
Freight,TotalDue, Comment,rowguid,ModifiedDate
from dbo.SalesOrderHeader_test
where SalesOrderID = 75123
insert into dbo.SalesOrderDetail_test
(SalesOrderID, CarrierTrackingNumber, OrderQty, ProductID,
SpecialOfferID,UnitPrice,UnitPriceDiscount,LineTotal,
rowguid,ModifiedDate)
select 75123+@i, CarrierTrackingNumber, OrderQty, ProductID,
SpecialOfferID,UnitPrice,UnitPriceDiscount,LineTotal,
rowguid, getdate()
from Sales.SalesOrderDetail
set @i = @i +1
end
go
--數(shù)據(jù)準備完畢--------------------------------
2.2實驗數(shù)據(jù)說明:
dbo.SalesOrderHeader_test里存放的是每一張訂單的頭信息,包括訂單創(chuàng)建日期、客戶編號、合同編號、銷售員編號等,每個訂單都有一個單獨的訂單號。在訂單號這個字段上,有一個聚集索引。
dbo.SalesOrderDetail_test里存放的是訂單的詳細內(nèi)容。一張訂單可以銷售多個產(chǎn)品給同一個客戶,所以dbo.SalesOrderHeader_test和dbo.SalesOrderDetail_test是一對多的關系。每條詳細內(nèi)容包括它所屬的訂單編號,它自己在表格里的唯一編號(SalesOrderDetailID)、產(chǎn)品編號、單價,以及銷售數(shù)量等。在這里,先只在SalesOrderID上建立一個非聚集索引。create index默認創(chuàng)建的就是非聚集索引。
按照AdventureWorks里原先的數(shù)據(jù),dbo.SalesOrderHeader_test里有3萬多條訂單信息,dbo.SalesOrderDetail里有12萬多條訂單詳細記錄,基本上一條訂單有3~5條詳細記錄。這是一個正常的分布。為了使數(shù)據(jù)分布不均勻,我們再在dbo.SalesOrderHeader_test里加入9條訂單記錄,它們的編號是從75124到75132。這是9張?zhí)厥獾挠唵?,每張?2萬多條詳細記錄。也就是說,dbo.SalesOrderDetail_test里會有90%的數(shù)據(jù)屬于這9張訂單。主要是使用“select 75123+@i...”來搜索出Sales.SalesOrderDetail中的所有記錄插入到dbo.SalesOrderDetail。一共執(zhí)行9次。
2.3 table scan

Hot AI Tools

Undress AI Tool
Undress images for free

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Clothoff.io
AI clothes remover

Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

Hot Tools

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics

In iOS17, Apple has more control over what apps can see in photos. Read on to learn how to manage app access by app. In iOS, Apple's in-app photo picker lets you share specific photos with the app, while the rest of your photo library remains private. Apps must request access to your entire photo library, and you can choose to grant the following access to apps: Restricted Access – Apps can only see images that you can select, which you can do at any time in the app or by going to Settings > ;Privacy & Security>Photos to view selected images. Full access – App can view photos

WindowsServerBackup is a function that comes with the WindowsServer operating system, designed to help users protect important data and system configurations, and provide complete backup and recovery solutions for small, medium and enterprise-level enterprises. Only users running Server2022 and higher can use this feature. In this article, we will explain how to install, uninstall or reset WindowsServerBackup. How to Reset Windows Server Backup If you are experiencing problems with your server backup, the backup is taking too long, or you are unable to access stored files, then you may consider resetting your Windows Server backup settings. To reset Windows

How to solve the problem that Tomcat cannot successfully access the war package after deploying it requires specific code examples. As a widely used Java Web server, Tomcat allows developers to package their own developed Web applications into war files for deployment. However, sometimes we may encounter the problem of being unable to successfully access the war package after deploying it. This may be caused by incorrect configuration or other reasons. In this article, we'll provide some concrete code examples that address this dilemma. 1. Check Tomcat service

We can access the metadata of audio files using Mutagen and the eyeD3 module in Python. For video metadata we can use movies and the OpenCV library in Python. Metadata is data that provides information about other data, such as audio and video data. Metadata for audio and video files includes file format, file resolution, file size, duration, bitrate, etc. By accessing this metadata, we can manage media more efficiently and analyze the metadata to obtain some useful information. In this article, we will take a look at some of the libraries or modules provided by Python for accessing metadata of audio and video files. Access audio metadata Some libraries for accessing audio file metadata are - using mutagenesis

A JsonNode is Jackson's JSON tree model that can read JSON into JsonNode instances and write JsonNode into JSON. We can use Jackson to read JSON into a JsonNode by creating an ObjectMapper instance and calling the readValue() method. We can access fields, arrays or nested objects using the get() method of the JsonNode class. We can use the asText() method to return a valid string representation and convert the node's value to Javaint using the asInt() method of the JsonNode class. In the example below we can access Json

As a modern programming language, Go language plays an important role in development. The Go language provides some built-in time functions and structures to make time processing more convenient. In this article, we will introduce some commonly used time processing methods in the Go language. time.Now() We can use the time.Now() function to get the current time: now:=time.Now()fmt.Println(now) output: 2019-06-131

Sharing folders is indeed an extremely useful feature in a home or business network environment. It allows you to easily share folders with other users, thereby facilitating file transfer and sharing. Win10 Home Edition shared folder cannot be accessed Solution: Solution 1: Check network connection and user permissions When trying to use Win10 shared folders, we first need to confirm whether the network connection and user permissions are normal. If there is a problem with the network connection or the user does not have permission to access the shared folder, it may result in inaccessibility. 1. First, please ensure that the network connection is smooth so that the computer and the computer where the shared folder is located are in the same LAN and can communicate normally. 2. Secondly check the user permissions to confirm that the current user has permission to share files.

How to modify the default name of nginx, you can disguise it a little, or you can install Tip: Generally, modifications are made before nginx is compiled. After modification, the code needs to be recompiled as follows: scr/core/nginx.conf#definenginx_version"1.4.7"#definenginx_ver"nginx/"n
