Database Reference
In-Depth Information
> # create a new physical design provider
> New-PDTDrive -Name P -Database tpch1g
> # set the current location at the root of the provider
>cdP:
> # load the TPC-H workload
> $w = Get-Query -Path D:/workloads/tpch-all.sql
Reading queries from D:/workloads/tpch-all.sql...
> # get the cost of all queries in the base configuration in decreasing order of cost
> $c = Get-Configuration base
> $w | Optimize-Query -Configuration $c | sort -desc cost | out-chart -values Cost
280
240
200
160
120
80
40
0
> # obtain the top-3 most expensive queries
> $expW = $w | Optimize-Query -Configuration $c | sort cost -desc | select -first 3 | %
{ $ .query }
Name
Database
Type
Rows
Requests
----
--------
----
----
--------
Q20
tpch1g
Select
100
45
Q8
tpch1g
Select
172.421
48
Q14
tpch1g
Select
999.809
8
> # for each expensive query, obtain access-path requests and infer best indexes
> $bestIdx = $expW | % { $ .Requests } |% { $ .BestIndex }
Name
Table
Keys Includes
----
-----
---- --------
PDT I17
orders
1
1
PDT I18
orders
1
1
PDT I19
lineitem 1
3
...
> # create a new configuration with all these best indexes
> $bestC = New-Configuration -Indexes $bestIdx -Name "MyC"
FIGURE 12.4 Interactive physical design tuning example. (Used with per-
mission from Bruno, N. & Chaudhuri, S. In Proceedings of the International
Conference on Data Engineering [ ICDE ], 2010.)
Search WWH ::




Custom Search