国产av日韩一区二区三区精品,成人性爱视频在线观看,国产,欧美,日韩,一区,www.成色av久久成人,2222eeee成人天堂

Home Database Mysql Tutorial Sql Server中的表訪問方式Table Scan, Index Scan, Index Seek

Sql Server中的表訪問方式Table Scan, Index Scan, Index Seek

Jun 07, 2016 pm 04:20 PM
scan server table Way access

Sql 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

Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undress AI Tool

Undress AI Tool

Undress images for free

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

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

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

iOS 17: How to control which apps can access your photos iOS 17: How to control which apps can access your photos Sep 13, 2023 pm 09:09 PM

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 &gt ;Privacy & Security>Photos to view selected images. Full access – App can view photos

How to install, uninstall, and reset Windows server backup How to install, uninstall, and reset Windows server backup Mar 06, 2024 am 10:37 AM

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 of inaccessibility after Tomcat deploys war package How to solve the problem of inaccessibility after Tomcat deploys war package Jan 13, 2024 pm 12:07 PM

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

Access metadata of various audio and video files using Python Access metadata of various audio and video files using Python Sep 05, 2023 am 11:41 AM

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

How to access JSONNode's JSON fields, arrays and nested objects in Java? How to access JSONNode's JSON fields, arrays and nested objects in Java? Aug 30, 2023 pm 11:05 PM

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

What are the methods of time processing in Go language? What are the methods of time processing in Go language? Jun 10, 2023 pm 09:42 PM

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

What to do if shared folders cannot be accessed in Windows 10 Home Edition What to do if shared folders cannot be accessed in Windows 10 Home Edition Jan 11, 2024 pm 07:36 PM

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 Nginx version name to disguise any web server How to modify the Nginx version name to disguise any web server May 14, 2023 pm 09:19 PM

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

See all articles