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
0
votes
1 answer

Oracle Query in full table scan

I don't understand why this (partioned by range) table is in full table scan in this query: UPDATE customer_table SET username = '', phone = '', ip = '', cell = '', channel = '' WHERE id = ''; I've also created this…
Fabio
  • 13
  • 6
0
votes
1 answer

Oracle SQL - Why this query has full table scan?

I've got a problem with this query: SELECT * FROM customer_table PARTITION (p25062014) WHERE ( customer_table.username NOT IN ('user1','user2','user3') OR customer_table.username IS NULL ) AND (customer_table.ip NOT IN…
Fabio
  • 13
  • 6
0
votes
1 answer

Multiple seq scans on the same filters

I wanted to ask why by execution of a simple query optimizer decide to execute seq scan twice using the same filter appending the results at the end? SELECT count(*) FROM customers c WHERE ((name IS NOT NULL) AND (flag = 4) AND (birth_date <…
Borys
  • 2,676
  • 2
  • 24
  • 37
0
votes
1 answer

SQL "find missing index" script suggests missing index that already exists? Query in top 10 CPU list

One query is in the top of the most cpu intensive list, but it's simplicity (see example below) contradicts this. However the "find missing index" script suggests creating an index for the table... but this exact (column order and include) index…
baskabas
  • 333
  • 3
  • 5
  • 15
0
votes
0 answers

If a certain operation/operator appears in actual query plan then is it necessary that the operation has executed?

I have captured Showplan XML Statistics Profile event class and I got this plan . I have also captured Scan:Started event class. Here is a part of the plan that appears for Showplan XML Statistics Profile What took me by surprise is that although…
Sameer
  • 3,124
  • 5
  • 30
  • 57
0
votes
3 answers

Explain Plan with Oracle

I want to take a look at the execution-Plans in Oracle. I tried this: select /*+ gather_plan_statistics */ /*test4*/ * from tag where key='name'; SELECT t.* FROM v$sql s, table(DBMS_XPLAN.DISPLAY_CURSOR(s.sql_id, s.child_number, 'TYPICAL…
Andre
  • 1,249
  • 1
  • 15
  • 38
0
votes
2 answers

ANSI_NULLS and QUOTED_IDENTIFIER Behavior

I have a view: create view dbo.vMainTable as select MT.* from dbo.Table1 T1 with(index(dbo_Table1_UQ)) inner loop join dbo.MainTable MT with(index(dbo_MainTable_PK)) on T1.ID = MT.Table1_ID go On querying the view, I get the below error if…
aliensurfer
  • 1,600
  • 3
  • 17
  • 38
0
votes
1 answer

Inefficent Query Plans SQL Server 2008 R2

Good Day, We experience ongoing issues with our databases for which our internal DBA's are unable to explain. Using the below query example: Select Distinct Date, AccountNumber, Region, Discount, ActiveBalance Into …
greywolf1978
  • 1
0
votes
1 answer

Return types of glob() and like() and failure of using index although 'LIKE optimization' applies

I'm writing this finding due to auto-created SQL by EntityFramework (see related question): When returning the result of glob() (or like()) it appears that the type of these functions is bit: SELECT Name, glob('admin*', Name) as globresult FROM…
springy76
  • 3,706
  • 2
  • 24
  • 46
0
votes
1 answer

How to read Verbose Output from MongoDB-explain(1)

I have the following query.explain(1)-Output. It is a verbose output and my question is how to read that. How is the order of the operations? Does it starts with GEO_NEAR_2DSPHERE or with LIMIT? What does the field advanced express? And most…
Andre
  • 1,249
  • 1
  • 15
  • 38
0
votes
0 answers

Getting strange reaction in T-SQL script

I am writing a stored procedure and when I tested the procedure in Execution Plan/Sentry Plan Explorer I have noticed that in one statement the plan reacts strangely. Here are 2 scripts that from my point of view are the same, but the results are…
Misha Zaslavsky
  • 8,414
  • 11
  • 70
  • 116
0
votes
2 answers

Will the query plan be changed on different data size?

Suppose the data distribution does not change, For a same query, only dataset is enlarged a time, will the time taken also becomes 1 time? If the data distribution does not change, will the query plan change if in theory?
user3419945
  • 347
  • 1
  • 2
  • 17
0
votes
3 answers

SQL Server 2005 Execution Plan

I am attempting to troubleshoot a slow running stored procedure in SQL Server 2005. I am analyzing the execution plan and see a SORT that is 45%, but I am not using an ORDER clauses. What would be causing this. UPDATE SP (cleaned up, and made…
Dustin Laine
  • 37,935
  • 10
  • 86
  • 125
0
votes
1 answer

Exists Vs IN Vs =

Considering that the MASTER table has primary Key over the column ID This is the original query UPDATE A SET ID = DESC WHERE EXISTS (SELECT 1 FROM MASTER IM2 WHERE IM2.CD_ID = :B2 AND IM2.ID = A.DESC) This is the Modified Query 1 UPDATE A SET…
Guddu
  • 1,588
  • 4
  • 25
  • 53
0
votes
1 answer

Is there a way to transform this keylookup in an index scan?

I'm not an SQL expert (I try to get jobs done - the faster the better, obviously), I don't know if the information I'm giving you is enough to solve the problem. I'm using SQL Server, SQL Management studio. In this case I have a join query on the…
foebu
  • 1,365
  • 2
  • 18
  • 35
1 2 3
…
95 96