Stack Overflow
Stack Overflow

Questions tagged [sql-execution-plan]

A execution, or query plan, is the breakdown of steps the database engine uses to get a result.

A execution, or query plan, is the breakdown of steps the database engine uses to get a result. Specifically it is the plan that the optimizer determines is the best, most efficient, way to get the data.

The execution plan can be analyzed to determine what is happening and how to improve efficiency. Including finding if an index is being scanned or missing an index altogether. There is also many points of analytic data that can be acquired via the plan including things such as estimated and actual number of rows.

1429 questions
21
votes
4 answers

How can I see the SQL execution plan in Oracle?

I'm learning about database indexes right now, and I'm trying to understand the efficiency of using them. I'd like to see whether a specific query uses an index. I want to actually see the difference between executing the query using an index and…
user1573640
  • 293
  • 1
  • 2
  • 10
20
votes
5 answers

Why does putting a WHERE clause outside view have terrible performance

Let's say you have a view: CREATE VIEW dbo.v_SomeJoinedTables AS SELECT a.date, a.Col1, b.Col2, DENSE_RANK() OVER(PARTITION BY a.date, a.Col2 ORDER BY a.Col3) as Something FROM a JOIN b on a.date = b.date I've found that the…
bpeikes
  • 3,495
  • 9
  • 42
  • 80
19
votes
5 answers

Why is this an Index Scan and not a Index Seek?

Here's the query: SELECT top 100 a.LocationId, b.SearchQuery, b.SearchRank FROM dbo.Locations a INNER JOIN dbo.LocationCache b ON a.LocationId = b.LocationId WHERE a.CountryId = 2 AND a.Type = 7 Location …
RPM1984
  • 72,246
  • 58
  • 225
  • 350
19
votes
2 answers

How can I "think better" when reading a PostgreSQL query plan?

I spent over an hour today puzzling myself over a query plan that I couldn't understand. The query was an UPDATE and it just wouldn't run at all. Totally deadlocked: pg_locks showed it wasn't waiting for anything either. Now, I don't consider myself…
Evan Carroll
  • 78,363
  • 46
  • 261
  • 468
19
votes
6 answers

EXPLAIN SELECT in other databases

I found EXPLAIN SELECT query very useful in MySQL because it gives information on how SQL will be executed and gives the opportunity to analyze, for e.g., missing indexes you should add in order to improve response BEFORE doing the query itself and…
Mauro H. Leggieri
18
votes
1 answer

Why does the Execution Plan include a user-defined function call for a computed column that is persisted?

I have a table with 2 computed columns, both of which has "Is Persisted" set to true. However, when using them in a query the Execution Plan shows the UDF used to compute the columns as part of the plan. Since the column data is calculated by the…
Kevin Babcock
  • 10,187
  • 19
  • 69
  • 89
18
votes
4 answers

Tool for comparison of SQL Server query plans?

Does anyone know of a tool that can be used to compare (relatively complex) query plans? I'm not looking for a guide to query plans, but just a tool that enables me to quickly see e.g. the different index use. EDIT: just to make it clear, I'm not…
davek
  • 22,499
  • 9
  • 75
  • 95
18
votes
2 answers

Why is Oracle ignoring index with ORDER BY?

My intention is to obtain a paginated resultset of customers. I am using this algorithm, from Tom: select * from ( select /*+ FIRST_ROWS(20) */ FIRST_NAME, ROW_NUMBER() over (order by FIRST_NAME) RN from CUSTOMER C ) where RN between 1 and…
Bogdan Minciu
  • 255
  • 1
  • 3
  • 6
17
votes
5 answers

Conditional UNION ALL in table function

So use case is as follows - there're some parameter, based on which I want to select data from one table or another. create table dbo.TEST1 (id int primary key, name nvarchar(128)) create table dbo.TEST2 (id int primary key, name nvarchar(128)) So…
Roman Pekar
  • 107,110
  • 28
  • 195
  • 197
17
votes
2 answers

Query cost relative to batch is 100%

I'm not sure sure how to interpret this, but all the queries I run in sql server 2005 have a "query cost (relative to batch)" of 100%. Is there any way to reduce the cost?
chobo
  • 31,561
  • 38
  • 123
  • 191
17
votes
1 answer

Is there a way to programmatically convert a SQL Server query plan to an image?

I'd like to be able to convert SQL Server query plans from XML to images. Ideally a vector format, but a bitmap would do. Is there an open source library to do this? Or can I use one of the SQL Server Management Studio DLLs? Thanks.
Ben Challenor
  • 3,365
  • 1
  • 35
  • 35
17
votes
3 answers

difference between explain plan and execution plan

Can anyone explain me what is the difference between execution plan and explain plan. When I execute set autotrace traceonly; select * from emp where empno=7369; Execution Plan ---------------------------------------------------------- 0 …
Gaurav Soni
  • 6,278
  • 9
  • 52
  • 72
16
votes
1 answer

Getting rid of Table Spool in SQL Server Execution plan

I have a query that creates several temporary tables and then inserts data into them. From what I understand this is a potential cause of Table Spool. When I look at my execution plan the bulk of my processing is spent on Table Spool. Are there…
Abe Miessler
  • 82,532
  • 99
  • 305
  • 486
15
votes
7 answers

SQL order of operations

If I run the following SQL query SELECT * FROM A LEFT JOIN B ON A.foo=B.foo WHERE A.date = "Yesterday" Does the WHERE statement get evaluated before or after the JOIN? If after, what would be a better way to write this statement so that returns…
tinkertime
  • 2,972
  • 4
  • 30
  • 45
15
votes
3 answers

How do I know if any index is used in a query | PostgreSQL 11?

I am little bit confused and need some advice. I use PostgreSQL 11 database. I have such pretty simple sql statement: SELECT DISTINCT "CITY", "AREA", "REGION" FROM youtube WHERE "CITY" IS NOT NULL AND "AREA" IS NOT NULL AND "REGION"…
Nurzhan Nogerbek
  • 4,806
  • 16
  • 87
  • 193
1 2
3
…
95 96